sql Server 查询15天之内的数据，按年月日分组，空数据需要补0

sql 代码：

WITH DateRange AS (
    SELECT DATEADD(DAY, -15, CONVERT(DATE, GETDATE())) AS StartDate
    UNION ALL
    SELECT DATEADD(DAY, 1, StartDate)
    FROM DateRange
    WHERE StartDate < CONVERT(DATE, GETDATE())
)
SELECT
    CONVERT(VARCHAR(100), StartDate, 23) AS '年月日',
    RIGHT('00' + CAST(DAY(dr.StartDate) AS VARCHAR(2)), 2) AS [Day],
    ISNULL(COUNT(t.ZDYtime), 0) AS [DataCount]
FROM
    DateRange dr
LEFT JOIN
    TabelA t ON CAST(t.ZDYtime AS DATE) = dr.StartDate
GROUP BY
    CONVERT(VARCHAR(100), ZDYtime, 23) ,dr.StartDate
ORDER BY
    dr.StartDate DESC; 

sql 结果 

 注：TableA 是实际查询数据表名，ZDYtime 是 实际查询筛选的时间

原理：查询递归 15天的时间去跟左连接实际表名的时间。

【WHERE StartDate < CONVERT(DATE, GETDATE())】：循环条件。

如果查询前一个月的数据可【DATEADD(DAY, -30, CONVERT(DATE, GETDATE())) AS StartDate】直接减30