带日期筛选的SQL Union统计：如何保留单行列并正确计数？

嘿，我来帮你搞定这个问题！你遇到的核心问题是把日期筛选放在了视图的外部查询里，同时在视图的GROUP BY中加入了date_created——这就导致每个不同的日期值都会生成独立的分组，同一个code_id自然被拆成了多行。而且原视图的设计是要覆盖所有code_id和asset_standard_id的组合（包括0次出现的），外部筛选日期会直接过滤掉那些没有符合日期记录的组合，还会拆分有记录的组合。

下面给你两个可行的解决方案，按需选择：

方案1：修改视图，把日期筛选整合到连接逻辑中（推荐） #

我们把日期筛选条件移到LEFT JOIN的连接条件里，这样统计的就是指定日期范围内的次数，同时每个code_id + asset_standard_id组合只会显示一行，0次的记录也能保留。

修正后的ALTER VIEW语句：

ALTER VIEW cause_codes AS
SELECT 
    possibilities.code_id AS code_id,
    possibilities.asset_standard_id,
    COUNT(U.pkey) AS [COUNT]
FROM (
    SELECT 
        a.asset_standard_id,
        b.code_id
    FROM (SELECT DISTINCT asset_standard_id FROM @imvw_woap_code_with_cust) AS a
    CROSS JOIN (
        SELECT DISTINCT code_id FROM @IMTBL_CODE
        UNION
        SELECT DISTINCT CODE_ID AS [ID] FROM @imvw_woap_code_with_cust
    ) AS b
) AS possibilities
LEFT OUTER JOIN @imvw_woap_code_with_cust AS U 
    ON U.code_id = possibilities.code_id 
    AND possibilities.asset_standard_id = u.asset_standard_id 
    AND u.code_type_id = 'A-Problem'
    -- 将日期筛选放到连接条件中，只统计符合范围的记录
    AND U.date_completed > '2017-09-10 02:00:30.013' 
    AND U.date_completed < '2017-09-12 02:00:30.013'
GROUP BY possibilities.code_id, possibilities.asset_standard_id
ORDER BY [COUNT]

之后查询视图时，只需要筛选目标资产即可：

SELECT * FROM cause_codes WHERE asset_standard_id = '2 west'

方案2：保留视图灵活性，用外部聚合实现动态日期筛选 #

如果需要视图支持不同的日期范围查询，不想在视图里硬编码日期，可以先恢复原视图（去掉date_created相关的内容），然后在外部查询时先过滤数据再聚合：

首先恢复原视图：

ALTER VIEW cause_codes AS
SELECT 
    possibilities.code_id AS code_id,
    possibilities.asset_standard_id,
    COUNT(U.pkey) AS [COUNT]
FROM (
    SELECT 
        a.asset_standard_id,
        b.code_id
    FROM (SELECT DISTINCT asset_standard_id FROM @imvw_woap_code_with_cust) AS a
    CROSS JOIN (
        SELECT DISTINCT code_id FROM @IMTBL_CODE
        UNION
        SELECT DISTINCT CODE_ID AS [ID] FROM @imvw_woap_code_with_cust
    ) AS b
) AS possibilities
LEFT OUTER JOIN @imvw_woap_code_with_cust AS U 
    ON U.code_id = possibilities.code_id 
    AND possibilities.asset_standard_id = u.asset_standard_id 
    AND u.code_type_id = 'A-Problem'
GROUP BY possibilities.code_id, possibilities.asset_standard_id
ORDER BY [COUNT]

然后用CTE先筛选出符合日期范围的记录，再和视图的全组合关联统计：

WITH filtered_woap AS (
    SELECT * FROM @imvw_woap_code_with_cust
    WHERE date_completed > '2017-09-10 02:00:30.013' 
      AND date_completed < '2017-09-12 02:00:30.013'
      AND code_type_id = 'A-Problem'
)
SELECT 
    p.code_id,
    p.asset_standard_id,
    COUNT(f.pkey) AS [COUNT]
FROM cause_codes p
LEFT JOIN filtered_woap f 
    ON p.code_id = f.code_id 
    AND p.asset_standard_id = f.asset_standard_id
WHERE p.asset_standard_id = '2 west'
GROUP BY p.code_id, p.asset_standard_id
ORDER BY [COUNT]

为什么这两个方案有效？ #

• 方案1通过把日期筛选放到连接条件中，确保左连接只统计符合日期的记录，同时保留了所有code_id + asset_standard_id的组合，0次出现的记录也能正常显示。
• 方案2保留了原视图的通用性，通过CTE提前过滤数据，再和视图的全组合关联，既可以灵活指定不同的日期范围，又能保证每个code_id只显示一行，计数准确。

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