如何在SQL中动态获取过去30天日期并实现数据透视统计

现有一段SQL代码可按日期统计特定国家接收的文件数量，但日期为硬编码形式。需修改代码，实现每次执行查询时自动获取过去30天的数据。

原SQL代码 #

with t (Country ,Date,total)
as
(
select b.country as Market, CAST(a.ProcessDate AS Date) AS DATE, count(a.ProcessDate) AS total from Log a LEFT JOIN File b ON a.FileID = b.FileID where a.ProcessDate BETWEEN '2022-11-01' AND '2022-11-07' GROUP BY b.country, CAST(a.ProcessDate AS DATE)
)

Select 
    *
from
    (
Select 
    Date,
    Total,
    Country
from t
    ) x
Pivot(
    sum(total)
    for Date in (
           
            [2022-11-01],
            [2022-11-02],
            [2022-11-03],
            [2022-11-04]

    )
) as pivottable

查询结果（测试数据） #

表结构及测试数据 #

MasterFile表 #

FileProcessLog表（注：原代码中表名为Log，此处应为笔误，实际为FileProcessLog） #

原代码核心问题有两个：CTE中的日期范围硬编码、PIVOT的日期列固定。要实现自动获取过去30天数据，需分两步处理：

1. 替换CTE中的硬编码日期范围 #

将原代码中a.ProcessDate BETWEEN '2022-11-01' AND '2022-11-07'替换为动态计算过去30天的条件，避免时间部分干扰：

a.ProcessDate >= DATEADD(DAY, -30, CAST(GETDATE() AS DATE)) 
AND a.ProcessDate < DATEADD(DAY, 1, CAST(GETDATE() AS DATE))

2. 动态生成PIVOT的日期列 #

过去30天的日期是动态变化的，无法硬编码列名，需用动态SQL生成PIVOT语句，完整实现如下：

DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX);

-- 生成过去30天的日期列名，格式为[YYYY-MM-DD]
SELECT @cols = STUFF((SELECT ',' + QUOTENAME(CAST(DateVal AS DATE)) 
                      FROM (
                          SELECT DATEADD(DAY, -n, CAST(GETDATE() AS DATE)) AS DateVal
                          FROM (SELECT TOP 30 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 AS n FROM sys.all_columns) AS nums
                      ) AS date_range
                      ORDER BY DateVal
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

-- 构建动态查询
SET @query = N'
WITH t (Country, Date, total)
AS
(
    SELECT b.Country AS Country, 
           CAST(a.ProcessDate AS DATE) AS Date, 
           COUNT(a.ProcessDate) AS total 
    FROM FileProcessLog a 
    LEFT JOIN MasterFile b ON a.FileID = b.FileID 
    WHERE a.ProcessDate >= DATEADD(DAY, -30, CAST(GETDATE() AS DATE)) 
      AND a.ProcessDate < DATEADD(DAY, 1, CAST(GETDATE() AS DATE))
    GROUP BY b.Country, CAST(a.ProcessDate AS DATE)
)
SELECT *
FROM (
    SELECT Date, Total, Country
    FROM t
) x
PIVOT (
    SUM(total)
    FOR Date IN (' + @cols + N')
) AS pivottable';

-- 执行动态查询
EXEC sp_executesql @query;

说明 #

• 先通过子查询生成过去30天的所有日期，用QUOTENAME处理成PIVOT需要的列名格式。
• 动态拼接完整SQL语句，替换原硬编码日期列。
• 使用sp_executesql执行动态SQL，保证安全性与性能。

内容的提问来源于stack exchange，提问作者AKHIL OMAR