问题一:不用每次手动修改sno,如何修改脚本批量自动完成比较?请专家答疑解惑.
代码上传不了,所以写在下面的题主回答里,
-- 问题一:不用每次手动修改sno,如何修改脚本批量自动完成比较;
--问题二:想要得到的结果是S表的数据,但 每一行 有对应的SNO 例如:
-- #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;
用循环处理，尽量不要用游标，比较费内存
如果SNO是连续的，直接这么循环，否则在循环里面加判断，存在这个SNO才继续处理，不存在只执行@SQN+1
--- 逐一比较 相同的个数为 same_RpNr,如何批量完成sno =2021001.......2021006
declare @SNO int=2021001
while @SNO<2021006
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
);
set @SNO=@SNO+1
end
-- #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
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 =2021001 -- 每次手动修改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
);