mysql列转行以及年月分组_MySQL

bitsCN.com

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的用法，用其来完成列转行的操作。用法等同于if else

bitsCN.com