如何筛选最新Revision且状态为published的SQL数据？技术问询

首先你原来的SQL写法存在问题：SELECT *, MAX(revision_id) FROM ... GROUP BY id DESC 在标准SQL中不合法，SELECT列表里的非聚合列（除id外）未包含在GROUP BY中，执行时这些列的值是不确定的，无法保证取到对应最新revision_id的完整行数据。

要实现**仅保留每个ID的最新版本且状态为'published'**的行，推荐两种可靠写法：

方法一：子查询关联 #

先通过子查询找出每个ID对应的最新revision_id，再关联原表获取完整行数据，最后筛选状态：

SELECT t.*
FROM content_moderation_state_field_revision t
INNER JOIN (
    SELECT id, MAX(revision_id) AS latest_rev
    FROM content_moderation_state_field_revision
    GROUP BY id
) latest ON t.id = latest.id AND t.revision_id = latest.latest_rev
WHERE t.moderation_state = 'published';

方法二：窗口函数排序 #

使用ROW_NUMBER()窗口函数给每个ID的revision按降序排名，取排名第1的最新行，再筛选状态：

SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY id ORDER BY revision_id DESC) AS rn
    FROM content_moderation_state_field_revision
) ranked
WHERE rn = 1 AND moderation_state = 'published';

为什么直接加WHERE条件不行？ #

如果直接在原SQL的WHERE子句加moderation_state = 'published'，会先过滤掉所有状态不是published的行，再对剩下的行取每个ID的最大revision_id。此时若某个ID的最新revision状态并非published，但存在旧的published版本，就会错误地把这个旧版本的revision_id当作该ID的“最新”值，完全不符合需求。

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