如何用SQL将多行合并为单行并筛选多记录账户的初始/终值信息

需求梳理 #

需要从Account details表中筛选出对应记录数≥2的act_id，并将每个符合条件的act_id的多行数据合并为单行，展示：

• 初始金额（最早创建记录的amount）、初始创建日期、初始创建人
• 最终金额（最晚创建记录的amount）、最终创建日期、最终创建人

解决方案1：使用窗口函数（推荐，效率更高） #

通过窗口函数对每个act_id的记录按创建日期排序并排名，直接关联最早和最晚的记录：

WITH act_valid AS (
    -- 先筛选出记录数≥2的act_id
    SELECT act_id
    FROM "Account details"
    GROUP BY act_id
    HAVING COUNT(*) >= 2
),
act_ranked AS (
    -- 给每个act_id的记录按创建日期升序、降序分别排名
    SELECT 
        act_id,
        amount,
        created_dt_ AS created_dt,
        created_by,
        ROW_NUMBER() OVER (PARTITION BY act_id ORDER BY created_dt_) AS rank_asc,
        ROW_NUMBER() OVER (PARTITION BY act_id ORDER BY created_dt_ DESC) AS rank_desc
    FROM "Account details"
    WHERE act_id IN (SELECT act_id FROM act_valid)
)
-- 关联升序排名第1（初始）和降序排名第1（最终）的记录
SELECT 
    ar_asc.act_id,
    ar_asc.amount AS ini_amt,
    ar_asc.created_dt AS ini_dt,
    ar_asc.created_by AS ini_usr,
    ar_desc.amount AS fnl_amt,
    ar_desc.created_dt AS fnl_dt,
    ar_desc.created_by AS fnl_usr
FROM act_ranked ar_asc
JOIN act_ranked ar_desc 
    ON ar_asc.act_id = ar_desc.act_id
    AND ar_asc.rank_asc = 1
    AND ar_desc.rank_desc = 1;

解决方案2：使用子查询（逻辑更直观） #

先筛选符合条件的act_id，再通过子查询分别获取每个act_id的最早、最晚记录字段：

SELECT 
    base.act_id,
    -- 获取初始信息（最早创建的记录）
    (SELECT amount FROM "Account details" WHERE act_id = base.act_id ORDER BY created_dt_ LIMIT 1) AS ini_amt,
    (SELECT created_dt_ FROM "Account details" WHERE act_id = base.act_id ORDER BY created_dt_ LIMIT 1) AS ini_dt,
    (SELECT created_by FROM "Account details" WHERE act_id = base.act_id ORDER BY created_dt_ LIMIT 1) AS ini_usr,
    -- 获取最终信息（最晚创建的记录）
    (SELECT amount FROM "Account details" WHERE act_id = base.act_id ORDER BY created_dt_ DESC LIMIT 1) AS fnl_amt,
    (SELECT created_dt_ FROM "Account details" WHERE act_id = base.act_id ORDER BY created_dt_ DESC LIMIT 1) AS fnl_dt,
    (SELECT created_by FROM "Account details" WHERE act_id = base.act_id ORDER BY created_dt_ DESC LIMIT 1) AS fnl_usr
FROM (
    -- 筛选记录数≥2的act_id
    SELECT act_id
    FROM "Account details"
    GROUP BY act_id
    HAVING COUNT(*) >= 2
) base;

注意事项 #

• 表名包含空格时，不同数据库的引号规则不同：MySQL用反引号`Account details`，PostgreSQL/Oracle用双引号"Account details"，需根据实际使用的数据库调整。
• 如果created_dt_存在重复值，ROW_NUMBER()会随机选择其中一条，若需确定性结果，可在排序时加上id作为辅助排序字段（比如ORDER BY created_dt_, id）。

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