SQL GROUP BY筛选最新记录及用户翻译覆盖的查询优化咨询

原查询存在的问题 #

你的查询有两个明显问题：

1. 若某个word_translation_id对应多条user_id非空的记录（题目仅限制单用户对同一word_translation最多一条，但允许多用户提交），左连接后会生成重复的word_translation_id结果，违反了“每个word_translation_id仅对应一个translation id”的要求。
2. 原查询需要两次扫描translations表（一次分组取默认翻译的最大ID，一次提取用户翻译），数据量较大时会增加IO开销，性能表现不佳。

更高效的实现方式 #

可以利用窗口函数ROW_NUMBER()实现，只需扫描一次translations表，逻辑简洁且性能更优：

SELECT id, word_translation_id
FROM (
    SELECT 
        id, 
        word_translation_id,
        ROW_NUMBER() OVER (
            PARTITION BY word_translation_id 
            ORDER BY CASE WHEN user_id IS NOT NULL THEN 0 ELSE 1 END, id DESC
        ) AS rn
    FROM translations
) t
WHERE rn = 1;

逻辑说明 #

• PARTITION BY word_translation_id按翻译关联ID分组
• ORDER BY子句优先将user_id非空的记录排在前面（标记为0），同一优先级下按id降序选取最新记录
• 筛选每组中排序第一的记录（rn=1），正好满足每个word_translation_id仅返回一条最优翻译ID的要求

性能优化补充 #

为进一步提升查询效率，建议给translations表创建复合索引：

CREATE INDEX idx_translations_wt_user_id ON translations(word_translation_id, user_id, id DESC);

该索引可让数据库直接按分组和排序条件快速定位目标记录，避免全表扫描。

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