如何在MySQL中按ID分组提取不同等级的最新数据到对应列

原始数据表 #

+----+-------+------+---------+-------------+-----------+
| Id | p_name| grade| promoted| created_on  | review_by |
+----+-------+------+---------+-------------+-----------+
| 1  | Abc   | A    |   Yes   | 2023-Jun-14 |    AK     |
| 1  | Abc   | A    |   Yes   | 2023-Jun-17 |    RSK    |
| 1  | Abc   | B    |   Yes   | 2023-Jun-15 |    PS     |
| 1  | Abc   | B    |   Yes   | 2023-Jun-16 |    ZX     |
| 2  | Pqr   | A    |   Yes   | 2023-May-10 |    CB     |
| 2  | Pqr   | B-   |   Yes   | 2023-May-05 |    MN     |
| 2  | Pqr   | B    |   Yes   | 2023-May-07 |    KL     |
+----+-------+------+---------+-------------+-----------+

期望结果 #

+----+-------+--------------+-------------------+--------------+------------------+
| Id | p_name| Date for AA  | review_by for AA  | Date for BB  | review_by for BB |
+----+-------+--------------+-------------------+--------------+------------------+ 
| 1  |  Abc  | 2023-Jun-17  |    RSK            | 2023-Jun-16  |      ZX          | 
| 2  |  Pqr  | 2023-May-10  |    CB             | 2023-May-07  |      KL          | 
+----+-------+--------------+-------------------+--------------+------------------+

需求条件 #

• 按Id分组，每组仅返回一条记录
• 筛选promoted = 'Yes'的记录：
  • 当grade属于(A,A-)时，提取最新的created_on存入Date for AA，对应review_by存入review_by for AA
  • 当grade属于(B,B-)时，提取最新的created_on存入Date for BB，对应review_by存入review_by for BB

解决方案 #

方法1：窗口函数+条件聚合（通用SQL）

适用于MySQL 8+、PostgreSQL、SQL Server等支持窗口函数的数据库：

WITH ranked_records AS (
    SELECT 
        Id,
        p_name,
        grade,
        created_on,
        review_by,
        -- 按ID和等级类别分组，每组内按日期倒序排名，最新记录排第1
        ROW_NUMBER() OVER (
            PARTITION BY Id, 
                         CASE WHEN grade IN ('A', 'A-') THEN 'AA' 
                              WHEN grade IN ('B', 'B-') THEN 'BB' 
                         END
            ORDER BY created_on DESC
        ) AS rn
    FROM your_table_name
    WHERE promoted = 'Yes'
      AND grade IN ('A', 'A-', 'B', 'B-')
)
SELECT 
    Id,
    p_name,
    MAX(CASE WHEN grade IN ('A', 'A-') AND rn = 1 THEN created_on END) AS "Date for AA",
    MAX(CASE WHEN grade IN ('A', 'A-') AND rn = 1 THEN review_by END) AS "review_by for AA",
    MAX(CASE WHEN grade IN ('B', 'B-') AND rn = 1 THEN created_on END) AS "Date for BB",
    MAX(CASE WHEN grade IN ('B', 'B-') AND rn = 1 THEN review_by END) AS "review_by for BB"
FROM ranked_records
GROUP BY Id, p_name;

思路说明：

1. 通过CTE给符合条件的记录按Id和等级类别（AA/BB）分组排名，锁定每个分组内的最新记录
2. 利用条件聚合提取每个Id下对应类别的最新记录字段，生成目标宽表

方法2：分别获取最新记录再关联（直观易懂）

适用于BigQuery、Snowflake、PostgreSQL 13+等支持QUALIFY子句的数据库：

WITH aa_latest AS (
    SELECT 
        Id,
        p_name,
        created_on AS "Date for AA",
        review_by AS "review_by for AA"
    FROM your_table_name
    WHERE promoted = 'Yes' AND grade IN ('A', 'A-')
    QUALIFY ROW_NUMBER() OVER (PARTITION BY Id ORDER BY created_on DESC) = 1
),
bb_latest AS (
    SELECT 
        Id,
        created_on AS "Date for BB",
        review_by AS "review_by for BB"
    FROM your_table_name
    WHERE promoted = 'Yes' AND grade IN ('B', 'B-')
    QUALIFY ROW_NUMBER() OVER (PARTITION BY Id ORDER BY created_on DESC) = 1
)
SELECT 
    a.Id,
    a.p_name,
    a."Date for AA",
    a."review_by for AA",
    b."Date for BB",
    b."review_by for BB"
FROM aa_latest a
JOIN bb_latest b ON a.Id = b.Id;

思路说明：

1. 用两个CTE分别提取每个Id下AA类和BB类的最新记录
2. 通过Id关联两个结果集，直接得到合并后的目标结果

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