我有一张类似的表:
table t1{ida,idb,idc}
我有tabletype变量中的记录列表
table t2{idb,idc}
现在,我想检查是否存在与所有t1
的组相同的t2
组,其中IDA组I组
尝试以下查询,该查询使用inner join
在表t1
和t2
中查找匹配行,并显示t1.ida
的值,其中有一个完整的匹配项
select a.ida
from t1 a inner join t2 b
on a.idb = b.idb and a.idc = b.idc
group by a.ida
having count(1) >= (select count(1) from t2)
似乎想要这样的东西:
DECLARE @t1 TABLE ( ida INT, idb INT, idc INT )
DECLARE @t2 TABLE ( idb INT, idc INT )
INSERT INTO @t1
VALUES ( 1, 1, 1 ),
( 1, 2, 1 ),
( 1, 3, 2 ),
( 2, 1, 1 ),
( 2, 2, 1 ),
( 2, 1, 2 )
INSERT INTO @t2
VALUES ( 1, 1 ),
( 2, 1 ),
( 3, 2 )
SELECT DISTINCT
t1.ida
FROM @t1 t1
WHERE NOT EXISTS ( SELECT *
FROM @t2 t2
WHERE NOT EXISTS ( SELECT *
FROM @t1
WHERE ida = t1.ida
AND idb = t2.idb
AND idc = t2.idc ) )
AND NOT EXISTS ( SELECT *
FROM @t1 tt1
WHERE ida = t1.ida
AND NOT EXISTS ( SELECT *
FROM @t2
WHERE idb = tt1.idb
AND idc = tt1.idc ) )
输出:
ida
1
因此,这仅返回ida
值,除了同一匹配数的数量外,还有一个完整的匹配。IE。以下仍将匹配:
( 1, 1, 1 ),
( 1, 2, 1 ),
( 1, 3, 2 ),
( 1, 3, 2 )