mysql 分组列转行,mysql列转行以及年月分组_MySQL

最新推荐文章于 2024-04-06 20:56:42 发布

weixin_33047553

最新推荐文章于 2024-04-06 20:56:42 发布

阅读量213

点赞数

文章标签： 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 elsebitsCN.com

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：php中文网