如何用SQL识别存在日期范围重叠的重复座位预订？

原SQL的问题在于，它仅通过分组desk_id, date_from, date_to统计重复，只能揪出日期范围完全一致的重复预订，没法检测到那些日期范围存在重叠的情况（比如同一座位同时被订了1月10-16日和1月12-13日）。

下面是修改后的SQL，专门用来识别同一座位上所有存在日期重叠的预订记录：

-- 先整理目标用户的座位分配记录，生成临时唯一标识避免自连接时匹配自己
WITH desk_allocations AS (
    SELECT 
        da.desk_id, 
        da.date_from, 
        da.date_to, 
        hr.name,
        -- 用ROW_NUMBER生成临时唯一ID，若表本身有分配记录的主键（比如allocation_id）直接用主键即可
        ROW_NUMBER() OVER(ORDER BY da.desk_id, da.date_from) AS temp_alloc_id
    FROM [human_resources].[dbo].[desks_temporary_allocations] da
    JOIN [human_resources].[dbo].hrms_mirror hr ON hr.sage_id = da.sage_id
    WHERE hr.name LIKE 'priyanka%'
)
-- 自连接匹配同一座位的重叠记录
SELECT DISTINCT
    main.desk_id,
    main.date_from AS 主预订开始日期,
    main.date_to AS 主预订结束日期,
    main.name AS 主预订人,
    overlap.date_from AS 重叠预订开始日期,
    overlap.date_to AS 重叠预订结束日期,
    overlap.name AS 重叠预订人
FROM desk_allocations main
JOIN desk_allocations overlap 
    ON main.desk_id = overlap.desk_id
    -- 排除同一条记录自己匹配自己
    AND main.temp_alloc_id <> overlap.temp_alloc_id
    -- 核心：判断两个日期范围是否重叠的条件，覆盖包含、交叉、部分重叠等所有场景
    AND main.date_from <= overlap.date_to
    AND main.date_to >= overlap.date_from
ORDER BY main.desk_id, main.date_from;

关键逻辑说明 #

1. CTE预处理：先筛选出目标用户（name以priyanka开头）的所有座位分配记录，同时生成临时唯一ID（避免自连接时同一条记录和自己匹配）。如果你的desks_temporary_allocations表本身有主键字段（比如allocation_id），直接替换掉temp_alloc_id即可。
2. 自连接匹配重叠：通过自连接关联同一座位的不同分配记录，用main.date_from <= overlap.date_to AND main.date_to >= overlap.date_from这个条件判断日期范围是否重叠，能覆盖所有可能的重叠场景。
3. 去重处理：用DISTINCT避免重复输出同一对重叠记录（比如A和B重叠时，不会同时出现A→B和B→A两条重复结果）。

如果需要汇总每个预订记录的重叠次数和具体信息，可以用下面这个版本：

WITH desk_allocations AS (
    SELECT 
        da.desk_id, 
        da.date_from, 
        da.date_to, 
        hr.name,
        ROW_NUMBER() OVER(ORDER BY da.desk_id, da.date_from) AS temp_alloc_id
    FROM [human_resources].[dbo].[desks_temporary_allocations] da
    JOIN [human_resources].[dbo].hrms_mirror hr ON hr.sage_id = da.sage_id
    WHERE hr.name LIKE 'priyanka%'
)
SELECT
    main.desk_id,
    main.date_from,
    main.date_to,
    main.name,
    COUNT(overlap.temp_alloc_id) AS 重叠次数,
    -- 把所有重叠的预订信息拼接成字符串（SQL Server 2017+支持STRING_AGG）
    STRING_AGG(CONCAT(overlap.name, '(', CONVERT(VARCHAR(10), overlap.date_from, 23), '至', CONVERT(VARCHAR(10), overlap.date_to, 23), ')'), '; ') AS 重叠详情
FROM desk_allocations main
LEFT JOIN desk_allocations overlap 
    ON main.desk_id = overlap.desk_id
    AND main.temp_alloc_id <> overlap.temp_alloc_id
    AND main.date_from <= overlap.date_to
    AND main.date_to >= overlap.date_from
GROUP BY main.desk_id, main.date_from, main.date_to, main.name
-- 只保留有重叠的记录
HAVING COUNT(overlap.temp_alloc_id) > 0
ORDER BY main.desk_id, main.date_from;

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