如何编写SQL查询计算指定时间段内人员在地点的停留天数？

当然可以实现，核心是计算每条记录的停留区间与查询日期范围的重叠天数，同时处理DataOut为空（即未完成离开登记）的情况。

核心逻辑 #

1. 确定每条记录的有效停留区间：
   • 若DataOut为空，默认停留截止到查询的结束日期
   • 否则使用DataOut作为停留结束时间
2. 计算有效区间与查询范围的重叠部分：
   • 重叠起始日：取DataIn和查询起始日期的较大值
   • 重叠结束日：取有效停留结束时间和查询结束日期的较小值
3. 若重叠起始日晚于结束日，天数记为0；否则计算两日期间的天数差（按你的预期结果，这里采用结束日减起始日的差值，不包含起始日当天）

具体SQL实现（以MySQL为例） #

先定义查询的起始和结束日期，再执行查询：

-- 设置查询日期范围（格式：dd/mm/yyyy）
SET @query_start = STR_TO_DATE('01/01/2022', '%d/%m/%Y');
SET @query_end = STR_TO_DATE('31/01/2022', '%d/%m/%Y');

SELECT
    Id_registration,
    Id_Person,
    Id_Location,
    -- 计算重叠天数
    GREATEST(
        0,
        DATEDIFF(
            -- 取有效结束日与查询结束日的较小值
            LEAST(COALESCE(STR_TO_DATE(DataOut, '%d/%m/%Y'), @query_end), @query_end),
            -- 取DataIn与查询起始日的较大值
            GREATEST(STR_TO_DATE(DataIn, '%d/%m/%Y'), @query_start)
        )
    ) AS `Days Result`
FROM
    your_registration_table
-- 可选：过滤完全不重叠的记录，提升查询效率
WHERE
    STR_TO_DATE(DataIn, '%d/%m/%Y') <= @query_end
    AND COALESCE(STR_TO_DATE(DataOut, '%d/%m/%Y'), @query_end) >= @query_start;

适配其他数据库的调整 #

• SQL Server：用CONVERT(DATE, DataIn, 103)替换STR_TO_DATE（103对应dd/mm/yyyy格式），天数计算改为DATEDIFF(day, 起始日, 结束日)
• Oracle：用TO_DATE(DataIn, 'DD/MM/YYYY')转换日期，天数计算用TRUNC(结束日) - TRUNC(起始日)
• PostgreSQL：用TO_DATE(DataIn, 'DD/MM/YYYY')转换日期，天数直接用结束日 - 起始日

结果匹配说明 #

• 记录1：重叠区间为03/01/2022至15/01/2022，天数差为12，符合预期
• 记录2：重叠区间为16/01/2022至31/01/2022，天数差为15，符合预期
• 注：你给出的预期结果中记录3的信息与原始数据不符（原始数据为Id_Person:2、Id_Location:1，DataIn为10/10/2022，该日期超出查询结束日31/01/2022，实际重叠天数为0），若要得到预期的2天结果，需调整该记录的DataIn为30/01/2022这类在查询范围内的日期

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