验证SQL Server表中迁移的记录

Question

我有一个场景，我将数据从表t1传输到t2。并非所有记录都被转移。我想弄清楚哪些身份证被完全转移了。例如，我有一个数据子集，其中ID 23和25都有5个记录，每个记录都有不同的部分和子部分。由于ID 23中只有2条记录被转移到t2，所以我不希望它们出现在我的结果查询中。然而，对于ID 25，所有记录都被转移到t2。所以，我希望它能反映在我的结果中。

我得到了这么远的

select * 
from t2
where exists (select * from t1 
where t1.id = t2.id 
and t1.section = t2.section
and t1.sub = t2.sub
group by id)

表1

id  section sub
----------------
23  1   9
23  1   10
23  2   2
23  3   2
23  3   3
24  1   9
24  1   10
24  2   2
24  3   2
24  3   3
25  1   9
25  1   10
25  2   2
25  3   2
25  3   3

表2

id  section sub
----------------
23  1   9
23  1   10
25  1   9
25  1   10
25  2   2
25  3   2
25  3   3

所需结果：

id  section sub
---------------
25  1   9
25  1   10
25  2   2
25  3   2
25  3   3

创建表的代码

CREATE TABLE t1 
(
id varchar(3),
section varchar(4), 
sub varchar(2)
)
CREATE TABLE t2 
(
id varchar(3),
section varchar(4), 
sub varchar(2)
)
INSERT INTO t1 (id, section, sub)
VALUES ('23', '1', '9')
INSERT INTO t1 (id, section, sub)
VALUES ('23', '1', '10')
INSERT INTO t1 (id, section, sub)
VALUES ('23', '2', '2')
INSERT INTO t1 (id, section, sub)
VALUES ('23', '3', '2')
INSERT INTO t1 (id, section, sub)
VALUES ('23', '3', '3')
INSERT INTO t1 (id, section, sub)
VALUES ('24', '1', '9')
INSERT INTO t1 (id, section, sub)
VALUES ('24', '1', '10')
INSERT INTO t1 (id, section, sub)
VALUES ('24', '2', '2')
INSERT INTO t1 (id, section, sub)
VALUES ('24', '3', '2')
INSERT INTO t1 (id, section, sub)
VALUES ('24', '3', '3')
INSERT INTO t1 (id, section, sub)
VALUES ('25', '1', '9')
INSERT INTO t1 (id, section, sub)
VALUES ('25', '1', '10')
INSERT INTO t1 (id, section, sub)
VALUES ('25', '2', '2')
INSERT INTO t1 (id, section, sub)
VALUES ('25', '3', '2')
INSERT INTO t1 (id, section, sub)
VALUES ('25', '3', '3')

INSERT INTO t2 (id, section, sub)
VALUES ('23', '1', '9')
INSERT INTO t2 (id, section, sub)
VALUES ('23', '1', '10')
INSERT INTO t2 (id, section, sub)
VALUES ('25', '1', '9')
INSERT INTO t2 (id, section, sub)
VALUES ('25', '1', '10')
INSERT INTO t2 (id, section, sub)
VALUES ('25', '2', '2')
INSERT INTO t2 (id, section, sub)
VALUES ('25', '3', '2')
INSERT INTO t2 (id, section, sub)
VALUES ('25', '3', '3')

Answer 1

您可以尝试以下操作，只需计算t2中每个ID的数量，并将其与t1中相同的数量进行比较，其中它们是相同的：

select id,section,sub from (
select *, Count(*) over(partition by id) c
from t2
)t
where c=(
select Count(*)
from t1
where t1.id=t.id
group by t1.id
)