在SQL的实际使用中，查询每个分组中最大的一条数据是高频需求，比如统计每个班级分数最高的学生、每个地区销售额最高的订单等。这类需求可以通过自连接或者窗口函数两种方案实现，两种方式各有特点，适用不同的场景。
方式一：使用自连接实现
自连接的核心思路是将同一张表看作两张独立的表进行关联，通过条件筛选找出每个分组中满足最大值条件的记录。假设我们有一张员工表employee，包含id、dept_id、name、salary字段，现在需要查询每个部门薪资最高的员工信息。
自连接实现步骤
- 将
employee表分别作为左表a和右表b，关联条件为部门id相同 - 筛选左表a的薪资大于等于右表b的薪资的记录
- 对结果按左表的id分组，确保每个员工只出现一次
示例代码
-- 创建测试表
CREATE TABLE employee (
id INT PRIMARY KEY,
dept_id INT,
name VARCHAR(50),
salary DECIMAL(10,2)
);
-- 插入测试数据
INSERT INTO employee VALUES
(1, 1, '张三', 8000),
(2, 1, '李四', 9000),
(3, 2, '王五', 7500),
(4, 2, '赵六', 8500),
(5, 3, '孙七', 10000);
-- 自连接查询每个部门薪资最高的员工
SELECT a.id, a.dept_id, a.name, a.salary
FROM employee a
LEFT JOIN employee b ON a.dept_id = b.dept_id AND a.salary < b.salary
WHERE b.salary IS NULL
ORDER BY a.dept_id;
上述查询中，左连接的条件是同部门下a的薪资小于b的薪资，如果存在这样的b记录，说明a不是部门最高薪资。最后筛选b.salary IS NULL的记录，就是每个部门薪资最高的员工。如果同一个部门有多个员工薪资相同且都是最高，这种方式会返回所有最高薪资的员工。
方式二：使用窗口函数实现
窗口函数是SQL中用于处理分组内排序、排名等场景的功能，使用ROW_NUMBER()、RANK()、DENSE_RANK()等函数配合OVER()子句，可以快速标记每个分组内的记录排序，从而筛选出最大值对应的记录。
常用窗口函数说明
| 函数名 | 作用 |
|---|---|
| ROW_NUMBER() | 为每个分组内的记录生成唯一的连续序号，相同值也会分配不同序号 |
| RANK() | 为每个分组内的记录生成排名，相同值排名相同，下一个排名会跳过重复的数量 |
| DENSE_RANK() | 为每个分组内的记录生成排名，相同值排名相同，下一个排名连续不跳过 |
示例代码
同样使用上面的employee表，查询每个部门薪资最高的员工：
-- 使用ROW_NUMBER()实现，每个部门只返回一条最高薪资记录（薪资相同取排序靠前的）
SELECT id, dept_id, name, salary
FROM (
SELECT
id,
dept_id,
name,
salary,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
FROM employee
) t
WHERE rn = 1
ORDER BY dept_id;
-- 使用DENSE_RANK()实现，每个部门返回所有最高薪资的员工（薪资相同都返回）
SELECT id, dept_id, name, salary
FROM (
SELECT
id,
dept_id,
name,
salary,
DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dr
FROM employee
) t
WHERE dr = 1
ORDER BY dept_id;
上述代码中，PARTITION BY dept_id表示按部门分组，ORDER BY salary DESC表示组内按薪资降序排序。子查询给每个部门的员工标记了排序序号，外层查询筛选序号为1的记录就是每个部门的最高薪资记录。
两种方式的对比
- 自连接兼容性好，几乎所有SQL数据库都支持，但代码逻辑相对复杂，数据量大的时候关联性能较差
- 窗口函数代码简洁，可读性强，性能通常优于自连接，但需要数据库支持窗口函数（MySQL 8.0+、PostgreSQL、SQL Server等主流版本都支持）
- 如果分组内存在多个相同的最大值记录，自连接默认返回所有记录，窗口函数可以通过选择不同的排名函数控制返回逻辑
实际使用中，如果数据库支持窗口函数，优先选择窗口函数实现分组取最大值的查询，代码更简洁，维护成本更低。如果使用的数据库版本较低不支持窗口函数，再选择自连接的方式实现。