2021-01-04

【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
 

沒有留言:

張貼留言