我有一个包含许多集合的表(@ t1).我想在@ t1中找到@ t2的完美匹配.
在此示例中,所需结果为1.
(第1组完全匹配,第2组包含3个元素,而@ t2只包含2个元素,第3组包含的元素少于@ t2,第4组包含@ t2中不允许的NULL元素,第5组包含正确数量的元素但其中一个要素不相等.)
DECLARE @t1 TABLE (id INT,data INT); DECLARE @t2 TABLE (data INT PRIMARY KEY); INSERT INTO @t1 (id,data) VALUES (1,1),(1,2),(2,3),(3,(4,NULL),(5,3); INSERT @t2 (data) VALUES (1),(2);
我有一个查询可能正在完成工作,但它看起来有点可怜我也.
WITH t1 AS
(
SELECT id,data
FROM @t1
WHERE data IS NOT NULL
),t1_count AS
(
SELECT id,RCount = COUNT(*)
FROM @t1
WHERE data IS NOT NULL
GROUP BY id
)
SELECT t1.id
FROM t1
JOIN t1_count ON t1.id = t1_count.id
FULL JOIN @t2 t2 ON t1.data = t2.data
WHERE t1_count.RCount = (SELECT RCount = COUNT(*) FROM @t2)
GROUP BY t1.id
HAVING COUNT(t1.data) = COUNT(t2.data);
编辑(GarethD的评论)：
WITH t1 AS
(
SELECT
id,data,RCount = COUNT(*) OVER(PARTITION BY id)
FROM @t1
WHERE data IS NOT NULL
)
SELECT t1.id
FROM t1
FULL JOIN @t2 t2 ON t1.data = t2.data
WHERE t1.RCount = (SELECT RCount = COUNT(*) FROM @t2)
GROUP BY t1.id
HAVING COUNT(t1.data) = COUNT(t2.data);
解决方法
你想要的是Exact
Relational Division.不幸的是,sql Server没有本机操作符,但它是一个记录良好的问题.一个可能的解决方案(从
an article by Joe Celko获取的想法)是比较计数,类似于您已经在做的事情：
SELECT t1.id FROM @t1 AS t1 LEFT JOIN @t2 AS t2 ON t1.data = t2.data GROUP BY t1.id HAVING COUNT(t1.data) = (SELECT COUNT(data) FROM @t2) AND COUNT(t2.data) = (SELECT COUNT(data) FROM @t2);
请注意,两次HAVING比较都是必要的：
>第一个确保t1具有所需的行数和>第二个确保这些行只包含来自t2的值(否则,t2.data将通过LEFT JOIN为NULL.回想一下,COUNT(x)只计算x的非空值).
