RICHEER COCA 2022-06-08 22:21 采纳率: 93.9%
浏览 27
已结题

问题一:不用每次手动修改sno,如何修改脚本批量自动完成比较?请专家答疑解惑.

代码上传不了,所以写在下面的题主回答里,

-- 问题一:不用每次手动修改sno,如何修改脚本批量自动完成比较;
--问题二:想要得到的结果是S表的数据,但 每一行 有对应的SNO 例如:

img

img

  • 写回答

3条回答 默认 最新

  • Hello World, 2022-06-09 09:37
    关注
    
    -- #tb_N简称 表N 有600行记录
    DROP TABLE IF EXISTS #tb_N;   --  select * from #tb_N
     
    create table #tb_N([ID] [int] IDENTITY(1,1) NOT NULL primary key,Sno varchar(10),no1 INT,no2 INT,no3 INT,no4 INT,no5 INT,no6 INT,no7 INT,no8 INT,no9 INT,no10 INT,no11 INT,no12 INT,no13 INT,no14 INT,no15 INT,no16 INT,no17 INT,no18 INT,no19 INT,no20 INT
    ) 
    insert into #tb_N 
     
    SELECT '2021001','9','12','16','18','33','36','40','41','46','48','51','53','54','55','59','61','62','63','68','75'
    UNION ALL SELECT '2021002','6','10','11','13','14','28','30','36','39','43','44','47','48','59','67','69','71','73','75','79'
    UNION ALL SELECT '2021003','1','10','11','24','26','27','32','38','49','59','60','63','66','68','69','71','72','73','76','77'
    UNION ALL SELECT '2021004','2','4','6','10','16','17','19','24','27','30','34','38','40','44','48','49','56','60','63','66'
    UNION ALL SELECT '2021005','3','6','12','13','15','21','25','28','32','34','42','45','46','54','55','60','63','65','70','78'
    UNION ALL SELECT '2021006','1','2','3','5','6','12','15','17','21','22','26','29','30','33','43','49','51','58','59','61'
     
    --- #tb_S简称表S,  实际表S 有400000+(约50万)行记录
    DROP TABLE IF EXISTS #tb_S;   --  select * from #tb_S
     
    create table #tb_S([seq] [int] IDENTITY(1,1) NOT NULL primary key,no1 INT,no2 INT,no3 INT,no4 INT,no5 INT,no6 INT,no7 INT,no8 INT,no9 INT,no10 INT,no11 INT,no12 INT,no13 INT,no14 INT,no15 INT,no16 INT,no17 INT,no18 INT,no19 INT,no20 INT
    ) 
    insert into #tb_S 
    SELECT '3','10','12','13','15','16','18','23','27','31','46','48','54','55','59','63','68','70','77','80'
    UNION ALL SELECT '11','13','23','27','28','34','39','43','44','48','52','53','59','64','65','68','71','72','75','80'
    UNION ALL SELECT '2','3','9','10','11','15','16','19','31','34','36','40','44','48','50','52','60','66','71','73'
    UNION ALL SELECT '9','10','14','15','16','24','26','27','37','45','49','50','53','55','59','60','62','66','69','77'
    UNION ALL SELECT '3','5','13','14','21','24','32','35','46','47','50','52','53','54','55','60','62','63','65','66'
    UNION ALL SELECT '1','2','12','13','14','15','17','23','29','30','43','47','48','49','50','51','52','53','55','65'
    UNION ALL SELECT '3','9','10','11','12','18','19','26','41','46','48','51','52','53','54','56','59','61','67','68'
    UNION ALL SELECT '1','10','11','12','13','26','27','29','37','45','49','52','60','61','63','66','71','76','77','79'
    UNION ALL SELECT '3','6','11','12','14','15','20','21','23','25','32','34','51','54','58','60','61','70','78','79'
     
    ---  逐一比较 相同的个数为 same_RpNr,如何批量完成sno =2021001.......2021006
    DECLARE @SNO VARCHAR(100);
    DECLARE cur CURSOR FOR SELECT   Sno FROM #tb_N ORDER BY Sno;
    OPEN cur;
    FETCH NEXT FROM cur
    INTO @SNO;
    WHILE @@fetch_status = 0
        BEGIN
            DROP TABLE IF EXISTS #222; --  select * from #222
            ;
            WITH t1 AS (SELECT Sno, no1, no2, no3, no4, no5, no6, no7, no8, no9, no10, no11, no12, no13, no14, no15, no16, no17, no18, no19, no20 
            FROM #tb_N WHERE Sno = @SNO  -- 每次手动修改sno,如何修改脚本批量自动完成;
            ),
                 t2 AS (SELECT  rn
                        FROM    t1
                            UNPIVOT (   rn
                                        FOR TYPE IN (no1, no2, no3, no4, no5, no6, no7, no8, no9, no10, no11, no12, no13,
                                                     no14, no15, no16, no17, no18, no19, no20)) AS p)
            SELECT  [seq],
                    ((CASE WHEN no1 IN ( SELECT     rn FROM t2 ) THEN 1 ELSE 0 END)
                     + (CASE WHEN no2 IN ( SELECT   rn FROM t2 ) THEN 1 ELSE 0 END)
                     + (CASE WHEN no3 IN ( SELECT   rn FROM t2 ) THEN 1 ELSE 0 END)
                     + (CASE WHEN no4 IN ( SELECT   rn FROM t2 ) THEN 1 ELSE 0 END)
                     + (CASE WHEN no5 IN ( SELECT   rn FROM t2 ) THEN 1 ELSE 0 END)
                     + (CASE WHEN no6 IN ( SELECT   rn FROM t2 ) THEN 1 ELSE 0 END)
                     + (CASE WHEN no7 IN ( SELECT   rn FROM t2 ) THEN 1 ELSE 0 END)
                     + (CASE WHEN no8 IN ( SELECT   rn FROM t2 ) THEN 1 ELSE 0 END)
                     + (CASE WHEN no9 IN ( SELECT   rn FROM t2 ) THEN 1 ELSE 0 END)
                     + (CASE WHEN no10 IN ( SELECT      rn FROM t2 ) THEN 1 ELSE 0 END)
                     + (CASE WHEN no11 IN ( SELECT      rn FROM t2 ) THEN 1 ELSE 0 END)
                     + (CASE WHEN no12 IN ( SELECT      rn FROM t2 ) THEN 1 ELSE 0 END)
                     + (CASE WHEN no13 IN ( SELECT      rn FROM t2 ) THEN 1 ELSE 0 END)
                     + (CASE WHEN no14 IN ( SELECT      rn FROM t2 ) THEN 1 ELSE 0 END)
                     + (CASE WHEN no15 IN ( SELECT      rn FROM t2 ) THEN 1 ELSE 0 END)
                     + (CASE WHEN no16 IN ( SELECT      rn FROM t2 ) THEN 1 ELSE 0 END)
                     + (CASE WHEN no17 IN ( SELECT      rn FROM t2 ) THEN 1 ELSE 0 END)
                     + (CASE WHEN no18 IN ( SELECT      rn FROM t2 ) THEN 1 ELSE 0 END)
                     + (CASE WHEN no19 IN ( SELECT      rn FROM t2 ) THEN 1 ELSE 0 END)
                     + (CASE WHEN no20 IN ( SELECT      rn FROM t2 ) THEN 1 ELSE 0 END)) AS same_RpNr  ---    相同的个数为 same_RpNr,
            INTO    #222
            FROM    #tb_S;
    
            --- 当same_RpNr=10 时表 #tb_S里的记录有许多条记录,但 select top (1) 仅仅取一条记录写入到表Result;
            SELECT seq, no1, no2, no3, no4, no5, no6, no7, no8, no9, no10, no11, no12, no13, no14, no15, no16, no17, no18, no19, no20 FROM #tb_S a
            WHERE   a.seq IN (   SELECT TOP (1) seq
                                 FROM   #222    --   select top (1) 仅仅取一条记录;
                                 WHERE  same_RpNr = 10 );
            FETCH NEXT FROM cur
            INTO @SNO;
        END;
    CLOSE cur;
    DEALLOCATE cur;
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(2条)

报告相同问题?

问题事件

  • 系统已结题 6月18日
  • 已采纳回答 6月10日
  • 修改了问题 6月8日
  • 创建了问题 6月8日

悬赏问题

  • ¥15 我想通过python处理sybase IQ库的一些数据,但是不知道sybase IQ库怎么连接,之前连接都是用linux上的 dbisql
  • ¥15 安装llama-cpp-python==0.1.83失败
  • ¥20 管道轴向耦合水击问题
  • ¥60 补全networkx TODO部分。
  • ¥15 有内推吗,云计算linux运维方向
  • ¥30 sort cuteSV.vcf by bcftools用IGV可视化出现报错
  • ¥100 SOS!对STK中导出的天体图像进行质心提取有没有人做过啊
  • ¥15 python 欧式距离
  • ¥15 运行qteasy报错
  • ¥15 遗传算法解决有工序顺序约束的大规模FJSP问题