直接以SQL語法列出某個Table依日期歸類計數的資料數

最近解決了一個問題:

該如何在SQL中,直接以SQL語法列出某個Table依日期歸類計數的資料數,並且保留無計數的日期。

傳統上如果只要做到 「以SQL語法列出Table中依日期歸類計數的資料數」。

SQL可能會類似這樣:

   SELECT VARCHAR_FORMAT(DATE(CREATETIME),’yyyy-mm-dd’) as 日期  , count(*) as 計數 FROM VG.EM

     WHERE createtime >= TIMESTAMP(‘2015-08-01 00:00:00’) AND  createtime <= TIMESTAMP(‘2015-08-26 23:59:59’)  GROUP BY DATE(CREATETIME)  with ur

上述SQL可能會產生類似這樣的結果:

日期 計數
2015-08-01 1
2015-08-03 2
2015-08-05 3
2015-08-07 4
2015-08-09 3
2015-08-11 2
2015-08-13 1

可是當如果我們期待資料是呈現以下的樣子:

日期 計數
2015-08-01 1
2015-08-02 0
2015-08-03 2
2015-08-04 0
2015-08-05 3
2015-08-06 0
2015-08-07 4
2015-08-08 0
2015-08-09 3
2015-08-10 0
2015-08-11 2
2015-08-12 0
2015-08-13 1

沒有計數的還是要列出日期並且將其標示為0,那SQL的寫法就是以下這個範例:

WITH TAB(DIA) AS (SELECT DATE(‘2015-08-01’) FROM SYSIBM.SYSDUMMY1 UNION ALL SELECT DIA + 1 DAY FROM TAB WHERE DIA < ‘2015-08-26’) 

SELECT DIA as 日期, COALESCE(c,0) as 計數 FROM TAB

 LEFT JOIN (

   SELECT VARCHAR_FORMAT(DATE(CREATETIME),’yyyy-mm-dd’) as d  , count(*) as c FROM VG.EM

     WHERE createtime >= ‘2015-08-01 00:00:00’ AND  createtime <= ‘2015-08-26 23:59:59’ AND MRTEMPLATE_ID = 23 GROUP BY DATE(CREATETIME) 

 ) ON DIA = d       

 ORDER BY DIA with ur

以上是DB2的寫法!

其他的資料庫,目前我只再試過了Oracle,Oracle SQL語法如下:

SELECT b.CDATE as 日期, NVL(a.v, 0) as 計數 FROM

( SELECT TO_CHAR( TO_DATE(‘2015-04-01’, ‘YYYY-MM-DD’) + (LEVEL-1), ‘YYYY-MM-DD’) AS CDATE FROM DUAL CONNECT BY LEVEL < (TO_DATE(‘2015-06-30′,’YYYY-MM-DD’)-TO_DATE(‘2015-04-01′,’YYYY-MM-DD’) + 2) ) b

LEFT JOIN (

SELECT TO_CHAR(EPINSTIME,’YYYY-MM-DD’) as CDATE ,COUNT(*) as v FROM E.ep WHERE EPTIME >= TO_DATE(‘2015-04-01’, ‘yyyy-mm-dd’) GROUP BY TO_CHAR(EPTIME,’YYYY-MM-DD’) ORDER BY TO_CHAR(EPTIME,’YYYY-MM-DD’)

) a ON a.CDATE = b.CDATE ORDER BY b.CDATE

廣告

About fenjj

Perfect !!??...
本篇發表於 Uncategorized。將永久鏈結加入書籤。

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

您的留言將使用 WordPress.com 帳號。 登出 / 變更 )

Twitter picture

您的留言將使用 Twitter 帳號。 登出 / 變更 )

Facebook照片

您的留言將使用 Facebook 帳號。 登出 / 變更 )

Google+ photo

您的留言將使用 Google+ 帳號。 登出 / 變更 )

連結到 %s