如下所示：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
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')对时间年月进行了分组排序
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
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列转行以及年月分组实例就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持服务器之家。
