SQL学习杂项整理

sql统计同一字段（数据表同一列）不同值计数

1、比如，行为分析业务告警表业务类型字段，有区域入侵、攀高、绊线等需要统计2019/12/27分表中不同业务类型告警值
参照如下图：

2、统计alarm_type 不同值统计数，实现sql如下

SELECT sum(case when alarm_type = 11 then 1 else 0 end) as "11", sum(case when alarm_type = 12 then 1 else 0 end) as "12",
sum(case when alarm_type = 13 then 1 else 0 end) as "13", sum(case when alarm_type = 14 then 1 else 0 end) as "14", 
 sum(case when alarm_type = 15 then 1 else 0 end) as "15", sum(case when alarm_type = 16 then 1 else 0 end) as "16",
 sum(case when alarm_type = 17 then 1 else 0 end) as "17", sum(case when alarm_type = 18 then 1 else 0 end) as "18",
 sum(case when alarm_type = 19 then 1 else 0 end) as "19", sum(case when alarm_type = 20 then 1 else 0 end) as "20",
 sum(case when alarm_type = 31 then 1 else 0 end) as "31", sum(case when alarm_type = 32 then 1 else 0 end) as "32",
 sum(case when alarm_type = 33 then 1 else 0 end) as "33", sum(case when alarm_type = 41 then 1 else 0 end) as "41"
 FROM public.tbl_iaba_alarm_info_2019_12_26 where query_time >= 1577289600 and query_time <= 1577375999 and camera_id = '_206';

可以实现一次查询多个，注意事项：1、间隔逗号“,”必须使用英文；2、sum统计别名注意使用字符串，数字要使用双引号括起，英文可省略双引号
3、查询结果如下：

4、sum统计多个值，汇总统计总数，count(*)累加总和

SELECT sum(case when alarm_type = 11 then 1 else 0 end) as "11", sum(case when alarm_type = 12 then 1 else 0 end) as "12",
sum(case when alarm_type = 13 then 1 else 0 end) as "13", sum(case when alarm_type = 14 then 1 else 0 end) as "14", 
 sum(case when alarm_type = 15 then 1 else 0 end) as "15", sum(case when alarm_type = 16 then 1 else 0 end) as "16",
 sum(case when alarm_type = 17 then 1 else 0 end) as "17", sum(case when alarm_type = 18 then 1 else 0 end) as "18",
 sum(case when alarm_type = 19 then 1 else 0 end) as "19", sum(case when alarm_type = 20 then 1 else 0 end) as "20",
 sum(case when alarm_type = 31 then 1 else 0 end) as "31", sum(case when alarm_type = 32 then 1 else 0 end) as "32",
 sum(case when alarm_type = 33 then 1 else 0 end) as "33", sum(case when alarm_type = 41 then 1 else 0 end) as "41",
 count(*) as total
 FROM public.tbl_iaba_alarm_info_2019_12_26 where query_time >= 1577289600 and query_time <= 1577375999 and camera_id = '_206';