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 显示结果

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

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