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列转行以及年月分组实例就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持潘少俊衡。
版权声明
本文仅代表作者观点,不代表本站立场。
本文系作者授权发表,未经许可,不得转载。
本文地址:/shujuku/MySQL/106890.html