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列转行以及年月分组实例就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持。
上一篇: MySQL 5.7中的关键字与保留字详解
下一篇: Spring中Bean的生命周期使用解析