MySQL优化查询：简洁筛选连续5年活力等级为6/8/9的树木

方案1：窗口函数实现（适配MySQL 8.0+、PostgreSQL等支持窗口函数的数据库） #

通过标记年份有效性，再计算连续5年的有效年份数来筛选目标树木：

SET @sampleyear = 2020;
WITH tree_vigor_check AS (
    SELECT
        tree_id,
        Year,
        Vigor,
        -- 标记当前年份Vigor是否符合要求
        CASE WHEN Vigor IN (6,8,9) THEN 1 ELSE 0 END AS is_valid,
        -- 按树木分组、年份排序，计算当前年份及前4年的连续有效年份总数
        SUM(CASE WHEN Vigor IN (6,8,9) THEN 1 ELSE 0 END) OVER (
            PARTITION BY tree_id 
            ORDER BY Year 
            ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
        ) AS consecutive_valid_years
    FROM tblTree
    WHERE Year BETWEEN @sampleyear - 4 AND @sampleyear
)
SELECT *
FROM tree_vigor_check
WHERE Year = @sampleyear
  AND consecutive_valid_years = 5;

方案2：分组统计+自连接（兼容低版本数据库） #

如果你的数据库不支持窗口函数，可通过分组统计5年内的有效记录数来筛选：

SET @sampleyear = 2020;
SELECT t.*
FROM tblTree t
INNER JOIN (
    SELECT tree_id
    FROM tblTree
    WHERE Year BETWEEN @sampleyear - 4 AND @sampleyear
    GROUP BY tree_id
    -- 统计5年内符合要求的记录数等于5，说明每年都达标
    HAVING COUNT(CASE WHEN Vigor IN (6,8,9) THEN 1 END) = 5
) valid_tree_ids ON t.tree_id = valid_tree_ids.tree_id
WHERE t.Year = @sampleyear;

方案说明 #

• 方案1用窗口函数可以直观追踪连续年份的有效性，逻辑清晰，适合新版本数据库；
• 方案2兼容性更强，无需窗口函数支持，通过分组统计确保5年全部达标；
• 两种方案都会返回2020年连续5年符合Vigor要求的树木记录，符合你预期的筛选结果。

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