一条sql搞定设备在线离线统计问题_sql 计算离线时长

业务场景

对不同状态的设备进行统计

SELECT count(1)                                          AS total,
               sum(CASE WHEN handle_state = 1 THEN 1 ELSE 0 END) AS handleNum,
               sum(CASE WHEN handle_state = 2 THEN 1 ELSE 0 END) AS noticeNum,
               sum(CASE WHEN handle_state = 3 THEN 1 ELSE 0 END) AS developNum,
               sum(CASE WHEN handle_state = 4 THEN 1 ELSE 0 END) AS archiveNum
        FROM fire_handle
        WHERE to_days(alarm_time) = to_days(
                now())
1
2
3
4
5
6
7
8