如何使用SELECT GREATEST提取每行前4个最大值并结构化展示？
2026-7-2
问题描述
现有如下数据表：
| ID | COL_1 | COL_2 | COL_3 | COL_4 | COL_5 | COL_6 |
|---|---|---|---|---|---|---|
| 1 | 2 | 5 | 2 | 9 | 4 | 7 |
| 2 | 2 | 4 | 3 | 5 | 4 | 7 |
| 4 | 2 | 2 | 3 | 5 | 4 | 8 |
| 5 | 3 | 2 | 3 | 5 | 5 | 9 |
| 6 | 4 | 3 | 4 | 6 | 6 | 8 |
| 7 | 5 | 7 | 5 | 7 | 6 | 4 |
| 8 | 6 | 8 | 6 | 8 | 9 | 7 |
需要通过SELECT结合GREATEST函数，提取每行中的前4个最大值，输出如下结构的结果：
| ID | COL_1 | COL_2 | COL_3 | COL_4 |
|---|---|---|---|---|
| 1 | 9 | 7 | 5 | 4 |
| 2 | 7 | 5 | 4 | 3 |
| 4 | 8 | 5 | 4 | 3 |
| 5 | 9 | 5 | 3 | 2 |
| 6 | 8 | 6 | 4 | 3 |
| 7 | 7 | 6 | 5 | 4 |
| 8 | 9 | 8 | 7 | 6 |
解决方案
核心思路是通过嵌套GREATEST和NULLIF函数，依次提取每行的第1至第4大值：每次提取当前最大值后，将该值从候选集中排除（替换为NULL），再提取下一个最大值（GREATEST会自动忽略NULL）。
优化后的SQL语句（使用CTE减少重复计算）
WITH row_max_values AS ( SELECT ID, COL_1, COL_2, COL_3, COL_4, COL_5, COL_6, -- 第1大值 GREATEST(COL_1, COL_2, COL_3, COL_4, COL_5, COL_6) AS max1, -- 第2大值：排除max1后取最大 GREATEST( NULLIF(COL_1, GREATEST(COL_1, COL_2, COL_3, COL_4, COL_5, COL_6)), NULLIF(COL_2, GREATEST(COL_1, COL_2, COL_3, COL_4, COL_5, COL_6)), NULLIF(COL_3, GREATEST(COL_1, COL_2, COL_3, COL_4, COL_5, COL_6)), NULLIF(COL_4, GREATEST(COL_1, COL_2, COL_3, COL_4, COL_5, COL_6)), NULLIF(COL_5, GREATEST(COL_1, COL_2, COL_3, COL_4, COL_5, COL_6)), NULLIF(COL_6, GREATEST(COL_1, COL_2, COL_3, COL_4, COL_5, COL_6)) ) AS max2 FROM your_table_name -- 替换为你的实际表名 ) SELECT ID, max1 AS COL_1, max2 AS COL_2, -- 第3大值：排除max1和max2后取最大 GREATEST( NULLIF(NULLIF(COL_1, max1), max2), NULLIF(NULLIF(COL_2, max1), max2), NULLIF(NULLIF(COL_3, max1), max2), NULLIF(NULLIF(COL_4, max1), max2), NULLIF(NULLIF(COL_5, max1), max2), NULLIF(NULLIF(COL_6, max1), max2) ) AS COL_3, -- 第4大值：排除前3大值后取最大 GREATEST( NULLIF(NULLIF(NULLIF(COL_1, max1), max2), GREATEST( NULLIF(NULLIF(COL_1, max1), max2), NULLIF(NULLIF(COL_2, max1), max2), NULLIF(NULLIF(COL_3, max1), max2), NULLIF(NULLIF(COL_4, max1), max2), NULLIF(NULLIF(COL_5, max1), max2), NULLIF(NULLIF(COL_6, max1), max2) )), NULLIF(NULLIF(NULLIF(COL_2, max1), max2), GREATEST( NULLIF(NULLIF(COL_1, max1), max2), NULLIF(NULLIF(COL_2, max1), max2), NULLIF(NULLIF(COL_3, max1), max2), NULLIF(NULLIF(COL_4, max1), max2), NULLIF(NULLIF(COL_5, max1), max2), NULLIF(NULLIF(COL_6, max1), max2) )), NULLIF(NULLIF(NULLIF(COL_3, max1), max2), GREATEST( NULLIF(NULLIF(COL_1, max1), max2), NULLIF(NULLIF(COL_2, max1), max2), NULLIF(NULLIF(COL_3, max1), max2), NULLIF(NULLIF(COL_4, max1), max2), NULLIF(NULLIF(COL_5, max1), max2), NULLIF(NULLIF(COL_6, max1), max2) )), NULLIF(NULLIF(NULLIF(COL_4, max1), max2), GREATEST( NULLIF(NULLIF(COL_1, max1), max2), NULLIF(NULLIF(COL_2, max1), max2), NULLIF(NULLIF(COL_3, max1), max2), NULLIF(NULLIF(COL_4, max1), max2), NULLIF(NULLIF(COL_5, max1), max2), NULLIF(NULLIF(COL_6, max1), max2) )), NULLIF(NULLIF(NULLIF(COL_5, max1), max2), GREATEST( NULLIF(NULLIF(COL_1, max1), max2), NULLIF(NULLIF(COL_2, max1), max2), NULLIF(NULLIF(COL_3, max1), max2), NULLIF(NULLIF(COL_4, max1), max2), NULLIF(NULLIF(COL_5, max1), max2), NULLIF(NULLIF(COL_6, max1), max2) )), NULLIF(NULLIF(NULLIF(COL_6, max1), max2), GREATEST( NULLIF(NULLIF(COL_1, max1), max2), NULLIF(NULLIF(COL_2, max1), max2), NULLIF(NULLIF(COL_3, max1), max2), NULLIF(NULLIF(COL_4, max1), max2), NULLIF(NULLIF(COL_5, max1), max2), NULLIF(NULLIF(COL_6, max1), max2) )) ) AS COL_4 FROM row_max_values;
逻辑说明
- 提取第1大值：直接用
GREATEST获取该行所有列的最大值。 - 提取第2大值：用
NULLIF将所有等于第1大值的内容替换为NULL，再用GREATEST从剩余非NULL值中取最大。 - 提取第3、4大值：重复上述逻辑，依次排除已提取的前N大值，再从剩余值中取最大。
注意：如果某行存在多个相同的最大值（如第7行的COL_2和COL_4均为7），NULLIF会将所有对应值替换为NULL，不影响后续提取逻辑。
内容的提问来源于stack exchange，提问作者AleksRous
相关产品推荐
相关产品推荐
