SQL 统计 字段 竖向转横向 （行转列）显示

公司某应用总体监控界面的一个场景 显示各个状态作业的数目，由于有个别作业链中作业数有上万了，整个作业链界面刷新效率要求需要在一条sql中解决

1 建表语句

create table wb (
 app_id int,
  job_link_id varchar(255),
  job_id varchar(255),
  up_job_id varchar(255),
  proc_status varchar(32)   
)

2 初始化数据

insert  into `wb`(`app_id`,`job_link_id`,`job_id`,`up_job_id`,`proc_status`) values 
(1,'job_link2','job_id1','up_job_id2','N'),
(1,'job_link2','job_idc','up_job_id2','N'),
(1,'job_link2','job_ide','up_job_id2','N'),
(1,'job_link2','job_id1','up_job_id1','N'),
(1,'job_link2','job_id1','up_job_id1','N'),
(1,'job_link2','job_id1','up_job_id1','N'),
(1,'job_link2','job_id1','up_job_id1','N'),
(1,'job_linka','job_id1','up_job_id2','N'),
(1,'job_linkb','job_id1','up_job_id2','N'),
(1,'job_linkc','job_id1','up_job_id2','N'),
(1,'job_linkd','job_id1','up_job_idc','P'),
(1,'job_linkd','job_id1','up_job_idc','P'),
(1,'job_linkd','job_id1','up_job_idc','P'),
(1,'job_linkd','job_id1','up_job_idc','P'),
(1,'job_linke','job_id1','up_job_id2','R'),
(1,'job_linkf','job_id1','up_job_id2','C'),
(1,'job_linkg','job_id1','up_job_id2','E'),
(1,'job_linkh','job_id1','up_job_id2','R');

3 情景 统计作业链 中各个状态的数目 横向显示，统一作业的父作业可以有多个 ，但是该作业的本状态唯一

4 使用case when +临时表解决

select job_link_id
    ,sum(case when proc_status='N' then total else 0 end) as 'N'
    ,sum(case when proc_status='P' then total else 0 end) as 'P'
    ,sum(case when proc_status='C' then total else 0 end) as 'C'
    ,sum(case when proc_status='R' then total else 0 end) as 'R'
    ,sum(case when proc_status='E' then total else 0 end) as 'E'
    ,sum(case when proc_status='Q' then total else 0 end) as 'Q'
    ,sum(total) as 'Total'
from (
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 显示结果