如何过滤GROUP BY结果？按日均多TRANS筛选的HAVING方案失效

你的需求是保留查询结果中同一日期下存在多个TRANS分组的行（即原结果的第3、4、6、7行），之前添加的HAVING COUNT(trim(CA.TRANS)) > 1之所以无效，是因为它统计的是每个分组内的原始记录数量，而非当天有多少个不同的TRANS分组。

以下是两种可行的解决方案：

方法一：使用窗口函数（推荐） #

通过窗口函数COUNT(*) OVER (PARTITION BY DATE)直接计算每个日期对应的分组总数，再筛选总数大于1的行：

WITH grouped_data AS (
    SELECT 
        to_char(CA.EFFECTIVE_DATE, 'mm/dd/yyyy') as DATE,
        trim(CA.TRANS) as TRANS,
        CA.DESCR,   
        sum(CA.AMOUNT) AS AMOUNT,
        -- 计算当前日期下的分组总数
        COUNT(*) OVER (PARTITION BY to_char(CA.EFFECTIVE_DATE, 'mm/dd/yyyy')) AS daily_group_count
    FROM 
        CASHDBO.TRANSACTIONS CA
        INNER JOIN SECURITYDBO.SECURITY SM  ON (SM.SECURITY_ID = CA.SECURITY_ID) 
    WHERE
        CA.TRANS_TYPE in ( 'REINVEST','BUY','RGHTEXER','SELL')
    GROUP BY
        to_char(CA.EFFECTIVE_DATE, 'mm/dd/yyyy'),
        trim(CA.TRANS),
        CA.DESCR
)
SELECT DATE, TRANS, DESCR, AMOUNT
FROM grouped_data
WHERE daily_group_count > 1;

方法二：子查询关联 #

先筛选出所有存在多个TRANS分组的日期，再关联原查询结果，只保留这些日期的行：

SELECT g.DATE, g.TRANS, g.DESCR, g.AMOUNT
FROM (
    -- 原分组查询
    SELECT 
        to_char(CA.EFFECTIVE_DATE, 'mm/dd/yyyy') as DATE,
        trim(CA.TRANS) as TRANS,
        CA.DESCR,   
        sum(CA.AMOUNT) AS AMOUNT
    FROM 
        CASHDBO.TRANSACTIONS CA
        INNER JOIN SECURITYDBO.SECURITY SM  ON (SM.SECURITY_ID = CA.SECURITY_ID) 
    WHERE
        CA.TRANS_TYPE in ( 'REINVEST','BUY','RGHTEXER','SELL')
    GROUP BY
        to_char(CA.EFFECTIVE_DATE, 'mm/dd/yyyy'),
        trim(CA.TRANS),
        CA.DESCR
) g
-- 关联筛选出符合条件的日期
INNER JOIN (
    SELECT 
        to_char(CA.EFFECTIVE_DATE, 'mm/dd/yyyy') as DATE
    FROM 
        CASHDBO.TRANSACTIONS CA
        INNER JOIN SECURITYDBO.SECURITY SM  ON (SM.SECURITY_ID = CA.SECURITY_ID) 
    WHERE
        CA.TRANS_TYPE in ( 'REINVEST','BUY','RGHTEXER','SELL')
    GROUP BY
        to_char(CA.EFFECTIVE_DATE, 'mm/dd/yyyy')
    -- 筛选当天TRANS分组数大于1的日期
    HAVING COUNT(DISTINCT trim(CA.TRANS)) > 1
) d ON g.DATE = d.DATE;

说明 #

• 方法一利用窗口函数无需重复编写查询逻辑，代码更简洁高效；
• 方法二通过子查询先锁定目标日期，适合对窗口函数不熟悉的场景。

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