如何编写SQL按状态值将同表datestamp列拆分为起止时间两列

适用场景说明 #

假设你的数据表名为equipment_status_records，可替换为实际表名，以下提供两种兼容不同数据库版本的实现方案： #

方案1：支持窗口函数的主流数据库（MySQL8.0+、PostgreSQL、SQL Server、Oracle等，性能更优） #

WITH filtered_events AS (
    -- 仅保留业务需要的开始/结束状态记录，过滤无效数据提升查询效率
    SELECT 
        equipment_type,
        machine_num,
        status,
        datestamp,
        CASE WHEN status IN (0,2,9) THEN 1 ELSE 0 END AS is_start
    FROM equipment_status_records
    WHERE status IN (0,2,9,1,3)
),
event_groups AS (
    SELECT 
        *,
        -- 按设备类型+机器号分组，按时间升序排序，累计开始事件数作为会话ID，同一会话对应一对起止时间
        SUM(is_start) OVER (PARTITION BY equipment_type, machine_num ORDER BY datestamp ASC) AS session_id
    FROM filtered_events
)
SELECT 
    equipment_type,
    machine_num,
    MIN(CASE WHEN is_start = 1 THEN datestamp END) AS starttime,
    MAX(CASE WHEN is_start = 0 THEN datestamp END) AS endtime
FROM event_groups
GROUP BY equipment_type, machine_num, session_id
-- 如需过滤仅存在开始时间无对应结束时间的记录，取消下方注释即可
-- HAVING MAX(CASE WHEN is_start = 0 THEN datestamp END) IS NOT NULL
ORDER BY equipment_type, machine_num, starttime ASC;

方案2：兼容不支持窗口函数的低版本数据库（如MySQL5.7及以下） #

SELECT 
    s.equipment_type,
    s.machine_num,
    s.datestamp AS starttime,
    MIN(e.datestamp) AS endtime
FROM equipment_status_records s
LEFT JOIN equipment_status_records e 
    ON s.equipment_type = e.equipment_type 
    AND s.machine_num = e.machine_num
    AND e.status IN (1,3)
    AND e.datestamp > s.datestamp
WHERE s.status IN (0,2,9)
GROUP BY s.equipment_type, s.machine_num, s.datestamp
-- 如需过滤仅存在开始时间无对应结束时间的记录，取消下方注释即可
-- HAVING endtime IS NOT NULL
ORDER BY s.equipment_type, s.machine_num, starttime ASC;

注意事项 #

• 若存在datestamp重复的场景，可在窗口函数或关联条件的排序规则中补充status或其他唯一字段，避免排序逻辑异常
• 连续多个开始事件无结束事件的场景下，结束时间仅会匹配给最近的一个开始事件，符合常规业务配对逻辑

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