PostgreSQL：通过单条查询获取关联表的第一条与最后一条记录

嘿，针对你的表结构和需求，我们可以利用PostgreSQL的窗口函数轻松实现单条查询获取每个用户对应的第一条和最后一条survey_results记录。下面提供两种常见的实现方式，你可以根据实际需求选择：

方式一：将首尾记录作为独立行返回 #

这种方式会把每个用户的第一条和最后一条survey记录分别作为单独的行输出，同时标记记录类型：

WITH ranked_surveys AS (
    SELECT
        u.id AS user_id,
        sr.id AS survey_id,
        sr.name,
        sr.created_at,
        -- 按创建时间升序给每个用户的survey记录编号
        ROW_NUMBER() OVER (PARTITION BY u.id ORDER BY sr.created_at ASC) AS rn_asc,
        -- 按创建时间降序给每个用户的survey记录编号
        ROW_NUMBER() OVER (PARTITION BY u.id ORDER BY sr.created_at DESC) AS rn_desc
    FROM users u
    JOIN survey_results sr ON u.id = sr.user_id
)
SELECT
    user_id,
    survey_id,
    name,
    created_at,
    CASE 
        WHEN rn_asc = 1 THEN '第一条记录' 
        WHEN rn_desc = 1 THEN '最后一条记录' 
    END AS record_type
FROM ranked_surveys
WHERE rn_asc = 1 OR rn_desc = 1;

说明： #

• PARTITION BY u.id表示按用户分组，确保我们只在当前用户的survey记录里排序编号
• ROW_NUMBER()会给每条记录分配唯一的序号，如果多条记录的created_at相同，序号会随机分配；如果需要把时间相同的记录都算作首尾，可以替换成RANK()
• 最后通过WHERE筛选出序号为1的记录，也就是每个用户的最早和最晚survey记录

方式二：将首尾记录字段合并到同一行 #

如果你希望每个用户的首尾记录信息都在同一行展示，可以用这种写法：

SELECT DISTINCT ON(u.id)
    u.id AS user_id,
    -- 获取最早的survey名称和创建时间
    FIRST_VALUE(sr.name) OVER (PARTITION BY u.id ORDER BY sr.created_at ASC) AS first_survey_name,
    FIRST_VALUE(sr.created_at) OVER (PARTITION BY u.id ORDER BY sr.created_at ASC) AS first_survey_created,
    -- 获取最晚的survey名称和创建时间，注意要指定窗口范围
    LAST_VALUE(sr.name) OVER (
        PARTITION BY u.id 
        ORDER BY sr.created_at ASC 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_survey_name,
    LAST_VALUE(sr.created_at) OVER (
        PARTITION BY u.id 
        ORDER BY sr.created_at ASC 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_survey_created
FROM users u
JOIN survey_results sr ON u.id = sr.user_id;

说明： #

• FIRST_VALUE()和LAST_VALUE()用于提取分组内的首尾字段值
• 必须指定ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING，否则LAST_VALUE()默认只会取到当前行及之前的记录，无法获取真正的最后一条
• DISTINCT ON(u.id)确保每个用户只返回一行结果

测试数据注意点： #

你插入的三条survey_results的created_at都是now()，所以在测试时首尾记录可能是随机的。实际场景中只要created_at有时间差，就能正确区分最早和最晚的记录。

内容的提问来源于stack exchange，提问作者Mateusz Urbański