RICHEER COCA 2021-11-07 12:25 采纳率: 93.9%
浏览 69
已结题

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

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

img

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

  • 写回答

6条回答 默认 最新

  • Hello World, 2021-11-08 11:05
    关注

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

    img

    
    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;
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(5条)

报告相同问题?

问题事件

  • 已结题 (查看结题原因) 11月17日
  • 已采纳回答 11月10日
  • 创建了问题 11月7日

悬赏问题

  • ¥15 Mapreduce是正常的,在运行其他jar包时并没有任何问题,只是在做LogCount.jar 时出的问题。如图所示
  • ¥15 ImportError: DLL load failed while importing _iterative: 找不到指定的模块。
  • ¥15 如何通过交互分析得出某高危患者对放疗获益更多
  • ¥15 相关性分析中,p<0.05, r=0.29,怎么评价相关性呢
  • ¥15 docker部署Mongodb后输入命令报错?
  • ¥15 将下列流程图转变成python程序代码
  • ¥15 我需要全国每个城市的最新小区名字等数据。
  • ¥15 开发一个小区生态的小程序
  • ¥15 如何解决Excel中dependent dropdown list 的问题
  • ¥15 MddBootstrapInitialize2失败