如何在SQL Server中获取所有Address共有的RoomType值

需求说明 #

从Building_RoomTypes表中筛选出所有地址对应的记录都存在的唯一房间类型（即每个建筑地址都包含该房间类型）。

可复现的建表与插入数据代码（SQL Server 14.0.2065.1兼容） #

先创建表并插入模拟住宅、办公建筑共存的示例数据：

-- 创建表
CREATE TABLE Building_RoomTypes (
    ID INT PRIMARY KEY IDENTITY(1,1),
    Address NVARCHAR(100) NOT NULL,
    RoomType NVARCHAR(50) NOT NULL
);

-- 插入示例数据
INSERT INTO Building_RoomTypes (Address, RoomType)
VALUES
('住宅A', 'bathroom'),
('住宅A', 'kitchen'),
('住宅A', 'bedroom'),
('办公大楼B', 'bathroom'),
('办公大楼B', 'kitchen'),
('办公大楼B', 'meeting room'),
('住宅C', 'bathroom'),
('住宅C', 'kitchen'),
('住宅C', 'study');

查询解决方案 #

通过对比房间类型覆盖的地址数与总地址数，筛选出所有建筑都存在的房间类型：

SELECT RoomType
FROM Building_RoomTypes
GROUP BY RoomType
HAVING COUNT(DISTINCT Address) = (SELECT COUNT(DISTINCT Address) FROM Building_RoomTypes);

逻辑说明

1. 子查询SELECT COUNT(DISTINCT Address) FROM Building_RoomTypes计算表中所有不同的建筑地址总数；
2. 主查询按RoomType分组，统计每个房间类型对应的不同地址数量；
3. HAVING子句筛选出地址数量等于总地址数的房间类型，即为所有建筑都存在的房间类型。

执行后预期结果：

RoomType
--------
bathroom
kitchen

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