高效获取SQL分组中最大值及其关联值的优雅方案
在SQL中，根据分组后的第三列，在另一列中查找最大值并获取其关联值，通常比较棘手。考虑以下场景：
现有如下表格：
| KEY | NUM | VAL |
|---|---|---|
| A | 1 | AB |
| B | 1 | CD |
| B | 2 | EF |
| C | 2 | GH |
| C | 3 | HI |
| D | 1 | JK |
| D | 3 | LM |
目标是为每个KEY找到最大NUM对应的VAL值。预期结果如下：
| KEY | VAL |
|---|---|
| A | AB |
| B | EF |
| C | HI |
| D | LM |
虽然可以使用以下查询达到目的：
<code class="language-sql">select KEY, VAL
from TABLE_NAME TN
where NUM = (
select max(NUM)
from TABLE_NAME TMP
where TMP.KEY = TN.KEY
)</code>但当KEY值较多时，这种方法显得笨拙。 然而，使用row_number()函数可以提供更优雅的解决方案：
<code class="language-sql">select key, val
from (select t.*, row_number() over (partition by key order by num desc) as seqnum
from table_name t
) t
where seqnum = 1;</code>这种方法利用了分区和排序的概念来获得期望的结果。通过将row_number()的分区设置为KEY，并按NUM降序排序，每个分区中的每一行都会分配一个序列号。然后选择序列号为1的行，即可获取每个KEY对应的最大NUM值及其关联的VAL值。
需要注意的是，这种改进后的方法与原始查询的行为略有不同。原始查询可能为每个KEY返回多行，而此解决方案确保每个KEY只返回一行。如果需要保留原始行为，则可以使用dense_rank()或rank()替换row_number()。
以上是如何通过分组高效地检索SQL中的最大值和关联值？的详细内容。更多信息请关注PHP中文网其他相关文章！
