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的列转行。其中关键点是case when的用法，用其来完成列转行的操作。用法等同于if else