bus_sid logtype subtype sn logtime message curstatustype
21521 2 231 1 2009-11-27 15:15:18 预警 1
21521 2 231 2 2009-11-27 15:15:18 预警 1
21521 2 231 3 2009-11-27 15:15:18 预警 1
21521 2 231 4 2009-11-27 15:15:18 预警 1
21521 2 238 1 2009-12-24 17:55:34 黄牌 8有上面一列数据 查询后要统计出:
bus_sid yj yyj ryj yp rp
21521 1 0 0 1 0
curstatustype(预警统计状态:当前预警黄牌红牌状态 1:预警;2:黄牌预警;5:红牌预警,8:黄牌 11:红牌)这条SQL 怎么写好呢!!!求救各位大侠!!!分不够可以追加!
21521 2 231 1 2009-11-27 15:15:18 预警 1
21521 2 231 2 2009-11-27 15:15:18 预警 1
21521 2 231 3 2009-11-27 15:15:18 预警 1
21521 2 231 4 2009-11-27 15:15:18 预警 1
21521 2 238 1 2009-12-24 17:55:34 黄牌 8有上面一列数据 查询后要统计出:
bus_sid yj yyj ryj yp rp
21521 1 0 0 1 0
curstatustype(预警统计状态:当前预警黄牌红牌状态 1:预警;2:黄牌预警;5:红牌预警,8:黄牌 11:红牌)这条SQL 怎么写好呢!!!求救各位大侠!!!分不够可以追加!
---------- --------------------
21521 yj
21521 yj
21521 yj
21521 yj
21521 yp
SQL> select bus_sid,
2 max(decode(sn,'yj',1,0)) yj,
3 max(decode(sn,'yyj',1,0)) yyj,
4 max(decode(sn,'ryj',1,0)) ryj,
5 max(decode(sn,'yp',1,0)) yp,
6 max(decode(sn,'rp',1,0)) rp
7 from t1
8 group by bus_sid; BUS_SID YJ YYJ RYJ YP RP
---------- ---------- ---------- ---------- ---------- ----------
21521 1 0 0 1 0
你模范着做一下就可以了
select busi_sid,
sum(case when curstatustype='1' then 1 else 0 end ) yj,
sum(case when curstatustype='2' then 1 else 0 end ) yyj,
sum(case when curstatustype='5' then 1 else 0 end ) ryj,
sum(case when curstatustype='8' then 1 else 0 end ) yp,
sum(case when curstatustype='11' then 1 else 0 end ) rp
from t_jc_ms_busi_log
group by busi_sid
现在用这个decode 简洁多了。。受教了
select busi_sid ,
max(decode(curstatustype,'1',1,0)) yj,
max(decode(curstatustype,'2',1,0)) yyj,
max(decode(curstatustype,'5',1,0)) ryj,
max(decode(curstatustype,'8',1,0)) yp,
max(decode(curstatustype,'11',1,0)) rp
from (select busi_sid,curstatustype from t_jc_ms_busi_log )
group by busi_sid;