如何選擇過去一周的記錄總數，包括 MySQL 中沒有記錄的天數

本質上，我需要一個可以輸出如下內容的查詢：

+------------+-------+
| date       | count |
+------------+-------+
| 2020-09-30 | 10    |
| 2020-09-29 | 3     |
| 2020-09-28 | 0     |
| 2020-09-27 | 6     |
| 2020-09-26 | 0     |
| 2020-09-25 | 5     |
| 2020-09-24 | 3     |
+------------+-------+

我知道其他問題會問類似的問題，但如果計數為 0，我所看到的輸出 0 都沒有。他們這樣做了：

+------------+-------+
| date       | count |
+------------+-------+
| 2020-09-30 | 10    |
| 2020-09-29 | 3     |
| 2020-09-27 | 6     |
| 2020-09-25 | 5     |
| 2020-09-24 | 3     |
+------------+-------+

最直接的方法是使用包含所有可能日期的日期表，然後在適當的情況下使用 OUTER APPLY 來獲取每個日期的命中數。

像這樣的東西：

SELECT  d.Date
       ,ISNULL(hc.HitCount, 0) AS HitCount
FROM    dbo.Date AS d
       OUTER APPLY (
                       SELECT  COUNT(1) AS HitCount
                       FROM    Table AS t
                       WHERE   1 = 1
                               AND t.Date = d.Date
                   ) AS hc
WHERE   1 = 1
       AND d.Date BETWEEN @StartDate AND @EndDate;

這是建構和填充日期表的腳本：

CREATE PROC [dbo].[usp_GrowDateDimension]
(
   @Years TINYINT NULL = 25
)
AS
DECLARE @MaxDate DATE;

IF NOT EXISTS (
                 SELECT    1
                 FROM      sys.objects AS o
                 WHERE     o.name = 'Date'
             )
BEGIN
   CREATE TABLE dbo.Date
   (
       DateKey               INT          NOT NULL
       ,FullDateAlternateKey DATE         NOT NULL
       ,DayNumberOfWeek      TINYINT      NOT NULL
       ,EnglishDayNameOfWeek NVARCHAR(10) NOT NULL
       ,DayNumberOfMonth     TINYINT      NOT NULL
       ,DayNumberOfYear      SMALLINT     NOT NULL
       ,WeekNumberOfYear     TINYINT      NOT NULL
       ,EnglishMonthName     NVARCHAR(10) NOT NULL
       ,MonthNumberOfYear    TINYINT      NOT NULL
       ,CalendarQuarter      TINYINT      NOT NULL
       ,CalendarYear         SMALLINT     NOT NULL
       ,CalendarSemester     TINYINT      NOT NULL
       ,Yesterday            AS (CASE
                                     WHEN FullDateAlternateKey = CONVERT(DATE, DATEADD(DAY, (-1), GETDATE())) THEN (1)
                                     ELSE (0)
                                 END
                                )
   );
END;

/* Get ceiling of Date dimension */
SELECT  @MaxDate = MAX(d.FullDateAlternateKey)
FROM    dbo.Date AS d
WHERE   d.FullDateAlternateKey <> '2999-12-31';

/* Set Start/Stop points based on ceiling */
DECLARE @CurrentDate DATE = DATEADD(DAY, 1, ISNULL(@MaxDate, '2000-12-31'));
DECLARE @EndDate DATE = DATEADD(YEAR, @Years, @CurrentDate);

/* Insert Rows */
WHILE @CurrentDate < @EndDate
BEGIN
   INSERT INTO dbo.Date
   (
       DateKey
       ,FullDateAlternateKey
       ,DayNumberOfWeek
       ,EnglishDayNameOfWeek
       ,DayNumberOfMonth
       ,DayNumberOfYear
       ,WeekNumberOfYear
       ,EnglishMonthName
       ,MonthNumberOfYear
       ,CalendarQuarter
       ,CalendarYear
       ,CalendarSemester
   )
   SELECT  YEAR(@CurrentDate) * 10000 + MONTH(@CurrentDate) * 100 + DAY(@CurrentDate) AS DateKey
           ,@CurrentDate AS FullDateAlternateKey
           ,DATEPART(dw, @CurrentDate) AS DayNumberOfWeek
           ,DATENAME(dw, @CurrentDate) AS EnglishDayNameOfWeek
           ,DAY(@CurrentDate) AS DayNumberOfMonth
           ,DATENAME(dy, @CurrentDate) AS DayNumberOfYear
           ,DATEPART(wk, @CurrentDate) AS WeekNumberOfYear
           ,DATENAME(mm, @CurrentDate) AS EnglishMonthName
           ,MONTH(@CurrentDate) AS MonthNumberOfYear
           ,DATEPART(q, @CurrentDate) AS CalendarQuarter
           ,YEAR(@CurrentDate) AS CalendarYear
           ,((DATEPART(QUARTER, @CurrentDate) - 1) / 2) + 1 AS CalendarSemester
   WHERE   NOT EXISTS  (
                           SELECT  1
                           FROM    dbo.Date AS d
                           WHERE   d.FullDateAlternateKey = @CurrentDate
                       );

   SET @CurrentDate = DATEADD(DD, 1, @CurrentDate);
END;

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