即使計數為0，如何按週分組

我下面的範例執行良好，我面臨的唯一挑戰是沒有顯示 0 結果的周。這是我的程式碼範例：

我目前的輸出：

week      | amount
4        |  354
6        |  222
7        |  144
8        |  354
9        |  45
10       |  55
11       |  76
12       |  98
13       |  45
14       |  344

上面的結果缺少很多周（1,2,3 和 15,16,17 等）我如何顯示那些計數為 0 的？

測試範例：

CREATE TABLE TABLE1 ( DATE datetime, COMPANY_ID varchar);
INSERT INTO TABLE1(DATE, COMPANY_ID)
VALUES 
('2019-01-24 00:00:00.000','Arla'),
('2019-01-24 00:00:00.000', 'B/S'),
('2019-01-24 00:00:00.000', 'NAaN'),
('2019-02-20 00:00:00.000', 'SuperBest'),
('2019-02-20 00:00:00.000', 'GS'),
('2019-04-29 00:00:00.000', 'Aldi'),
('2019-04-29 00:00:00.000', 'Netto'),
('2018-01-24 00:00:00.000', 'Arla');


;with ResultsTable as
(

SELECT 
DATENAME (WK, DATE) AS WEEK,
COUNT (DISTINCT COMPANY_ID) AS AMOUNT
FROM
(
SELECT COMPANY_ID, DATE = MIN(DATE)
FROM TABLE1 A 

WHERE YEAR(A.DATE) = '2019' AND 

COMPANY_ID NOT IN(SELECT COMPANY_ID  FROM TABLE1 WHERE DATE < '2019-01-01') 
GROUP BY COMPANY_ID) d    

GROUP BY dateadd(wk, datediff(wk, 0, DATE), 0),  DATENAME(WK, DATE) 

)
,WeekNumbers (WeekNumber)
AS (
SELECT 1 AS Number

UNION ALL

SELECT WeekNumber + 1
FROM WeekNumbers
WHERE WeekNumber < (
       SELECT datename(week, '2019-12-31')
       )
)
--join cte to original results
--when no match on week number, set amount to 0
SELECT wn.WeekNumber AS [week]
,COALESCE(rt.[week], 0) AS amount
FROM WeekNumbers wn
LEFT JOIN ResultsTable rt ON rt.[week] = wn.WeekNumber

解決方案是否可以像使用遞歸CTE生成一年中的幾週的數字表然後left join輸入到您的主查詢一樣簡單？當未找到週數匹配時，coalesce用於輸出實際金額或零？

--demo setup
Declare @ResultsTable table (
 [week] INTEGER,
 amount INTEGER
);

INSERT INTO @ResultsTable
 ([week], amount)
VALUES
 ('4', '354'),
 ('6', '222'),
 ('7', '144'),
 ('8', '354'),
 ('9', '45'),
 ('10', '55'),
 ('11', '76'),
 ('12', '98'),
 ('13', '45'),
 ('14', '344');

--recursive cte to generates week numbers
;WITH WeekNumbers (WeekNumber)
AS (
   SELECT 1 AS Number

   UNION ALL

   SELECT WeekNumber + 1
   FROM WeekNumbers
   WHERE WeekNumber < (
           SELECT datename(week, '2019-12-31')
           )
   )
--join cte to original results
--when no match on week number, set amount to 0
SELECT wn.WeekNumber AS [week]
   ,COALESCE(rt.amount, 0) AS amount
FROM WeekNumbers wn
LEFT JOIN @ResultsTable rt
   ON rt.[week] = wn.WeekNumber

---

| week | amount |
|------|--------|
| 1    | 0      |
| 2    | 0      |
| 3    | 0      |
| 4    | 354    |
| 5    | 0      |
| 6    | 222    |
| 7    | 144    |
| 8    | 354    |
| 9    | 45     |
| 10   | 55     |
| 11   | 76     |
| 12   | 98     |
| 13   | 45     |
| 14   | 344    |
| 15   | 0      |
| 16   | 0      |
| 17   | 0      |
| 18   | 0      |
| 19   | 0      |
| 20   | 0      |
| 21   | 0      |
| 22   | 0      |
| 23   | 0      |
| 24   | 0      |
| 25   | 0      |
| 26   | 0      |
| 27   | 0      |
| 28   | 0      |
| 29   | 0      |
| 30   | 0      |
| 31   | 0      |
| 32   | 0      |
| 33   | 0      |
| 34   | 0      |
| 35   | 0      |
| 36   | 0      |
| 37   | 0      |
| 38   | 0      |
| 39   | 0      |
| 40   | 0      |
| 41   | 0      |
| 42   | 0      |
| 43   | 0      |
| 44   | 0      |
| 45   | 0      |
| 46   | 0      |
| 47   | 0      |
| 48   | 0      |
| 49   | 0      |
| 50   | 0      |
| 51   | 0      |
| 52   | 0      |
| 53   | 0      |

---

因此，使用合併到公用表表達式中的原始查詢，最終解決方案可能如下所示：

;with ResultsTable as
(
SELECT 
DATENAME (WK, DATE) AS WEEK,
COUNT (DISTINCT COMPANY_ID) AS AMOUNT
FROM
(
SELECT COMPANY, DATE = MIN(DATE)
FROM TABLE1 A INNER JOIN TABLE2 B
ON A.ID = B.ID
WHERE YEAR(A.DATE) = '2019' AND COMPANY_ID NOT IN(SELECT COMPANY_ID  FROM 
TABLE1 A INNER JOIN TABLE2 B  ON A.ID = B.ID AND DATE < '2019-01-01') GROUP 
BY COMPANY_ID) d    

GROUP BY dateadd(wk, datediff(wk, 0, DATE), 0),  DATENAME(WK, DATE) 
)
,WeekNumbers (WeekNumber)
AS (
   SELECT 1 AS Number

   UNION ALL

   SELECT WeekNumber + 1
   FROM WeekNumbers
   WHERE WeekNumber < (
           SELECT datename(week, '2019-12-31')
           )
   )
--join cte to original results
--when no match on week number, set amount to 0
SELECT wn.WeekNumber AS [week]
   ,COALESCE(rt.amount, 0) AS amount
FROM WeekNumbers wn
LEFT JOIN ResultsTable rt
   ON rt.[week] = wn.WeekNumber

引用自：https://dba.stackexchange.com/questions/236779