找到多个组中的一个匹配组
我有一个表（@ t1），其中包含一些集合。 我想在@ t1中找到@ t2的完美匹配。
在这个例子中，期望的结果是1。
（set 1完美匹配，set 2包含三个元素，而@ t2仅包含两个元素，set 3包含的元素少于@ t2，set 4包含NULL元素，这些元素在@ t2中不允许，而set 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, 1),
(2, 2),
(2, 3),
(3, 1),
(4, NULL),
(4, NULL),
(5, 1),
(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);
你想要的就是所谓的精确关系分割 。 不幸的是，SQL Server对此没有本地操作符，但它是一个记录完备的问题。 一种可能的解决方案（从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比较都是必要的：
t2.data将通过LEFT JOIN为NULL。回想一下，COUNT（x）只计算x的非空值）。 解决这个问题的一种方法是连接每个ID的值和第二个表中的值并进行比较。 您也可以应用连接的顺序。
例如，以下代码将连接来自第二个表的值：
DECLARE @Test VARCHAR(MAX) = (
SELECT ',' + data
FROM @t2
ORDER BY data
FOR XML PATH(''), TYPE
).value('.', 'VARCHAR(MAX)')
SELECT @test -- 12
以下内容将为第一个表执行此操作：
SELECT id
,(
SELECT ',' + data
FROM @t1
WHERE id = t1.id
ORDER BY data
FOR XML PATH(''), TYPE
).value('.', 'VARCHAR(MAX)')
FROM @t1 t1
GROUP BY id
您可以使用where子句轻松过滤值：
SELECT *
FROM
(
SELECT id
,(
SELECT ',' + data
FROM @t1
WHERE id = t1.id
ORDER BY data
FOR XML PATH(''), TYPE
).value('.', 'VARCHAR(MAX)') data
FROM @t1 t1
GROUP BY id
) DS
WHERE data = @test
将T-SQL中的值连接起来看起来不太nice ，但是你可以使用SQL CLR来应用一个聚合连接函数（请SQL CLR本文），然后你会得到如下的结果：
DECLARE @Test VARCHAR(MAX);
SELECT @Test = [dbo].[Concatenate] (data)
FROM @t2
SELECT id
FROM @t1
GROUP BY id
HAVING [dbo].[Concatenate] (data) = @Test
也许这对你来说似乎更简单？
SELECT dat.id
FROM (
SELECT t1.id as id, t2.data as data2, sourcecount.cnt as scnt, dataCount.cnt as dcnt, COUNT(*) OVER(PARTITION BY t1.id) as mcnt
FROM @t1 as t1
INNER JOIN (SELECT t.id, COUNT(*) as cnt FROM @t1 as t GROUP BY t.id) as sourceCount
ON t1.id = sourceCount.id
INNER JOIN @t2 as t2
ON t1.data = t2.data
CROSS JOIN (SELECT COUNT(*) as cnt FROM @t2) as dataCount
) as dat
WHERE dat.scnt = dat.dcnt AND dat.mcnt = dat.dcnt
GROUP BY dat.id
它通过与您的执行计划相同的方式生成，但可能更具可读性。
最好的问候，离子
链接地址: http://www.djcxy.com/p/85671.html