PostgreSQL如何关联查询另一张表的首个匹配结果？

需求说明 #

从Posts表中获取所有帖子，同时关联Images表中对应帖子按display_order升序排序后的第一条图片（当display_order相同时，取id最小的图片记录），最终输出包含帖子ID、标题和对应缩略图路径的结果。

不同数据库的实现方案 #

MySQL #

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

利用ROW_NUMBER()窗口函数分组排序，筛选每组第一条记录：

SELECT 
    p.id,
    p.title,
    i.image_path AS thumb_path
FROM Posts p
JOIN (
    SELECT 
        post_id,
        image_path,
        ROW_NUMBER() OVER (PARTITION BY post_id ORDER BY display_order ASC, id ASC) AS rn
    FROM Images
) i ON p.id = i.post_id
WHERE i.rn = 1;

方案2：子查询（兼容低版本MySQL）

通过子查询定位每个帖子的目标图片，再关联查询：

SELECT 
    p.id,
    p.title,
    (SELECT image_path 
     FROM Images 
     WHERE post_id = p.id 
     ORDER BY display_order ASC, id ASC 
     LIMIT 1) AS thumb_path
FROM Posts p;

PostgreSQL #

方案1：窗口函数

和MySQL逻辑一致，语法通用：

SELECT 
    p.id,
    p.title,
    i.image_path AS thumb_path
FROM Posts p
JOIN (
    SELECT 
        post_id,
        image_path,
        ROW_NUMBER() OVER (PARTITION BY post_id ORDER BY display_order ASC, id ASC) AS rn
    FROM Images
) i ON p.id = i.post_id
WHERE i.rn = 1;

方案2：DISTINCT ON（PostgreSQL 特有语法）

利用PostgreSQL的DISTINCT ON特性简化查询：

SELECT DISTINCT ON (p.id)
    p.id,
    p.title,
    i.image_path AS thumb_path
FROM Posts p
JOIN Images i ON p.id = i.post_id
ORDER BY p.id, i.display_order ASC, i.id ASC;

SQL Server #

使用窗口函数筛选每组第一条记录：

SELECT 
    id,
    title,
    thumb_path
FROM (
    SELECT 
        p.id,
        p.title,
        i.image_path AS thumb_path,
        ROW_NUMBER() OVER (PARTITION BY p.id ORDER BY i.display_order ASC, i.id ASC) AS rn
    FROM Posts p
    JOIN Images i ON p.id = i.post_id
) t
WHERE rn = 1;

逻辑说明 #

所有方案的核心逻辑一致：

1. 按帖子ID（post_id）分组，每组内先按display_order升序排序（确保display_order最小的图片优先）；
2. 当display_order相同时，按图片ID（id）升序排序，保证取到最早创建的图片；
3. 筛选每组的第一条记录，关联到帖子表得到最终结果。

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