oracle 分类统计sql总结
1、根据年份统计12个月的汇总信息，没有数据的月显示0，重点是如何分别显示12个月
select train.*,terri.territoryname from ( SELECT s.sponsor,s.regioncode, count(case when TO_CHAR(s.START_TIME, 'MM')='01' then 'm1' end) || '/' || sum(case when TO_CHAR(s.START_TIME, 'MM')='01' then SIGNUP_NUMBERS else '0' end) m1, count(case when TO_CHAR(s.START_TIME, 'MM')='02' then 'm2' end) || '/' || sum(case when TO_CHAR(s.START_TIME, 'MM')='02' then SIGNUP_NUMBERS else '0' end) m2, count(case when TO_CHAR(s.START_TIME, 'MM')='03' then 'm3' end) || '/' || sum(case when TO_CHAR(s.START_TIME, 'MM')='03' then SIGNUP_NUMBERS else '0' end) m3, count(case when TO_CHAR(s.START_TIME, 'MM')='04' then 'm4' end) || '/' || sum(case when TO_CHAR(s.START_TIME, 'MM')='04' then SIGNUP_NUMBERS else '0' end) m4, count(case when TO_CHAR(s.START_TIME, 'MM')='05' then 'm5' end) || '/' || sum(case when TO_CHAR(s.START_TIME, 'MM')='05' then SIGNUP_NUMBERS else '0' end) m5, count(case when TO_CHAR(s.START_TIME, 'MM')='06' then 'm6' end) || '/' || sum(case when TO_CHAR(s.START_TIME, 'MM')='06' then SIGNUP_NUMBERS else '0' end) m6, count(case when TO_CHAR(s.START_TIME, 'MM')='07' then 'm7' end) || '/' || sum(case when TO_CHAR(s.START_TIME, 'MM')='07' then SIGNUP_NUMBERS else '0' end) m7, count(case when TO_CHAR(s.START_TIME, 'MM')='08' then 'm8' end) || '/' || sum(case when TO_CHAR(s.START_TIME, 'MM')='08' then SIGNUP_NUMBERS else '0' end) m8, count(case when TO_CHAR(s.START_TIME, 'MM')='09' then 'm9' end) || '/' || sum(case when TO_CHAR(s.START_TIME, 'MM')='09' then SIGNUP_NUMBERS else '0' end) m9, count(case when TO_CHAR(s.START_TIME, 'MM')='10' then 'm10' end) || '/' || sum(case when TO_CHAR(s.START_TIME, 'MM')='10' then SIGNUP_NUMBERS else '0' end) m10, count(case when TO_CHAR(s.START_TIME, 'MM')='11' then 'm11' end) || '/' || sum(case when TO_CHAR(s.START_TIME, 'MM')='11' then SIGNUP_NUMBERS else '0' end) m11, count(case when TO_CHAR(s.START_TIME, 'MM')='12' then 'm12' end) || '/' || sum(case when TO_CHAR(s.START_TIME, 'MM')='12' then SIGNUP_NUMBERS else '0' end) m12, count(s.id) ||'/'||sum(SIGNUP_NUMBERS) nums FROM SH_TRAIN s where to_char(s.start_time,'YYYY')='2018' and s.is_publish='1' and s.regioncode like '6523%' group by s.sponsor,s.regioncode ) train left join t_s_territory terri on train.regioncode = terri.id
最终查询结果如下，其中月份中显示两个字段 “培训次数/参加培训的人数”：
2、decode方法使用：
select count(1) as cnt,decode(max(ssex),0,'男','女') as sex from staffmgtview where orgid = '40288016615433bf0161543f9899000d' group by ssex order by ssex asc;
输出结果：
3、case when根据生日将所有员工信息分年龄段汇总输出
select case when sbirthday is null then '年龄不详' when floor(MONTHS_BETWEEN(sysdate,sbirthday)/12) < 20 then '20岁以下' when floor(MONTHS_BETWEEN(sysdate,sbirthday)/12) between 20 and 29 then '20到29岁' when floor(MONTHS_BETWEEN(sysdate,sbirthday)/12) between 30 and 39 then '30到39岁' when floor(MONTHS_BETWEEN(sysdate,sbirthday)/12) between 40 and 49 then '40到49岁' when floor(MONTHS_BETWEEN(sysdate,sbirthday)/12) between 50 and 59 then '50到59岁' when floor(MONTHS_BETWEEN(sysdate,sbirthday)/12)>=60 then '60岁以上' end as age_level, count(1) as cnt from staffmgtview where orgid = '40288016615433bf0161543f9899000d' group by case when sbirthday is null then '年龄不详' when floor(MONTHS_BETWEEN(sysdate,sbirthday)/12) < 20 then '20岁以下' when floor(MONTHS_BETWEEN(sysdate,sbirthday)/12) between 20 and 29 then '20到29岁' when floor(MONTHS_BETWEEN(sysdate,sbirthday)/12) between 30 and 39 then '30到39岁' when floor(MONTHS_BETWEEN(sysdate,sbirthday)/12) between 40 and 49 then '40到49岁' when floor(MONTHS_BETWEEN(sysdate,sbirthday)/12) between 50 and 59 then '50到59岁' when floor(MONTHS_BETWEEN(sysdate,sbirthday)/12)>=60 then '60岁以上' end;
输出结果：
4、联合查询后分组输出，根据字典表动态分组：
select count(1) as cnt, max(dic.typename) as position from staffmgtview left join (SELECT tstype.typecode as typecode, tstype.typename as typename FROM t_s_type tstype WHERE tstype.typegroupid = (SELECT id FROM t_s_Typegroup gro WHERE gro.typegroupcode = 'POST')) dic on dic.typecode = staffmgtview.SPOST where orgid = '40288016615433bf0161543f9899000d' group by spost order by spost asc;
输出结果：
5、一年的十二个月分组统计
1）横着显示：
SELECT sum(case when TO_CHAR(s.create_date, 'MM')='01' then '1' else '0' end) m1, sum(case when TO_CHAR(s.create_date, 'MM')='02' then '1' else '0' end) m2, sum(case when TO_CHAR(s.create_date, 'MM')='03' then '1' else '0' end) m3, sum(case when TO_CHAR(s.create_date, 'MM')='04' then '1' else '0' end) m4, sum(case when TO_CHAR(s.create_date, 'MM')='05' then '1' else '0' end) m5, sum(case when TO_CHAR(s.create_date, 'MM')='06' then '1' else '0' end) m6, sum(case when TO_CHAR(s.create_date, 'MM')='07' then '1' else '0' end) m7, sum(case when TO_CHAR(s.create_date, 'MM')='08' then '1' else '0' end) m8, sum(case when TO_CHAR(s.create_date, 'MM')='09' then '1' else '0' end) m9, sum(case when TO_CHAR(s.create_date, 'MM')='10' then '1' else '0' end) m10, sum(case when TO_CHAR(s.create_date, 'MM')='11' then '1' else '0' end) m11, sum(case when TO_CHAR(s.create_date, 'MM')='12' then '1' else '0' end) m12, sum('1') nums FROM oldcheckinview s where to_char(s.create_date,'YYYY')='2018' and org_id = '40288002623167ae0162319816b30131' group by TO_CHAR(s.create_date, 'MM');
结果：
2）竖着显示：
select * from ( select sum(case when TO_CHAR(s.create_date, 'MM')='01' then '1' else '0' end) m1, sum(case when TO_CHAR(s.create_date, 'MM')='02' then '1' else '0' end) m2, sum(case when TO_CHAR(s.create_date, 'MM')='03' then '1' else '0' end) m3, sum(case when TO_CHAR(s.create_date, 'MM')='04' then '1' else '0' end) m4, sum(case when TO_CHAR(s.create_date, 'MM')='05' then '1' else '0' end) m5, sum(case when TO_CHAR(s.create_date, 'MM')='06' then '1' else '0' end) m6, sum(case when TO_CHAR(s.create_date, 'MM')='07' then '1' else '0' end) m7, sum(case when TO_CHAR(s.create_date, 'MM')='08' then '1' else '0' end) m8, sum(case when TO_CHAR(s.create_date, 'MM')='09' then '1' else '0' end) m9, sum(case when TO_CHAR(s.create_date, 'MM')='10' then '1' else '0' end) m10, sum(case when TO_CHAR(s.create_date, 'MM')='11' then '1' else '0' end) m11, sum(case when TO_CHAR(s.create_date, 'MM')='12' then '1' else '0' end) m12, sum('1') nums from oldcheckinview s where org_id = '40288002623167ae0162319816b30131' and TO_CHAR(s.create_date, 'YYYY')='2018' ) unpivot (sum2015 for years in (m1,m2,m3,m4,m5,m6,m7,m8,m9,m10,m11,m12,nums) );
效果如图：
6、分组统计之后排序，需要巧用引入虚拟表进行排序
select a.age_level,decode(b.cnt,null,0,b.cnt) as cnt from ( select '20岁以下' age_level,1 age_order from dual union all select '20到29岁' age_level,2 age_order from dual union all select '30到39岁' age_level,3 age_order from dual union all select '40到49岁' age_level,4 age_order from dual union all select '50到59岁' age_level,5 age_order from dual union all select '60岁以上' age_level,6 age_order from dual) a left join (select case when sbirthday is null then '年龄不详' when floor(MONTHS_BETWEEN(sysdate,sbirthday)/12) < 20 then '20岁以下' when floor(MONTHS_BETWEEN(sysdate,sbirthday)/12) between 20 and 29 then '20到29岁' when floor(MONTHS_BETWEEN(sysdate,sbirthday)/12) between 30 and 39 then '30到39岁' when floor(MONTHS_BETWEEN(sysdate,sbirthday)/12) between 40 and 49 then '40到49岁' when floor(MONTHS_BETWEEN(sysdate,sbirthday)/12) between 50 and 59 then '50到59岁' when floor(MONTHS_BETWEEN(sysdate,sbirthday)/12)>=60 then '60岁以上' end as age_level, count(1) as cnt from staffmgtview where orgid = '40288002616e0e9801616ef640460271' group by case when sbirthday is null then '年龄不详' when floor(MONTHS_BETWEEN(sysdate,sbirthday)/12) < 20 then '20岁以下' when floor(MONTHS_BETWEEN(sysdate,sbirthday)/12) between 20 and 29 then '20到29岁' when floor(MONTHS_BETWEEN(sysdate,sbirthday)/12) between 30 and 39 then '30到39岁' when floor(MONTHS_BETWEEN(sysdate,sbirthday)/12) between 40 and 49 then '40到49岁' when floor(MONTHS_BETWEEN(sysdate,sbirthday)/12) between 50 and 59 then '50到59岁' when floor(MONTHS_BETWEEN(sysdate,sbirthday)/12)>=60 then '60岁以上' end ) b on a.age_level = b.age_level order by a.age_order;
