如何无需两次关联，获取人员父子数据集的首尾操作记录

嘿，我来给你分享几个不用两次关联子表就能搞定这个需求的方案，比之前两次关联的写法效率更高哦！先假设咱们的表结构是这样的：

• 父表person：存储人员基础信息，字段比如id（主键）、name、age
• 子表operation：存储操作记录，字段比如id、parentId（关联person.id）、operate_time（操作时间）、content（操作内容）

方案1：条件聚合 + 窗口函数（兼容性强） #

这个方法只需要关联一次子表，通过子查询给每条操作记录标记序号和总条数，再在外层用条件聚合提取首次、末次记录：

SELECT
    p.id,
    p.name,
    p.age,
    -- 提取首次操作的时间和内容
    MAX(CASE WHEN o.rn = 1 THEN o.operate_time END) AS first_operate_time,
    MAX(CASE WHEN o.rn = 1 THEN o.content END) AS first_operate_content,
    -- 提取末次操作的时间和内容
    MAX(CASE WHEN o.rn = o.total_rn THEN o.operate_time END) AS last_operate_time,
    MAX(CASE WHEN o.rn = o.total_rn THEN o.content END) AS last_operate_content
FROM
    person p
LEFT JOIN (
    SELECT
        parentId,
        operate_time,
        content,
        -- 按操作时间正序给每个人员的记录编号，第一条是1
        ROW_NUMBER() OVER (PARTITION BY parentId ORDER BY operate_time ASC) AS rn,
        -- 统计每个人员的总操作记录数，最后一条的编号就是这个数
        COUNT(*) OVER (PARTITION BY parentId) AS total_rn
    FROM operation
) o ON p.id = o.parentId
GROUP BY
    p.id, p.name, p.age;

为啥好用？ #

• 只关联了一次子表，减少了数据库的关联开销
• 用MAX聚合是因为每个人员对应的rn=1和rn=total_rn各只有一条记录，聚合后不会丢失数据
• 兼容绝大多数关系型数据库（MySQL 8.0+、PostgreSQL、SQL Server等）

方案2：FIRST_VALUE/LAST_VALUE 窗口函数（写法简洁） #

如果你的数据库支持窗口函数，也可以用FIRST_VALUE和LAST_VALUE直接提取首尾记录，再通过DISTINCT去重：

SELECT DISTINCT
    p.id,
    p.name,
    p.age,
    -- 取最早的操作时间和内容
    FIRST_VALUE(o.operate_time) OVER (PARTITION BY p.id ORDER BY o.operate_time ASC) AS first_operate_time,
    FIRST_VALUE(o.content) OVER (PARTITION BY p.id ORDER BY o.operate_time ASC) AS first_operate_content,
    -- 注意这里要指定窗口范围，不然LAST_VALUE默认只会取到当前行的值
    LAST_VALUE(o.operate_time) OVER (
        PARTITION BY p.id 
        ORDER BY o.operate_time ASC 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_operate_time,
    LAST_VALUE(o.content) OVER (
        PARTITION BY p.id 
        ORDER BY o.operate_time ASC 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_operate_content
FROM
    person p
LEFT JOIN operation o ON p.id = o.parentId;

注意点： #

• 一定要给LAST_VALUE指定窗口范围ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING，不然默认的窗口范围是从当前行往前到第一条，拿不到最后一条记录
• 用DISTINCT是因为每个人员的每条操作记录都会带上相同的首尾值，去重后就得到单条人员记录
• 同样只关联了一次子表，写法更简洁

这两个方案都能避免两次关联子表的问题，你可以根据自己使用的数据库和实际数据量选择合适的写法~

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