问题出处:http://topic.csdn.net/u/20091209/14/554a7480-c6c1-4005-a4dc-bfbf99813c96.html?84247
A 表
Name Count Date
AA 2 2009/11/10
AA 5 2009/7/10
AA 3 2009/6/11
AA 9 2009/11/18
AA 4 2009/2/10
AA 6 2009/9/14
AA 3 2009/3/10
AA 6 2009/4/10
AA 8 2009/11/1
AA 5 2009/11/19
AA 4 2009/2/10
AA 6 2008/11/15
查詢條件 為: 2009年
需要得到的結果為: A表
Date Name Count
1月 AA 0
2月 AA 8
3月 AA 3
4月 AA 6
5月 AA 0
6月 AA 3
7月 AA 5
8月 AA 0
9月 AA 6
10月 AA 0
11月 AA 30
12月 AA 0
謝謝!
- WITH TMP_A AS (
- SELECT T.NAME, TO_CHAR(T.DATE1, 'MM') AS MM, SUM(T.COUNT) AS COUNT
- FROM A T
- GROUP BY TO_CHAR(T.DATE1, 'MM'), T.NAME
- ORDER BY TO_CHAR(T.DATE1, 'MM')
- ),
- TMP_TABLE AS (
- SELECT LEVEL AS TMP_LEVEL
- FROM DUAL
- CONNECT BY LEVEL < 13
- )
- SELECT
- TMP_TABLE.TMP_LEVEL || '月',
- CASE WHEN TMP_A.COUNT IS NULL THEN 0
- ELSE TMP_A.COUNT END AS COUNT
- FROM
- TMP_A,
- TMP_TABLE
- WHERE
- TMP_A.MM (+) = TMP_TABLE.TMP_LEVEL
- ORDER BY
- TMP_TABLE.TMP_LEVEL