您的当前位置:首页正文

mysql年月分组语句

2020-11-09 来源:爱够旅游网

欢迎进入Linux社区论坛,与200万技术人员互动交流 >>进入 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 ('1

欢迎进入Linux社区论坛,与200万技术人员互动交流 >>进入

  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的用法,用其来完成列转行的操作。

显示全文