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

如下所示：

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列转行以及年月分组实例就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持。