熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> MySQL >> 正文

mysql年月分組語句

2013-11-12 23:40:23  來源: MySQL 

  Java代碼

  SELECT count(DISTINCT(arect_id)) zcount ajob_dept

  DATE_FORMAT(submit_date %Y%m) zsubmit_date

  FROM

  表名 a

  WHERE

  astatu =

  AND arstatu =

  AND ajob_dept IN (

  GROUP BY

  ajob_dept

  DATE_FORMAT(submit_date %Y%m

  其中關鍵在於DATE_FORMAT(submit_date %Y%m)對時間年月進行了分組排序

  Java代碼

  SELECT

  zsubmit_date

  MAX(CASE WHEN job_dept = THEN zcount ELSE END ) zcount

  MAX(CASE WHEN job_dept = THEN zcount ELSE END ) zcount

  MAX(CASE WHEN job_dept = THEN zcount ELSE END ) zcount

  FROM

  (

  SELECT

  count(DISTINCT(arect_id)) zcount ajob_dept

  DATE_FORMAT(submit_date %Y%m) zsubmit_date

  FROM

  表名 a

  WHERE

  astatu =

  AND arstatu =

  AND ajob_dept IN (

  GROUP BY

  ajob_dept

  DATE_FORMAT(submit_date %Y%m

  ) q

  GROUP BY

  zsubmit_date

  以上是mysql的列轉行其中關鍵點是case when的用法用其來完成列轉行的操作


From:http://tw.wingwit.com/Article/program/MySQL/201311/11208.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.