题主代码有误无法用SQL统计出 百分比Rusult， 请专家解答。

Question

if object_id('[tempdb]..#tb') is not null drop table #tb;
go
create table #tb (id int,[R1]int,[R2] int ,[R3] int ,[R4]INT ,[R5] int ,[R6]int,NOTEXT VARCHAR(20));
go
insert into #tb
 select '1','1','3','18','19','26','29','01 03 18 19 26 29'
union all select '2','1','3','5','18','22','23','01 03 05 18 22 23'
union all select '3','1','7','8','10','12','24','01 07 08 10 12 24'
union all select '4','6','8','22','24','25','26','06 08 22 24 25 26'
union all select '5','4','11','13','22','25','32','04 11 13 22 25 32'
union all select '6','19','20','23','27','28','31','19 20 23 27 28 31'
union all select '7','4','20','22','24','26','33','04 20 22 24 26 33'
union all select '8','4','15','17','22','29','32','04 15 17 22 29 32'
union all select '9','6','14','15','19','29','31','06 14 15 19 29 31'
union all select '10','1','9','15','16','19','21','01 09 15 16 19 21'
union all select '11','4','5','12','16','22','30','04 05 12 16 22 30'
go
;with t as(
select *,
(R6-R5+R4-R3+R2-R1+0) AS AC,
(R6-R5+R4-R3+R2-R1+1) AS AC1,
(R6-R5+R4-R3+R2-R1+2) AS AC2,
(R6-R5+R4-R3+R2-R1+3) AS AC3
 from #tb
 )
select a.id,a.notext,a.R1,a.R2,a.R3,a.R4,a.R5,a.R6
,isnull(tt.query('<e> { for $i in e/r  return data($i)} </e>').value('.','varchar(20)'),' ') as same
,isnull(tt.value('count(e/*)','int'),0) as cnt 
from t a left join t b on a.id-1=b.id
outer apply (select xmlcode =cast('<n>'+REPLACE(a.notext,' ','</n><n>')+'</n>'
                                 +'<r>'+REPLACE(b.AC3,' ','</r><r>')+'</r>' as xml)) c1
outer apply(select tt=xmlcode.query('
                                <e> {    for $i in /r
                                        where data($i) =data(/n)
                                        return $i
                                     }    
                                </e>    

                                '))c3
order by id

希望解决的问题：
①、用公式R6-R5+R4-R3+R2-R1+n取得的AC数字 与 上一行R1,R2,R3,R4,R5,R6分别比对，相同的次数累计数为 Val如何取得？
②、统计的百分比Rusult =Val / max(RID)如何批量取得？
③、用SQL统计出当n=？时统计的百分比Rusult为最大。

Answer 1

找出了N值列表，最大的是第一个：
其他的需求参考这个思路看看能否解决

IF OBJECT_ID('[tempdb]..#tb') IS NOT NULL
    DROP TABLE #tb;
GO
CREATE TABLE #tb
(
 id INT, [R1] INT, [R2] INT, [R3] INT, [R4] INT, [R5] INT, [R6] INT, NOTEXT VARCHAR(20)
);
GO
INSERT #tb
    (id, R1, R2, R3, R4, R5, R6, NOTEXT)
VALUES
(1, 1, 3, 18, 19, 26, 29, '01 03 18 19 26 29'    ),
(2, 1, 3, 5, 18, 22, 23, '01 03 05 18 22 23'    ),
(3, 1, 7, 8, 10, 12, 24, '01 07 08 10 12 24'    ),
(4, 6, 8, 22, 24, 25, 26, '06 08 22 24 25 26'    ),
(5, 4, 11, 13, 22, 25, 32, '04 11 13 22 25 32'    ),
(6, 19, 20, 23, 27, 28, 31, '19 20 23 27 28 31'    ),
(7, 4, 20, 22, 24, 26, 33, '04 20 22 24 26 33'    ),
(8, 4, 15, 17, 22, 29, 32, '04 15 17 22 29 32'    ),
(9, 6, 14, 15, 19, 29, 31, '06 14 15 19 29 31'    ),
(10, 1, 9, 15, 16, 19, 21, '01 09 15 16 19 21'    ),
(11, 4, 5, 12, 16, 22, 30, '04 05 12 16 22 30'    )
GO
SELECT      up.Nums AS Number, COUNT(Nums) Times, MaxID, CAST(COUNT(Nums) * 100.0 / MaxID AS NUMERIC(10, 2)) [Percent]
FROM        (   SELECT  *,
                        LAG(R1) OVER (ORDER BY t.id) - N N1,
                        LAG(R2) OVER (ORDER BY t.id) - N N2,
                        LAG(R3) OVER (ORDER BY t.id) - N N3,
                        LAG(R4) OVER (ORDER BY t.id) - N N4,
                        LAG(R5) OVER (ORDER BY t.id) - N N5,
                        LAG(R6) OVER (ORDER BY t.id) - N N6,
                        MAX(t.id) OVER() MaxID
                FROM    (SELECT *, R6 - R5 + R4 - R3 + R2 - R1 AS N FROM #tb) t ) tt
    UNPIVOT (   Nums
                FOR NN IN (N1, N2, N3, N4, N5, N6)) up
GROUP BY    up.Nums,up.MaxID
ORDER BY    COUNT(Nums) DESC;