請問 SQL 語法如何填補 原SQL 語法: SELECT M_PERIOD AS 月, COUNT (REC_MA) AS 筆數, SUM (REC_MA) AS 金額 FROM SSMM WHERE YYMM = '9211' GROUP BY M_PERIOD ORDER BY M_PERIOD 原結果: 月 筆數 金額 1 386 200.0 2 90 100.0 3 239 400.0 5 1 2400.0 8 580 18300.0 10 1 700.0 12 8 400.0 填補成為: 月 筆數 金額 1 386 200 2 90 100 3 239 400 4 0 0 5 1 2400 6 0 0 7 0 0 8 580 18300 9 0 0 10 1 700 11 0 0 12 8 400

Hi:    Sorry，因為平常都用 Oracle，MS SQL 語法我不太熟，你先試試以下 SQL，若有錯誤，請不吝指教

SELECT  月,
        SUM(筆數) 筆數,
        SUM(金額) 金額
FROM   (SELECT  M_PERIOD AS 月, COUNT (REC_MA) AS 筆數, SUM (REC_MA) AS 金額
        FROM    SSMM
        WHERE   YYMM = '9211' 
        GROUP BY
                M_PERIOD
        UNION
        SELECT 1 AS 月,0 AS 筆數,0 AS 金額
        UNION
        SELECT 2 AS 月,0 AS 筆數,0 AS 金額
        UNION
        SELECT 3 AS 月,0 AS 筆數,0 AS 金額
        UNION
        SELECT 4 AS 月,0 AS 筆數,0 AS 金額
        UNION
        SELECT 5 AS 月,0 AS 筆數,0 AS 金額
        UNION
        SELECT 6 AS 月,0 AS 筆數,0 AS 金額
        UNION
        SELECT 7 AS 月,0 AS 筆數,0 AS 金額
        UNION
        SELECT 8 AS 月,0 AS 筆數,0 AS 金額
        UNION
        SELECT 9 AS 月,0 AS 筆數,0 AS 金額
        UNION
        SELECT 10 AS 月,0 AS 筆數,0 AS 金額
        UNION
        SELECT 11 AS 月,0 AS 筆數,0 AS 金額
        UNION
        SELECT 12 AS 月,0 AS 筆數,0 AS 金額)
GROUP BY
        月
ORDER BY
        月

-------------------------------- 小弟才疏學淺，若有謬誤請不吝指教 --------------------------------

------
Fishman

Fishman前輩的語法應該可以達到你的需求, 不過我個人盡量避免使用子查詢, 因為相對而言子查詢比較慢, 我比要傾向先把需要的月份先做成一個暫存的Table或View, (前端跑回圈產生sql語法), 然後再以這個暫存的Table或View為主left join你的SSMM, 應該也可以達成你的需求...... ------------------------------ 我是個菜鳥,若有錯誤請勿見怪 吼..吼...吼....

------
吼..吼...吼....

Fishman 前輩你好: 你提供語法小弟已經測試過但是會出現錯誤 關鍵字 'GROUP' 附近的語法不正確。 單獨執行子查詢是OK ? 請問要如何排除

Hi:    我在 SQL Server 上試了一下，原則上使可以執行的，但是它把 Command 改為

SELECT  月,
        SUM(筆數) AS 筆數,
        SUM(金額) AS 金額
FROM   (SELECT  M_PERIOD AS 月,
                COUNT(REC_MA) AS 筆數,
                SUM(REC_MA) AS 金額
        FROM    SSMM
        WHERE   YYMM = '9211'
        GROUP BY
                M_PERIOD
        UNION
        SELECT         1 AS 月, 0 AS 筆數, 0 AS 金額
        UNION
        SELECT         2 AS 月, 0 AS 筆數, 0 AS 金額
        UNION
        SELECT         3 AS 月, 0 AS 筆數, 0 AS 金額
        UNION
        SELECT         4 AS 月, 0 AS 筆數, 0 AS 金額
        UNION
        SELECT         5 AS 月, 0 AS 筆數, 0 AS 金額
        UNION
        SELECT         6 AS 月, 0 AS 筆數, 0 AS 金額
        UNION
        SELECT         7 AS 月, 0 AS 筆數, 0 AS 金額
        UNION
        SELECT         8 AS 月, 0 AS 筆數, 0 AS 金額
        UNION
        SELECT         9 AS 月, 0 AS 筆數, 0 AS 金額
        UNION
        SELECT         10 AS 月, 0 AS 筆數, 0 AS 金額
        UNION
        SELECT         11 AS 月, 0 AS 筆數, 0 AS 金額
        UNION
        SELECT         12 AS 月, 0 AS 筆數, 0 AS 金額) DERIVEDTBL
GROUP BY  月
ORDER BY  月

你試試看是否可行 -------------------------------- 小弟才疏學淺，若有謬誤請不吝指教 --------------------------------

------
Fishman

Fishman 前輩你好: 加上 DERIVEDTBL 就可以了 非常感謝!