SQL Server技术问题：查找存在于所有时段/序列的room_ID

我有一张表Y，已插入room_ID、时段字段（date_from、date_to）和序列字段seq。需要找出存在于**所有不同序列（时段）**的room_ID，比如示例中的room_ID 2就符合要求——它在seq为1、2、3的所有时段里都有记录。

示例数据的SQL代码如下：

DECLARE @y TABLE
(room_id    numeric(10,0)   null,
 date_from  datetime        null,
 date_to    datetime        null,
 seq        int             null)

INSERT @y
SELECT 2, '2023-05-01','2023-05-02',1

INSERT @y
SELECT 5, '2023-05-01','2023-05-02',1

INSERT @y
SELECT 8, '2023-05-01','2023-05-02',1

INSERT @y
SELECT 2, '2023-05-02','2023-05-03',2

INSERT @y
SELECT 8, '2023-05-02','2023-05-03',2

INSERT @y
SELECT 2, '2023-05-07','2023-05-09',3

解决方案 #

方法1：分组统计匹配序列数

先统计总共有多少个不同的seq，再分组统计每个room_id对应的distinct seq数量，两者相等的就是符合要求的room_id：

-- 先获取总序列数
DECLARE @total_seq INT = (SELECT COUNT(DISTINCT seq) FROM @y);

-- 筛选出覆盖所有序列的room_id
SELECT room_id
FROM @y
GROUP BY room_id
HAVING COUNT(DISTINCT seq) = @total_seq;

方法2：用EXCEPT排除缺失序列的room_id

先生成所有room_id和所有seq的笛卡尔积，再减去表中已有的room_id+seq组合，剩下的就是存在缺失序列的room_id，最后取补集：

SELECT DISTINCT room_id
FROM @y
WHERE room_id NOT IN (
    SELECT r.room_id
    FROM (SELECT DISTINCT room_id FROM @y) r
    CROSS JOIN (SELECT DISTINCT seq FROM @y) s
    EXCEPT
    SELECT room_id, seq FROM @y
);

两种方法都能得到目标结果：room_id = 2。

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