如何筛选透视查询，仅返回指定日期范围有至少一条记录的城镇

方案1：透视前预筛选有事件的城镇 #

先通过子查询锁定指定日期范围内至少发生1次事件的城镇，再和透视结果做关联，直接排除无事件的城镇。这种方式能减少透视处理的数据量，效率更高。

示例代码：

WITH 有事件城镇 AS (
    SELECT DISTINCT 城镇名称
    FROM 事件表
    WHERE 事件日期 BETWEEN @f1 AND @f2
      AND 事件数 > 0 -- 确保该城镇存在有效事件记录
)
SELECT p.城镇名称, p.[2024-01-01], p.[2024-01-02], -- 替换为你的实际日期列
       p.[2024-01-03], ...
FROM (
    SELECT 城镇名称, 事件日期, 事件数
    FROM 事件表
    WHERE 事件日期 BETWEEN @f1 AND @f2
) AS 源数据
PIVOT (
    SUM(事件数)
    FOR 事件日期 IN ([2024-01-01], [2024-01-02], [2024-01-03], ...) -- 对应日期列
) AS p
INNER JOIN 有事件城镇 t ON p.城镇名称 = t.城镇名称

方案2：透视后通过日期列总和过滤 #

如果透视后的日期列存在NULL（代表当天无事件），先用COALESCE把NULL转为0，再对所有日期列求和，筛选总和大于0的记录，直接排除全0的城镇。

示例代码：

SELECT 城镇名称, [2024-01-01], [2024-01-02], [2024-01-03], ...
FROM (
    SELECT 城镇名称, 事件日期, 事件数
    FROM 事件表
    WHERE 事件日期 BETWEEN @f1 AND @f2
) AS 源数据
PIVOT (
    SUM(事件数)
    FOR 事件日期 IN ([2024-01-01], [2024-01-02], [2024-01-03], ...)
) AS 透视结果
WHERE COALESCE([2024-01-01], 0) + COALESCE([2024-01-02], 0) + 
      COALESCE([2024-01-03], 0) + ... > 0

动态日期列的处理（日期自动生成场景） #

如果你的日期列是通过动态SQL生成的，需要在动态语句中拼接求和条件：

DECLARE @日期列 NVARCHAR(MAX)
DECLARE @求和条件 NVARCHAR(MAX)
DECLARE @动态SQL NVARCHAR(MAX)

-- 生成带引号的日期列字符串
SELECT @日期列 = STRING_AGG(QUOTENAME(CONVERT(VARCHAR(10), 事件日期, 23)), ',')
FROM (
    SELECT DISTINCT 事件日期
    FROM 事件表
    WHERE 事件日期 BETWEEN @f1 AND @f2
) AS 日期列表

-- 生成求和过滤条件
SELECT @求和条件 = STRING_AGG('COALESCE(' + QUOTENAME(CONVERT(VARCHAR(10), 事件日期, 23)) + ', 0)', ' + ') + ' > 0'
FROM (
    SELECT DISTINCT 事件日期
    FROM 事件表
    WHERE 事件日期 BETWEEN @f1 AND @f2
) AS 日期列表

-- 拼接并执行动态SQL
SET @动态SQL = '
SELECT 城镇名称, ' + @日期列 + '
FROM (
    SELECT 城镇名称, 事件日期, 事件数
    FROM 事件表
    WHERE 事件日期 BETWEEN @f1 AND @f2
) AS 源数据
PIVOT (
    SUM(事件数)
    FOR 事件日期 IN (' + @日期列 + ')
) AS 透视结果
WHERE ' + @求和条件

EXEC sp_executesql @动态SQL, N'@f1 DATE, @f2 DATE', @f1 = @f1, @f2 = @f2

问题说明 #

之前添加WHERE子句报错，大概率是因为透视后的日期列存在NULL值，直接和数值比较时触发类型不兼容。用COALESCE把NULL转为0后，就能正常计算求和并过滤。预筛选的方式更适合大数据量场景，能提前过滤无效数据，提升查询效率。

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