请求编写assetstatus表SQL查询，满足日期与downtime特定逻辑

当前查询语句 #

select * from assetstatus WHERE assetnum = 'DY2867'

数据与需求说明 #

当前查询返回assetnum为DY2867的多条状态记录，每条包含isrunning（0代表停机开始，1代表停机结束）、changedate（状态变更时间）、downtime（停机时长）等字段。

需要将成对的状态记录合并为单条结果，规则如下：

• 取isrunning=0记录的changedate作为开始日期
• 取对应isrunning=1记录的changedate作为结束日期，同时取该记录的downtime值

解决方案SQL #

方案1：使用窗口函数（高效推荐） #

适用于状态记录按changedate顺序严格交替（0→1→0→1...）的场景：

SELECT
    assetnum,
    changedate AS start_date,
    LEAD(changedate) OVER (PARTITION BY assetnum ORDER BY changedate) AS end_date,
    LEAD(downtime) OVER (PARTITION BY assetnum ORDER BY changedate) AS downtime
FROM assetstatus
WHERE assetnum = 'DY2867'
  AND isrunning = 0
ORDER BY start_date;

方案2：使用自连接（兼容性更强） #

适用于需要精准匹配每个停机开始（0）对应最近停机结束（1）的场景：

SELECT
    a.assetnum,
    a.changedate AS start_date,
    b.changedate AS end_date,
    b.downtime
FROM assetstatus a
INNER JOIN assetstatus b 
    ON a.assetnum = b.assetnum
    AND b.isrunning = 1
    AND b.changedate = (
        SELECT MIN(changedate) 
        FROM assetstatus 
        WHERE assetnum = a.assetnum 
            AND isrunning = 1 
            AND changedate > a.changedate
    )
WHERE a.assetnum = 'DY2867'
  AND a.isrunning = 0
ORDER BY a.changedate;

逻辑说明 #

• 方案1通过LEAD()窗口函数，直接获取当前isrunning=0记录的下一条记录（即对应的isrunning=1记录）的changedate和downtime，实现成对合并。
• 方案2通过子查询找到每个停机开始记录之后最早的停机结束记录，再通过自连接关联数据，确保匹配的准确性。

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