如何在标准SQL中高效获取每个分组的最高评分记录？

可行解决方案（兼容PostgreSQL/MySQL/SQLite） #

采用标准SQL窗口函数ROW_NUMBER()实现分组取首行，该特性为SQL:2003标准定义，三类数据库的主流支持版本（MySQL 8.0+、PostgreSQL 9.4+、SQLite 3.25+）均原生支持，无数据库专属依赖。
完整查询语句如下：

WITH album_photo_mapping AS (
    -- 复用方案二的高效关联逻辑，全量关联所有相册与对应递归子级下的所有照片
    SELECT
        a.id AS album_id,
        p.id AS photo_id,
        p.mime_type AS cover_mime_type,
        p.width AS cover_width,
        p.height AS cover_height,
        p.is_starred,
        p.created_at
    FROM albums a
    LEFT JOIN photos p
    LEFT JOIN albums direct_parents ON direct_parents.id = p.album_id
        ON direct_parents._lft >= a._lft AND direct_parents._rgt <= a._rgt
),
ranked_photos AS (
    -- 按相册ID分组，给每个分组内的照片按评分规则排序
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY album_id
            ORDER BY is_starred DESC, created_at DESC
        ) AS row_num
    FROM album_photo_mapping
)
-- 仅取每个分组排序第一的记录，即为对应相册的封面
SELECT
    album_id,
    photo_id AS cover_id,
    cover_mime_type,
    cover_width,
    cover_height
FROM ranked_photos
WHERE row_num = 1;

性能优化建议 #

为进一步提升查询效率，建议提前创建以下索引：

• albums表添加(_lft, _rgt)联合索引，加速嵌套集范围查询
• photos表添加(album_id)索引，加速照片与所属相册的关联查询
• 数据量极大的场景下，可给photos表添加(is_starred DESC, created_at DESC)联合索引，优化排序性能

低版本兼容方案 #

如果需要适配不支持窗口函数的极低版本数据库，可改用标准SQL关联子查询写法，性能略低于窗口函数版本，但远高于原方案一：

SELECT
    a.id AS album_id,
    p.id AS cover_id,
    p.mime_type AS cover_mime_type,
    p.width AS cover_width,
    p.height AS cover_height
FROM albums a
LEFT JOIN photos p ON EXISTS (
    SELECT 1 FROM albums direct_parents
    WHERE direct_parents.id = p.album_id
      AND direct_parents._lft >= a._lft AND direct_parents._rgt <= a._rgt
) AND NOT EXISTS (
    -- 排除同分组内评分更高的照片，剩余记录即为最高评分封面
    SELECT 1 FROM photos p2
    LEFT JOIN albums direct_parents2 ON direct_parents2.id = p2.album_id
    WHERE direct_parents2._lft >= a._lft AND direct_parents2._rgt <= a._rgt
      AND (p2.is_starred > p.is_starred OR (p2.is_starred = p.is_starred AND p2.created_at > p.created_at))
);

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