2021-01-04

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

例如:

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


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

 

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



 

 

ex1:

產生 自 1 到 50 的連續記錄

  1.  
  2. ;with sp as (
  3. select 1 as sppoint
  4. union all select sppoint + 1
  5. from sp
  6. where sppoint < 50
  7. )
  8. SELECT h.sppoint
  9. FROM sp h
  10.  


ex2:列出目前會員等級,自 等級1 ~ 等級50 的會員人數,及各等級消費總金額
  1.  
  2. ;with sp as (
  3. select 1 as sppoint
  4. union all select sppoint + 1
  5. from sp
  6. where sppoint < 50
  7. )
  8. SELECT h.sppoint, isnull(cnt,0) cnt, isnull(amt,0) amt FROM sp h
  9. left join
  10. (
  11. select sppoint, count(*) cnt, sum(amount) amt from pointtable
  12. where trsdate='20210101'
  13. group by sppoint
  14. ) b1 on h.sppoint=b1.sppoint
  15.  

沒有留言:

張貼留言