【MS SQL】利用遞迴產生連續數字的記錄

例如：

有時候，我們想要查詢某商品在這個月每日的銷售數量，但該商品並不一定每天都有賣出，只在 1, 5, 10, 16, 22, 29 有銷售，所以查詢銷售記錄的結果如下

當記錄筆數一多，乍看報表無法立即知道哪幾天未售出。

 

但我們希望呈現的是 1/1 ~ 1/31，即使未售出也要顯示 銷售量為 0，報表可以很清楚看出哪幾天銷售量為 0，而且，如果之後要將記錄轉成圖表，例如：折線圖，畫出的線才會是連續的。

 

 

ex1：

產生 自 1 到 50 的連續記錄

 
;with sp as (
    select 1 as sppoint
    union all select sppoint + 1
    from sp
    where sppoint < 50
)
SELECT h.sppoint
FROM sp h
 

ex2：列出目前會員等級，自 等級1 ~ 等級50 的會員人數，及各等級消費總金額

 
;with sp as (
    select 1 as sppoint
    union all select sppoint + 1
    from sp
    where sppoint < 50
)
SELECT h.sppoint, isnull(cnt,0) cnt, isnull(amt,0) amt FROM sp h
left join
(
    select sppoint, count(*) cnt, sum(amount) amt from pointtable
        where  trsdate='20210101'
        group by sppoint
) b1 on h.sppoint=b1.sppoint