哈哈网前两天一直在想一个需求，就是同一张表如何去根据不同时间获取不同的数据，因为SQL不太熟悉，所以想了很久最后想到在sql语句里使用case when进行判断，在这里将代码分享给大家，本人小白一个，大神勿喷
[SQL] 纯文本查看 复制代码
select a.*,e.name AS EName, y.aname AS alarmName,q.name AS typeName from (select t.* from sys_alarmevent t where equipmentTypeId =5 and t.`status`=0 and ADDTIME(t.alarmTimeId,'24:00:00') >= NOW() union all select t.* from sys_alarmevent t where equipmentTypeId !=5 and t.`status`=0 and ADDTIME(t.alarmTimeId,'00:05:00') >= NOW()) a LEFT JOIN sys_equipment e ON e.equipment_id = a.equipmentId LEFT JOIN sys_alarmtype y ON a.alarmtypeid=y.id LEFT JOIN sys_eqlx q ON a.equipmenttypeid=q.id order by a.alarmTimeId DESC
下面这个是根据另一张表里查的内容进行判断状态，如果另一张表能查出数据就说明是有信息的然后进行状态的分别赋值
[SQL] 纯文本查看 复制代码
select DISTINCT t.* , case WHEN (select count(0) from sys_alarmevent s where s.equipmentTypeId =5 and s.`status`=0 and ADDTIME(s.alarmTimeId,'24:00:00') >= NOW() and t.equipment_id=s.equipmentId) =0 then 0 else 1 end as alarmStatus from sys_equipment t where t.lx_id =5 and ab_status=0 union all select DISTINCT t.* , case WHEN (select count(0) from sys_alarmevent s where s.equipmentTypeId !=5 and s.`status`=0 and ADDTIME(s.alarmTimeId,'300') >= NOW() and t.equipment_id=s.equipmentId) =0then 0 else 1 end as alarmStatusfrom sys_equipment t where t.lx_id !=5 and ab_status=0
