SQL Server 2016中动态多表交集及两表子集交集查询

嘿，我来帮你搞定这个SQL交集查询的问题！针对你提到的SQL Server 2016环境下的表结构和需求，我分两种场景给你详细解法：

先明确下核心需求：

• 表a的(id, b_id)组合唯一，表b的id唯一
• 每个b.id对应表a中a.b_id = b.id的a.id子集
• 需要找出所有这些子集的交集（即出现在每一个子集里的a.id）
• 同时要支持动态数量的b_id集合求交集

假设我们有示例数据：

-- 插入表a的6条记录
INSERT INTO a (id, b_id) VALUES 
(1, 1), (2, 1), (3, 1),
(2, 2), (3, 2), (4, 2);

-- 插入表b的记录
INSERT INTO b (id) VALUES (1), (2);

这里b.id=1对应子集{1,2,3}，b.id=2对应子集{2,3,4}，交集就是{2,3}。

固定场景：求所有b元素对应子集的交集 #

这种情况我们可以利用分组统计的思路：只有那些在每个b_id对应的子集中都出现的a.id，它关联的b_id数量才等于表b的总记录数。

对应的SQL查询：

SELECT id
FROM a
GROUP BY id
-- 统计该id关联的不同b_id数量，等于b表总条数则说明在所有子集里都存在
HAVING COUNT(DISTINCT b_id) = (SELECT COUNT(*) FROM b);

执行后会返回2和3，正好是我们要的交集。

注：因为题目明确a的(id,b_id)唯一，所以用COUNT(*)替代COUNT(DISTINCT b_id)也能得到正确结果，但保留DISTINCT会让查询更严谨，避免后续数据出现重复时出错。

动态场景：任意数量b_id的交集查询 #

如果不是固定用表b的所有元素，而是需要动态指定一组b_id（比如用户输入3个b_id求交集），这里有两种常用解法：

方法1：使用表值参数（推荐用于存储过程） #

SQL Server支持自定义表类型，适合在存储过程中接收动态数量的参数：

1. 先创建表类型：

CREATE TYPE dbo.BIdList AS TABLE (Id INT);

2. 然后编写查询（或存储过程）：

-- 声明表变量并插入动态的b_id
DECLARE @TargetBIds dbo.BIdList;
INSERT INTO @TargetBIds VALUES (1), (2); -- 这里可以插入任意数量的b_id

SELECT a.id
FROM a
JOIN @TargetBIds b ON a.b_id = b.Id
GROUP BY a.id
-- 统计关联的b_id数量等于传入的总数
HAVING COUNT(DISTINCT a.b_id) = (SELECT COUNT(*) FROM @TargetBIds);

方法2：使用字符串拆分（适合临时Ad Hoc查询） #

如果是临时查询，不想创建表类型，可以用SQL Server 2016自带的STRING_SPLIT函数拆分逗号分隔的b_id字符串：

-- 动态传入的b_id列表，用逗号分隔
DECLARE @BIdString NVARCHAR(MAX) = '1,2';

-- 先去重避免重复统计
WITH UniqueBIds AS (
    SELECT DISTINCT CAST(value AS INT) AS Id
    FROM STRING_SPLIT(@BIdString, ',')
)
SELECT a.id
FROM a
JOIN UniqueBIds b ON a.b_id = b.Id
GROUP BY a.id
HAVING COUNT(DISTINCT a.b_id) = (SELECT COUNT(*) FROM UniqueBIds);

内容的提问来源于stack exchange，提问作者fuglede