SQL 统计 字段 竖向转横向 （行转列）显示
公司某应用总体监控界面的一个场景 显示各个状态作业的数目，由于有个别作业链中作业数有上万了，整个作业链界面刷新效率要求需要在一条sql中解决
1 建表语句
1 create table wb ( 2 app_id int, 3 job_link_id varchar(255), 4 job_id varchar(255), 5 up_job_id varchar(255), 6 proc_status varchar(32) 7 )
2 初始化数据
1 insert into `wb`(`app_id`,`job_link_id`,`job_id`,`up_job_id`,`proc_status`) values 2 (1,'job_link2','job_id1','up_job_id2','N'), 3 (1,'job_link2','job_idc','up_job_id2','N'), 4 (1,'job_link2','job_ide','up_job_id2','N'), 5 (1,'job_link2','job_id1','up_job_id1','N'), 6 (1,'job_link2','job_id1','up_job_id1','N'), 7 (1,'job_link2','job_id1','up_job_id1','N'), 8 (1,'job_link2','job_id1','up_job_id1','N'), 9 (1,'job_linka','job_id1','up_job_id2','N'), 10 (1,'job_linkb','job_id1','up_job_id2','N'), 11 (1,'job_linkc','job_id1','up_job_id2','N'), 12 (1,'job_linkd','job_id1','up_job_idc','P'), 13 (1,'job_linkd','job_id1','up_job_idc','P'), 14 (1,'job_linkd','job_id1','up_job_idc','P'), 15 (1,'job_linkd','job_id1','up_job_idc','P'), 16 (1,'job_linke','job_id1','up_job_id2','R'), 17 (1,'job_linkf','job_id1','up_job_id2','C'), 18 (1,'job_linkg','job_id1','up_job_id2','E'), 19 (1,'job_linkh','job_id1','up_job_id2','R');
3 情景 统计作业链 中各个状态的数目 横向显示，统一作业的父作业可以有多个 ，但是该作业的本状态唯一
4 使用case when +临时表解决
1 select job_link_id 2 ,sum(case when proc_status='N' then total else 0 end) as 'N' 3 ,sum(case when proc_status='P' then total else 0 end) as 'P' 4 ,sum(case when proc_status='C' then total else 0 end) as 'C' 5 ,sum(case when proc_status='R' then total else 0 end) as 'R' 6 ,sum(case when proc_status='E' then total else 0 end) as 'E' 7 ,sum(case when proc_status='Q' then total else 0 end) as 'Q' 8 ,sum(total) as 'Total' 9 from ( 10 select distinct job_link_id,job_id,proc_status ,count(proc_status) as total from (select distinct job_link_id,job_id,proc_status from wb group by job_link_id,job_id) as temp1 group by job_link_id,job_id )as temp group by job_link_id;
5 显示结果
