【SQL】sql统计不同类别的不同状态的数目
例子:某主机下有5149个设备,设备分不同类别,设备运行会有不同状态(1-正常、2-告警、3-故障、0-离线)
sql:
SELECT t.DEVICE_TYPE_NAME,
SUM(CASE t.DEVICE_STATUS WHEN '1' THEN 1 ELSE 0 END) AS '正常',
SUM(CASE t.DEVICE_STATUS WHEN '3' THEN 1 ELSE 0 END) AS '故障',
SUM(CASE t.DEVICE_STATUS WHEN '0' THEN 1 ELSE 0 END) AS '离线',
SUM(CASE t.DEVICE_STATUS WHEN '2' THEN 1 ELSE 0 END) AS '告警'
FROM(SELECT * FROM `device_info` WHERE FIRE_SYSTEM_ID=1 and CATEGORY_ID=2 and DELETED=0 AND DIST_ID=5) t
GROUP BY t.DEVICE_TYPE_NAME
查询结果: