含聚合函数与NOT IN的HAVING子句未按预期生效的SQL问题

需求说明 #

• 核心规则：排除连续三年Vigor值为6、8或9的树木
• 强制规则：必须保留TreeID为2、3的所有行，排除TreeID为1的所有行

样本数据 #

PlotID    ObsYear TreeID Vigor
MACFI0407 2020    1      8
MACFI0407 2021    1      8
MACFI0407 2022    1      8
MACFI0407 2020    2      1
MACFI0407 2021    2      1
MACFI0407 2022    2      8
MACFI0407 2020    3      1
MACFI0407 2021    3      1
MACFI0407 2022    3      1

过往尝试的问题分析 #

SQL1 #

SELECT PlotID, TreeID, Vigor, count(*) c
FROM tblTreeInfo
GROUP BY PlotID, TreeID, Vigor
HAVING (c < 3 AND Vigor NOT IN (6, 8, 9))

• 问题：分组维度错误，按PlotID, TreeID, Vigor分组后，TreeID3的Vigor=1出现3次，不满足c<3的条件，导致该行被错误排除，违反了保留TreeID3所有行的要求。

SQL2 #

SELECT PlotID, TreeID, Vigor, count(*) c
FROM tblTreeInfo
WHERE Vigor NOT IN (6, 8, 9)
GROUP BY PlotID, TreeID, Vigor
HAVING (c < 3)

• 问题：WHERE子句直接过滤了Vigor为6、8、9的记录，导致TreeID2的2022年Vigor=8的行被排除，不符合保留TreeID2所有行的要求。

正确SQL实现 #

基础版本（假设ObsYear为连续三年） #

SELECT t.PlotID, t.ObsYear, t.TreeID, t.Vigor
FROM tblTreeInfo t
LEFT JOIN (
    -- 筛选出连续三年Vigor为6/8/9的树木ID
    SELECT TreeID
    FROM tblTreeInfo
    WHERE Vigor IN (6, 8, 9)
    GROUP BY TreeID
    HAVING COUNT(DISTINCT ObsYear) = 3
) bad_trees ON t.TreeID = bad_trees.TreeID
WHERE 
    -- 排除连续三年符合条件的树木
    bad_trees.TreeID IS NULL
    -- 强制保留TreeID为2、3的所有行
    OR t.TreeID IN (2, 3)
    -- 强制排除TreeID为1的所有行
    AND t.TreeID != 1;

严格连续年份版本（处理非连续三年的情况） #

如果需要严格判断年份是连续的（比如避免树木在2020、2022、2023年出现目标Vigor但中间断档的情况），可以用窗口函数实现：

SELECT t.PlotID, t.ObsYear, t.TreeID, t.Vigor
FROM tblTreeInfo t
LEFT JOIN (
    SELECT DISTINCT TreeID
    FROM (
        SELECT 
            TreeID,
            ObsYear,
            Vigor,
            -- 计算当前年份与前一条记录的年份差
            ObsYear - LAG(ObsYear) OVER (PARTITION BY TreeID ORDER BY ObsYear) AS year_gap,
            -- 标记当前行是否为目标Vigor值
            CASE WHEN Vigor IN (6, 8, 9) THEN 1 ELSE 0 END AS is_target
        FROM tblTreeInfo
    ) sub
    WHERE is_target = 1
    -- 按连续年份分组（用年份减去行号生成连续组的标识）
    GROUP BY TreeID, ObsYear - ROW_NUMBER() OVER (PARTITION BY TreeID ORDER BY ObsYear)
    -- 筛选出连续三年都是目标Vigor的组
    HAVING COUNT(*) = 3
) bad_trees ON t.TreeID = bad_trees.TreeID
WHERE 
    bad_trees.TreeID IS NULL
    OR t.TreeID IN (2, 3)
    AND t.TreeID != 1;

逻辑说明 #

1. 子查询bad_trees：先找出所有符合“连续三年Vigor为6/8/9”的树木ID。
2. 主查询通过左连接排除这些树木，同时强制保留TreeID为2、3的所有行，排除TreeID为1的行，完全匹配需求。

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