oracle case 多层,oracle 中 CASE WHEN 用法 同时并 GROUP BY 分组

oracle 中 CASE WHEN 用法 同时并 GROUP BY 分组

oracle 中 CASE WHEN 用法 同时并 GROUP BY 分组

列表数据 查询 未处理 作废 和通过的数据

业务逻辑： 想查询警报表的一些信息,因为警报消息多的时候,就时间不一样,所以未读的警报只需要取一条和一个最开始的时间和最近的一个时间就行,但是删除 通过 作废的时候,需要修改这些记录 ,所以取出的记录ALARM_ID需要用逗号拼接,

在实际情况中碰到到一种业务逻辑，根据某个字段不同的值，查询不同的表

所以想到CASE WHEN这个关键字

写法如下

SELECT DISTINCT

b.MSG,

b.ALARM_ID,

b.ADDTIME,

b.MSG_STATE,

w.PLAN_DETAIL_ID,

w.IFRECHECK,

CASE

WHEN w.IFRECHECK = 0 THEN

( SELECT s.SMALL_CLASS_DESC FROM SQMT_PLAN_DETAIL s WHERE s.id = w.PLAN_DETAIL_ID )

WHEN w.IFRECHECK = 1 THEN

( SELECT p.SMALL_CLASS_DESC FROM SQMT_PLAN_RECHECK p WHERE p.id = w.PLAN_DETAIL_ID )

END smallClassDesc

FROM

SQMT_ALARM b

LEFT JOIN SQMT_DEVICE_WATCH w ON b.MSG = w.RFIDNUMBER

WHERE

b.MSG_STATE = 0

ORDER BY

b.MSG_STATE,

b.ADDTIME DESC

后来发现还需要取时间的最大最小值，所以需要分组，

但是删除的时候需要删除所有已取出的 msg 的记录，所有这里需要把分组后的表主键alarmId 用逗号拼接返回前台(wm_concat(tmp.ALARM_ID) alarmId,)，后面遍历删除 ，sql 如下，

注意事项： 用子查询分组，子查询语句是需要查询的数据

SELECT

tmp.msg,

wm_concat(tmp.ALARM_ID) alarmId,

max( tmp.ADDTIME ) maxtime,

min( tmp.ADDTIME ) mintime,

max( tmp.PLAN_DETAIL_ID ) id,

max( tmp.IFRECHECK ) IFRECHECK,

count( tmp.msg ) num,

max( tmp.MSG_STATE ) msgState,

max( tmp.SMALLCLASSDESC ) smallClassDesc

FROM

(

SELECT DISTINCT

b.MSG,

b.ALARM_ID,

b.ADDTIME,

b.MSG_STATE,

w.PLAN_DETAIL_ID,

w.IFRECHECK,

CASE

WHEN w.IFRECHECK = 0 THEN

( SELECT s.SMALL_CLASS_DESC FROM SQMT_PLAN_DETAIL s WHERE s.id = w.PLAN_DETAIL_ID )

WHEN w.IFRECHECK = 1 THEN

( SELECT p.SMALL_CLASS_DESC FROM SQMT_PLAN_RECHECK p WHERE p.id = w.PLAN_DETAIL_ID )

END smallClassDesc

FROM

SQMT_ALARM b

LEFT JOIN SQMT_DEVICE_WATCH w ON b.MSG = w.RFIDNUMBER

WHERE

b.MSG_STATE = 0

AND find_in_set ( 'liuanbo', b.SEND_USER_ID ) > 0

ORDER BY

b.MSG_STATE,

b.ADDTIME DESC

) tmp

GROUP BY

tmp.msg

但是MSG_STATE 其余的状态我也想要，这里用了UNION all 聚合

select * from (

SELECT

tmp.msg,

wm_concat(tmp.ALARM_ID) alarmId,

max( tmp.ADDTIME ) maxtime,

min( tmp.ADDTIME ) mintime,

max( tmp.PLAN_DETAIL_ID ) id,

max( tmp.IFRECHECK ) IFRECHECK,

count( tmp.msg ) num,

max( tmp.MSG_STATE ) msgState,

max( tmp.SMALLCLASSDESC ) smallClassDesc

FROM

(

SELECT DISTINCT

b.MSG,

b.ALARM_ID,

b.ADDTIME,

b.MSG_STATE,

w.PLAN_DETAIL_ID,

w.IFRECHECK,

CASE

WHEN w.IFRECHECK = 0 THEN

( SELECT s.SMALL_CLASS_DESC FROM SQMT_PLAN_DETAIL s WHERE s.id = w.PLAN_DETAIL_ID )

WHEN w.IFRECHECK = 1 THEN

( SELECT p.SMALL_CLASS_DESC FROM SQMT_PLAN_RECHECK p WHERE p.id = w.PLAN_DETAIL_ID )

END smallClassDesc

FROM

SQMT_ALARM b

LEFT JOIN SQMT_DEVICE_WATCH w ON b.MSG = w.RFIDNUMBER

WHERE

b.MSG_STATE = 0

AND find_in_set ( 'liuanbo', b.SEND_USER_ID ) > 0

ORDER BY

b.MSG_STATE,

b.ADDTIME DESC

) tmp

GROUP BY

tmp.msg

UNION all

SELECT

DISTINCT

t.MSG msg,

concat( t.ALARM_ID,'') alarmId,

null as maxtime,

t.ADDTIME mintime,

w.PLAN_DETAIL_ID id,

w.IFRECHECK,

1 as num ,

t.MSG_STATE,

CASE

WHEN w.IFRECHECK = 0 THEN

( SELECT s.SMALL_CLASS_DESC FROM SQMT_PLAN_DETAIL s WHERE s.id = w.PLAN_DETAIL_ID )

WHEN w.IFRECHECK = 1 THEN

( SELECT p.SMALL_CLASS_DESC FROM SQMT_PLAN_RECHECK p WHERE p.id = w.PLAN_DETAIL_ID )

END smallClassDesc

FROM

SQMT_ALARM t

LEFT JOIN SQMT_DEVICE_WATCH w ON t.MSG = w.RFIDNUMBER

WHERE

t.MSG_STATE in (1,2) and find_in_set('liuanbo', t.SEND_USER_ID)>0

) t2

ORDER BY t2.msgState,t2.mintime desc

oracle 中 CASE WHEN 用法 同时并 GROUP BY 分组相关教程