如何在GROUP BY分组后获取最高ID对应的非NULL字段？

针对你的两个问题，本质上都是要在分组后拿到对应分组内最高ID（最新）的非NULL字段值，咱们结合你给出的数据示例来一步步解决。

先分析原SQL的问题 #

你写的这条SQL：

SELECT email, first_name, last_name from person group_by email order by id desc

存在两个关键问题：

1. 语法错误：group_by应该写成GROUP BY（下划线是错误写法，标准语法是空格分隔）；
2. 逻辑问题：当使用GROUP BY email时，first_name和last_name属于非聚合列，多数数据库（比如开启ONLY_FULL_GROUP_BY的MySQL）会直接报错，即使不报错，也只会返回分组内的任意一条记录，无法保证是你想要的最新非NULL值。

通用解决方案：使用窗口函数（推荐） #

窗口函数是现代SQL的标准特性，几乎所有主流数据库（MySQL 8.0+、PostgreSQL、SQL Server、Oracle等）都支持，能精准实现咱们的需求：

步骤说明 #

1. 按email分组，给每个分组内的记录排序：
   • 优先让first_name非NULL的记录排在前面；
   • 再按id降序排列（确保最新的记录在前）；
2. 给每条记录分配一个行号，每个分组内从1开始计数；
3. 筛选出每个分组内行号为1的记录，就是我们要的结果。

具体SQL代码 #

WITH ranked_persons AS (
    SELECT 
        email,
        first_name,
        last_name,
        id,
        -- 给每个email分组的记录排序：非NULL first_name优先，再按id降序
        ROW_NUMBER() OVER (
            PARTITION BY email 
            ORDER BY 
                CASE WHEN first_name IS NOT NULL THEN 0 ELSE 1 END,
                id DESC
        ) AS rn
    FROM person
)
SELECT email, first_name, last_name
FROM ranked_persons
WHERE rn = 1;

针对你的数据示例的执行结果 #

对于你给出的三条记录：

id | email | first_name | last_name
1 | a@b.com | ted | smith
2 | a@b.com | zed | smith
3 | a@b.com | NULL | johnson

这条SQL会先给a@b.com分组的三条记录排序：

• id=2的记录（first_name非NULL，id最大）行号为1；
• id=1的记录（first_name非NULL，id次之）行号为2；
• id=3的记录（first_name为NULL）行号为3；

最终筛选出rn=1的记录，也就是email: a@b.com, first_name: zed, last_name: smith，完全符合你的期望。

兼容旧版数据库的方案（无窗口函数） #

如果你的数据库不支持窗口函数（比如MySQL 5.x），可以用子查询来实现：

SELECT 
    p.email,
    (
        SELECT first_name 
        FROM person 
        WHERE email = p.email AND first_name IS NOT NULL 
        ORDER BY id DESC 
        LIMIT 1
    ) AS first_name,
    -- 同理，如果last_name也需要取最新非NULL值，用同样的逻辑
    (
        SELECT last_name 
        FROM person 
        WHERE email = p.email AND last_name IS NOT NULL 
        ORDER BY id DESC 
        LIMIT 1
    ) AS last_name
FROM (SELECT DISTINCT email FROM person) p;

这个方法的思路是：先取出所有不重复的email，然后对每个email，单独查询其对应的最新非NULL的first_name和last_name。

内容的提问来源于stack exchange，提问作者Dr. Chocolate