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日

悬赏问题

  • ¥20 matlab可以把多个二维图合成为三维瀑布图吗
  • ¥15 EEPROM,软件i2c
  • ¥500 求解读该段JS代码,需要知道是用干什么的
  • ¥20 qt4代码实现二进制文件读取显示,以及显示的内容进行搜索
  • ¥15 Labview获取LK-G3001数据
  • ¥15 我知道什么是混合树,但是怎么写代码啊
  • ¥50 开发板linux系统安装dpkg,apt函数库 有偿
  • ¥15 浏览器时间循环 交互事件和延时事件的 优先级与执行问题
  • ¥15 GD模块安装出错,libgd无法正常安装
  • ¥20 求有缘人帮我把笛卡尔坐标系转换为经纬度 有偿