mysql列转行以及年月分组实例

如下所示：

SELECT count(DISTINCT(a.rect_id)) zcount, a.job_dept,DATE_FORMAT(submit_date, '%Y-%m') zsubmit_dateFROM表名 aWHEREa.statu = 3AND a.rstatu = 2AND a.job_dept IN ('19', '20', '21')GROUP BYa.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/' target='_blank'>mysql列转行以及年月分组实例就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持脚本之家。