时间戳

首页 > 解决方案 > 与动态数量的表相交

sql - 与动态数量的表相交

问题描述

我有两个关系，a并且b，具有由给出的属性

CREATE TABLE a (id int, b_id int)
CREATE TABLE b (id int)

为此，我可以假设 in 中的所有值对a和 in 中的所有值b都是唯一的，并且将基于 SQL Server 2016 数据库。

的给定元素b定义了a.id由那些元素对应a.b_id的给定值的子集，我的目标是产生所有这些子集的交集。

比如说，它a包含六个值，

INSERT INTO a VALUES (1, 1), (1, 2), (1, 3), (2, 2), (3, 2), (3, 3)

那么预期的结果将包括以下内容：

b: (1), (2), (3). Expected result: (1)
b: (1), (2).      Expected result: (1)
b: (2).           Expected result: (1), (2), (3)
b: (2), (3).      Expected result: (1), (3)
b: [Empty].       Expected result: (1), (2), (3)

使用唯一性，对于非空的情况b，这可以通过

SELECT a.id FROM a
JOIN b on a.b_id = b.id
GROUP BY a.id
HAVING COUNT(a.id) = (SELECT COUNT(*) FROM b)

但这感觉很笨拙，因为 SQL 有一个INTERSECT现成的运算符，如果我在 LINQ 中编写相同的查询，我会简单地聚合交叉点。b如果不将其视为特殊情况，它也无法在空的情况下产生所需的结果。

所以，问题变成了：是否有一种更惯用的方式来执行上述查询，这也适用于 trivial b？

标签： sqlsql-servertsqlsql-server-2016

解决方案

您可以使用以下方法扩展处理空集案例的方法：

SELECT a.id
FROM a
LEFT JOIN  b on a.b_id = b.id
GROUP BY a.id
HAVING COUNT(b.id) = (SELECT COUNT(*) FROM b);

DBFiddle 演示 | DBFiddle Demo - 所有测试用例

额外：瞬态数据（在第二个演示中使用）

编辑：

另一种处理空集和离开的方法INNER JOIN：

SELECT a.id FROM a
JOIN b on a.b_id = b.id
GROUP BY a.id
HAVING COUNT(a.id) = (SELECT COUNT(*) FROM b)
UNION
SELECT a.id
FROM a
WHERE NOT EXISTS (SELECT 1 FROM b);

DBFiddle 演示 3

推荐阅读