mysql年月分组语句
java代码
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')对时间年月进行了分组排序
java代码
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的列转行。其中关键点是case when的用法，用其来完成列转行的操作。
bitscn.com