PostgreSQL如何正确标记两年earnsaveloss均≤0的ID？

云上老周

2026-7-8

修正PostgreSQL中ID标记逻辑的问题

问题根源

你当前的SQL代码中，子查询没有关联当前行的id，导致判断的是整个表是否存在2021年和2022年earnsaveloss=0的记录，而非每个ID自身两年都满足earnsaveloss≤0的条件。只要表中两年都有符合条件的数据，所有满足单年条件的行都会被错误标记。

解决方案

下面提供两种高效的修正方案：

方案1：使用窗口函数（推荐）

通过窗口函数统计每个ID在2021、2022年中满足earnsaveloss≤0的年份数，再判断是否为2：

SELECT
    id,
    year,
    earnsaveloss,
    CASE
        WHEN COUNT(CASE WHEN year IN (2021,2022) AND earnsaveloss <=0 THEN 1 END) OVER (PARTITION BY id) = 2
        THEN 'Lost money both years'
        ELSE ''
    END AS NoMoneyBothYears
FROM mssp_2021_2022_aco
WHERE year IN (2021,2022) -- 仅筛选目标年份数据
ORDER BY id, year;

PARTITION BY id：按ID分组统计
内层CASE仅对2021/2022年且earnsaveloss≤0的行计数
外层CASE判断计数是否为2（即两年都满足条件），满足则标记

方案2：先筛选符合条件的ID再关联

先通过分组查询找出两年都满足条件的ID，再与原表关联标记：

WITH qualified_ids AS (
    SELECT id
    FROM mssp_2021_2022_aco
    WHERE year IN (2021,2022) AND earnsaveloss <=0
    GROUP BY id
    HAVING COUNT(DISTINCT year) = 2 -- 确保两年都有符合条件的数据
)
SELECT
    t.id,
    t.year,
    t.earnsaveloss,
    CASE WHEN q.id IS NOT NULL THEN 'Lost money both years' ELSE '' END AS NoMoneyBothYears
FROM mssp_2021_2022_aco t
LEFT JOIN qualified_ids q ON t.id = q.id
WHERE t.year IN (2021,2022)
ORDER BY t.id, t.year;