经典sql记要

经典sql记录
1、聚合函数行转列：
select name,sum(case when subName='语文' then grade else 0 end) 语文,
sum(case when subName='数学' then grade else 0 end) 数学,
sum(case when subName='英语' then grade else 0 end) 英语
from t_user group by name
2、统计异常个数
select device_ip,channel_name,
SUM(IF(b.secondValue>0 AND b.secondValue<=60,1,0)) 60s内,
SUM(IF(b.secondValue>60 AND b.secondValue<=180,1,0)) 1到3m,
SUM(IF(b.secondValue>180 AND b.secondValue<=300,1,0)) 3到5m,
SUM(IF(b.secondValue>300 AND b.secondValue<=600,1,0)) 5到10m,
SUM(IF(b.secondValue>600 AND b.secondValue<=30*60,1,0)) 10至30m内,
SUM(IF(b.secondValue>30*60 AND b.secondValue<=60*60,1,0)) 30至60m内,
SUM(IF(b.secondValue>1*60*60 AND b.secondValue<=3*60*60,1,0)) 1至3h内,
SUM(IF(b.secondValue>3*60*60 AND b.secondValue<=5*60*60,1,0)) 3至5h内,
SUM(IF(b.secondValue>5*60*60 AND b.secondValue<=8*60*60,1,0)) 5至8h内,
SUM(IF(b.secondValue>8*60*60 AND b.secondValue<=12*60*60,1,0)) 8至12h内,
SUM(IF(b.secondValue>12*60*60 AND b.secondValue<24*60*60 - 1,1,0)) 12至24h内,
SUM(IF(b.secondValue>=24*60*60 - 1 ,1,0)) 24h及以上
from
(select  td.device_ip,tdc.channel_name,a.secondValue
from (
select *,TIMESTAMPDIFF(SECOND,CONCAT(record_lack_date,' ',record_lack_starttime),CONCAT(record_lack_date,' ',record_lack_endtime)) secondValue
from t_device_remote_record_exception where record_lack_date between '2016-06-20' and '2016-07-19'
) a LEFT JOIN t_device td ON a.device_id=td.device_id
LEFT JOIN t_device_channel tdc ON a.device_id=tdc.device_id and a.channel_number=tdc.channel_number
) b
group by device_ip,channel_name