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

Question

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

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

Answer 1

-- #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;