在数据库操作中，经常需要从一个表中选出每个分组（例如按字段 a 分组）中某字段（例如 b）最大值的行。本文以一个实际案例为基础，展示如何设计一个表，插入 10W 条测试数据，并比较多种 SQL 查询写法来实现“选出每个 a 值对应的最大 b 值的所有行”。我们将分析每种写法的优缺点，并提供完整的实践步骤，适用于现代数据库（如 PostgreSQL、MySQL 8.0+、SQL Server 等）。
背景
假设我们有一个表 test_table，包含字段 a（分组依据）、b（要取最大值的字段）和 other_column（其他数据列）。目标是：
- 创建表并插入 10W 条测试数据，模拟 100 个分组（
a从 1 到 100），每个分组约有 100 条记录。 - 查询每个
a值对应的最大b值的所有行，包含所有列。 - 比较不同 SQL 写法的性能、可读性和适用场景。
实践步骤
步骤 1：创建表结构
我们创建一个简单的表 test_table，包含以下字段：
a：整数，分组字段。b：整数，要取最大值的字段。other_column：字符串，模拟额外数据列。
CREATE TABLE test_table (
a INT,
b INT,
other_column VARCHAR(50)
);
为优化查询性能，添加复合索引：
CREATE INDEX idx_a_b ON test_table(a, b);
说明：索引 (a, b) 可加速分组和排序操作，尤其对窗口函数和连接查询有效。
步骤 2：插入 10W 条测试数据
我们生成 10W 条随机数据：
a：随机整数，范围 1 到 100，模拟 100 个分组。b：随机整数，范围 1 到 1000。other_column：生成格式为Data_00001到Data_10000的字符串。
PostgreSQL 插入脚本
PostgreSQL 使用 GENERATE_SERIES 生成序列，适合快速插入大量数据：
INSERT INTO test_table (a, b, other_column)
SELECT
FLOOR(RANDOM() * 100 + 1)::INT AS a, -- 随机 a (1到100)
FLOOR(RANDOM() * 1000 + 1)::INT AS b, -- 随机 b (1到1000)
'Data_' || LPAD(ROW_NUMBER() OVER ()::TEXT, 5, '0') AS other_column
FROM GENERATE_SERIES(1, 100000);
MySQL 插入脚本
MySQL 不支持 GENERATE_SERIES，使用存储过程插入数据：
DELIMITER //
CREATE PROCEDURE insert_test_data()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= 100000 DO
INSERT INTO test_table (a, b, other_column)
VALUES (
FLOOR(RAND() * 100 + 1), -- 随机 a (1到100)
FLOOR(RAND() * 1000 + 1), -- 随机 b (1到1000)
CONCAT('Data_', LPAD(i, 5, '0'))
);
SET i = i + 1;
END WHILE;
END //
DELIMITER ;
CALL insert_test_data();
验证数据
插入后，检查数据量和分布：
SELECT COUNT(*) FROM test_table; -- 应返回 100000
SELECT a, COUNT(*) FROM test_table GROUP BY a; -- 检查 a 的分布
步骤 3：查询每个 a 的最大 b 值行
我们需要选出每个 a 值对应的最大 b 值的所有行（包括重复的最大值），返回所有列。以下是四种常见写法，适用于不同场景。
写法 1：窗口函数（推荐）
窗口函数在现代数据库中性能优异，代码简洁，适合大数据量场景。
SELECT a, b, other_column
FROM (
SELECT a, b, other_column,
RANK() OVER (PARTITION BY a ORDER BY b DESC) AS rnk
FROM test_table
) t
WHERE rnk = 1;
优点：
- 性能高：只需扫描表一次，配合
(a, b)索引效率更高（查看查询计划type为ALL,如果子查询中加入where条件，type为range， 使用索引）。 - 灵活：
RANK()返回所有最大b的行；若需单行，可用ROW_NUMBER()。 - 可读性好：逻辑清晰，易于扩展（如添加其他排序条件）。
缺点：
- 兼容性：仅适用于支持窗口函数的数据库（MySQL 8.0+、PostgreSQL、SQL Server、Oracle）。
适用场景：现代数据库、数据量较大（万行以上）、需要处理重复值。
写法 2：相关子查询
使用子查询找出每个 a 的最大 b，然后匹配行。
SELECT *
FROM test_table t1
WHERE b = (SELECT MAX(b) FROM test_table t2 WHERE t2.a = t1.a);
优点：
- 直观：逻辑简单，易于理解。
- 通用：适用于所有 SQL 数据库，包括老版本（如 MySQL 5.x）。
- 正确处理重复值。
缺点：
- 性能较差：相关子查询为每行执行一次子查询，大数据量时可能导致全表扫描。
- 依赖索引：需要
(a, b)索引，否则效率低。
适用场景：小数据量（千行以内）、不支持窗口函数的数据库、快速开发。
写法 3：自连接
通过自连接检查是否存在更大的 b 值。
SELECT t1.*
FROM test_table t1
LEFT JOIN test_table t2
ON t1.a = t2.a AND t2.b > t1.b
WHERE t2.b IS NULL;
优点：
- 兼容性强：适用于所有数据库。
- 索引优化：配合
(a, b)索引，性能可接受。 - 处理重复值正确。
缺点：
- 性能：可能不如窗口函数，连接操作开销较大。
- 可读性：逻辑稍复杂。
适用场景：不支持窗口函数的数据库、中等数据量、需要兼容性。
写法 4：分组后连接
先计算每个 a 的最大 b，再连接回原表。
SELECT t1.*
FROM test_table t1
INNER JOIN (
SELECT a, MAX(b) AS max_b
FROM test_table
GROUP BY a
) t2
ON t1.a = t2.a AND t1.b = t2.max_b;
优点：
- 性能好：
GROUP BY扫描一次表，配合索引效率高。 - 兼容性：适用于所有数据库。
- 正确处理重复值。
缺点：
- 代码稍长：需要子查询和连接。
- 索引依赖：需要
(a, b)索引。
适用场景：不支持窗口函数的数据库、大数据量、需要兼容性。
步骤 4：示例数据与结果
假设插入的部分数据如下：
INSERT INTO test_table (a, b, other_column) VALUES
(1, 200, 'Data_00001'),
(1, 800, 'Data_00002'),
(1, 500, 'Data_00003'),
(2, 300, 'Data_00004'),
(2, 900, 'Data_00005'),
(2, 900, 'Data_00006'),
(3, 100, 'Data_00007'),
(3, 600, 'Data_00008');
运行窗口函数查询：
SELECT a, b, other_column
FROM (
SELECT a, b, other_column,
RANK() OVER (PARTITION BY a ORDER BY b DESC) AS rnk
FROM test_table
) t
WHERE rnk = 1;
结果：
| a | b | other_column |
|---|-----|--------------|
| 1 | 800 | Data_00002 |
| 2 | 900 | Data_00005 |
| 2 | 900 | Data_00006 |
| 3 | 600 | Data_00008 |
说明：
a = 1：最大b是 800，返回 1 行。a = 2：最大b是 900，有 2 行（重复值），都返回。a = 3：最大b是 600，返回 1 行。- 对于10W 条数据，假设
a分布在 1 到 100，结果约为 100 行（可能更多，若有重复）。
其他写法（相关子查询、自连接、分组后连接）会返回相同结果，但性能和可读性有所差异。
步骤 5：性能优化与验证
- 检查索引：确保
(a, b)索引存在：CREATE INDEX idx_a_b ON test_table(a, b); - 分析查询计划：使用
EXPLAIN或EXPLAIN ANALYZE检查查询是否利用索引：EXPLAIN SELECT a, b, other_column FROM ( SELECT a, b, other_column, RANK() OVER (PARTITION BY a ORDER BY b DESC) AS rnk FROM test_table ) t WHERE rnk = 1; - 性能测试：
- 10W 条数据：窗口函数通常最快，其次是分组后连接，相关子查询较慢。
- 若数据量增至百万级，窗口函数或分组后连接配合索引表现更优。
- 处理特殊情况：
- 重复值：
RANK()确保返回所有最大b的行；若需单行，用ROW_NUMBER()。 - 空值：若
b可能为 NULL，可在查询中添加COALESCE(b, 0)或WHERE b IS NOT NULL。
- 重复值：
写法比较
| 写法 | 性能（10,000 行） | 兼容性 | 可读性 | 重复值处理 | 适用场景 |
|---|---|---|---|---|---|
| 窗口函数 | 高 | MySQL 8.0+、PostgreSQL 等 | 中 | 支持 | 大数据量、现代数据库 |
| 相关子查询 | 中低 | 所有数据库 | 高 | 支持 | 小数据量、快速开发 |
| 自连接 | 中 | 所有数据库 | 中 | 支持 | 不支持窗口函数、中等数据量 |
| 分组后连接 | 高 | 所有数据库 | 中 | 支持 | 不支持窗口函数、大数据量 |
推荐：优先使用窗口函数（RANK()），因为：
- 性能最佳：单次表扫描，索引利用率高。
- 代码简洁：逻辑清晰，易于维护。
- 灵活：可处理重复值或扩展排序条件。
若数据库不支持窗口函数（如 MySQL 5.x），推荐分组后连接。
扩展与注意事项
- 数据库兼容性：
- PostgreSQL、SQL Server、Oracle：支持所有写法，窗口函数最优。
- MySQL 5.x：不支持窗口函数，使用分组后连接或自连接。
- 数据量扩展：
- 百万级数据：窗口函数或分组后连接配合索引仍高效。
- 更大规模：考虑分区表或分布式数据库。
- 其他需求：
- 若
b有 NULL 值，添加WHERE b IS NOT NULL或COALESCE。 - 若需按其他字段排序（如
other_column），在窗口函数中调整ORDER BY。
- 若
- 性能监控：
- 定期检查索引使用情况。
- 对于生产环境，测试查询性能并调整。
结论
通过插入 10W 条测试数据并比较四种 SQL 写法，我们发现窗口函数（RANK()）在现代数据库中通常是最优选择，兼顾性能、可读性和灵活性。对于不支持窗口函数的数据库，分组后连接是次优选择。实践时，确保创建适当索引，并根据数据量和数据库版本选择合适写法。