欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

mysql列转行以及年月分组实例

程序员文章站 2023-11-18 17:48:28
如下所示: select count(distinct(a.rect_id)) zcount, a.job_dept,  date_format(...

如下所示:

select count(distinct(a.rect_id)) zcount, a.job_dept, 

date_format(submit_date, '%y-%m') zsubmit_date 

from 

表名 a 

where 

a.statu = 3 

and a.rstatu = 2 

and a.job_dept in ('19', '20', '21') 

group by 

a.job_dept, 

date_format(submit_date, '%y-%m')

其中关键在于date_format(submit_date, '%y-%m')对时间年月进行了分组排序

select      
zsubmit_date,      
max(case when job_dept = '19' then zcount else 0 end ) 19zcount,      
max(case when job_dept = '20' then zcount else 0 end ) 20zcount,      
max(case when job_dept = '21' then zcount else 0 end ) 21zcount  

from    
(     
select       
count(distinct(a.rect_id)) zcount, a.job_dept,        
date_format(submit_date, '%y-%m') zsubmit_date     
from       
表名 a     
where       
a.statu = 3       
and a.rstatu = 2       
and a.job_dept in ('19', '20', '21')     
group by     
a.job_dept,     
date_format(submit_date, '%y-%m')    
) q  group by    
zsubmit_date 

以上这篇mysql列转行以及年月分组实例就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持。