如何在SQL Server中使用日期范围内的堆叠CTE或日历表

WITH DateRange AS (
    SELECT @StartDate AS Date
    UNION ALL
    SELECT DATEADD(DAY, 1, Date)
    FROM DateRange
    WHERE Date < @EndDate
)

SELECT Date
FROM DateRange
OPTION (MAXRECURSION 0)

CREATE TABLE Calendar (
    Date DATE PRIMARY KEY,
    Weekday VARCHAR(10),
    Month VARCHAR(10),
    Year INT,
    -- 其他列
)

DECLARE @CurrentDate DATE = @StartDate

WHILE @CurrentDate <= @EndDate
BEGIN
    INSERT INTO Calendar (Date, Weekday, Month, Year)
    VALUES (@CurrentDate, DATENAME(WEEKDAY, @CurrentDate), DATENAME(MONTH, @CurrentDate), YEAR(@CurrentDate))

    SET @CurrentDate = DATEADD(DAY, 1, @CurrentDate)
END

SELECT *
FROM YourTable
INNER JOIN Calendar ON YourTable.Date = Calendar.Date
WHERE Calendar.Date BETWEEN @StartDate AND @EndDate