PostgreSQL中分组查询最大值对应完整记录的正确实现方法

这是个非常典型的SQL分组查询坑，咱们先搞懂为什么你原来的语句会报错，再给你几种靠谱的解决办法～

为什么原语句报错？ #

你写的select hostel, rollno, max(parent_inc) from students group by hostel;会报错，核心原因是SQL的分组逻辑要求：SELECT里的非聚合列必须出现在GROUP BY子句里，或者被聚合函数包裹。

这里rollno既没在GROUP BY里，也没用到SUM/MAX这类聚合函数，数据库根本不知道你要选每个宿舍里哪一个学生的学号——毕竟一个宿舍可能有N个学生，它没法凭空猜你要的是收入最高的那个对应的学号。

靠谱的解决方法 #

方法1：用窗口函数（推荐，通用型强） #

现在大部分主流数据库（MySQL 8.0+、PostgreSQL、SQL Server、Oracle等）都支持窗口函数，用ROW_NUMBER()或者RANK()可以轻松搞定：

SELECT hostel, rollno, parent_inc
FROM (
    SELECT 
        hostel, 
        rollno, 
        parent_inc,
        -- 按宿舍分组，每个组内按家长收入降序排，给每行打序号
        ROW_NUMBER() OVER (PARTITION BY hostel ORDER BY parent_inc DESC) AS rn
    FROM students
) AS ranked_students
WHERE rn = 1; -- 取每个组里排第一的行（收入最高的学生）

• 如果你想保留同一个宿舍里多个收入并列最高的学生，把ROW_NUMBER()换成RANK()就行——RANK会给并列的行相同的序号，不会跳过数字。

方法2：子查询+表关联（兼容老版本数据库） #

如果你的数据库不支持窗口函数（比如MySQL 5.x），可以先查出每个宿舍的最高收入，再和原表关联匹配对应的学生：

SELECT s.hostel, s.rollno, s.parent_inc
FROM students s
JOIN (
    -- 先查每个宿舍的最高家长收入
    SELECT hostel, MAX(parent_inc) AS max_inc
    FROM students
    GROUP BY hostel
) AS hostel_max
ON s.hostel = hostel_max.hostel 
AND s.parent_inc = hostel_max.max_inc;

这个方法的好处是兼容性强，缺点是如果同一个宿舍有多个学生收入并列最高，会把这些学生都查出来（和用RANK()的效果一致）。

方法3：PostgreSQL专属的DISTINCT ON语法 #

如果你用的是PostgreSQL，还有个更简洁的写法：

SELECT DISTINCT ON (hostel) hostel, rollno, parent_inc
FROM students
ORDER BY hostel, parent_inc DESC;

DISTINCT ON (hostel)会保证每个hostel只返回一行，然后通过ORDER BY parent_inc DESC指定取每个宿舍里收入最高的那行。

小提示 #

• 如果你的业务允许同一个宿舍有多个最高收入的学生，优先选方法2或者用RANK()的方法1；
• 要是只需要任意一个最高收入的学生，ROW_NUMBER()或者PostgreSQL的DISTINCT ON更高效。

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