欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

mysql列转行以及年月分组_MySQL

程序员文章站 2024-01-26 10:24:29
...
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
相关标签: mysql Java