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;