查询t1中符合特定条件的ID及SQL子查询多行结果报错解决请求

这个报错的核心原因是：你在WHERE子句里的标量子查询（用来获取t2对应id最新run_date的status）返回了多行结果。虽然你通过most_recent_id拿到了每个id的最新run_date，但如果t2中存在同一id+同一最新run_date的多条不同status记录，这个子查询就会返回多行，而标量子查询要求只能返回0或1行，所以数据库抛出了错误。

我们可以换一种方式，先通过窗口函数获取t2中每个id的最新完整记录（包含status），再和t1关联，这样就能避免子查询的问题。以下是修正后的SQL：

WITH t2_latest AS (
    -- 获取t2中每个id的最新run_date对应的记录
    SELECT id, status, run_date
    FROM (
        SELECT 
            id,
            status,
            run_date,
            -- 按id分组，按run_date倒序排序，取第一条（最新的）
            ROW_NUMBER() OVER (PARTITION BY id ORDER BY run_date DESC) AS rn
        FROM t2
    ) sub_query
    WHERE rn = 1
)
SELECT t1.id
FROM t1
LEFT JOIN t2_latest ON t1.id = t2_latest.id
WHERE 
    -- 条件1：该id在t2中不存在
    t2_latest.id IS NULL
    OR 
    -- 条件2：id存在于t2，且t2最新run_date早于t1的run_date，同时状态不同
    (t2_latest.run_date < t1.run_date AND t1.status <> t2_latest.status);

为什么这样能解决问题？ #

• t2_latest CTE确保了每个id只有一条记录——就是该id在t2中run_date最新的那一条，不管t2里同一id有多少条历史记录，我们只保留最新的那条状态和日期。
• 通过LEFT JOIN关联t1和t2_latest，可以直接用字段比较来替代原来的子查询，彻底避免了标量子查询返回多行的问题。
• 逻辑上完全匹配你提出的两个条件：要么t2里没有这个id，要么存在但最新记录的日期更早且状态不同。

如果你担心ROW_NUMBER()的性能，也可以用MAX(run_date)关联t2来获取最新状态，写法如下（同时处理同一最新日期下有多条状态的情况）：

WITH t2_latest_date AS (
    SELECT id, MAX(run_date) AS latest_run_date
    FROM t2
    GROUP BY id
),
t2_latest_status AS (
    SELECT t2.id, t2.status, t2_latest_date.latest_run_date
    FROM t2
    JOIN t2_latest_date ON t2.id = t2_latest_date.id AND t2.run_date = t2_latest_date.latest_run_date
    -- 确保同一id只返回一条状态记录，避免多条重复干扰
    GROUP BY t2.id, t2.status, t2_latest_date.latest_run_date
)
SELECT t1.id
FROM t1
LEFT JOIN t2_latest_status ON t1.id = t2_latest_status.id
WHERE 
    t2_latest_status.id IS NULL
    OR 
    (t2_latest_status.latest_run_date < t1.run_date AND t1.status <> t2_latest_status.status);

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