MariaDB查询指定服务ID前数据及同天service=2后续可用时段SQL实现问题

解决方案 #

可以通过窗口函数先统计每日最晚的service=2时段，再做条件过滤即可实现需求，参考查询语句如下：

WITH daily_service2_info AS (
    SELECT 
        *,
        -- 按日期分组，取当日所有service=2的时段的最大值
        MAX(CASE WHEN service = 2 THEN timeslot END) OVER (PARTITION BY DATE(timeslot)) AS latest_s2_time
    FROM timeslots
)
SELECT timeslot 
FROM daily_service2_info
WHERE 
    service = 0
    AND (
        -- 当日无service=2时段则直接返回所有service=0的记录
        latest_s2_time IS NULL 
        -- 有service=2时段则仅返回晚于最晚service=2时段的记录
        OR timeslot > latest_s2_time
    )
ORDER BY timeslot ASC;

如果运行环境不支持CTE写法，也可以直接嵌套子查询实现，效果完全一致：

SELECT timeslot
FROM (
    SELECT 
        *,
        MAX(CASE WHEN service = 2 THEN timeslot END) OVER (PARTITION BY DATE(timeslot)) AS latest_s2_time
    FROM timeslots
) t
WHERE 
    service = 0
    AND (latest_s2_time IS NULL OR timeslot > latest_s2_time)
ORDER BY timeslot ASC;

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