如何在GROUP BY与MAX聚合时匹配对应列数据

现有prices表结构 #

+----+----------+-------------+------------------+---------------------+---------+
| id | realtyId | priceTypeId | price            | date                | comment |
+----+----------+-------------+------------------+---------------------+---------+
|  1 |        1 |           1 |     7.1100000000 | 2022-07-16 20:51:47 | []      |
|  2 |        2 |           1 |     2.3400000000 | 2022-07-16 21:01:05 | []      |
|  3 |        2 |           2 | 23950.0000000000 | 2022-07-16 21:03:58 | []      |
|  4 |        4 |           1 |     6.1800000000 | 2022-07-16 21:27:59 | []      |
|  5 |        5 |           1 |     6.1800000000 | 2022-07-16 21:28:12 | []      |
|  6 |        6 |           1 |     6.1800000000 | 2022-07-16 21:28:23 | []      |
|  7 |        7 |           1 |     3.9200000000 | 2022-07-16 21:28:37 | []      |
|  8 |       10 |           1 |     3.4500000000 | 2022-07-16 22:01:05 | []      |
|  9 |       11 |           1 |     4.6600000000 | 2022-07-16 22:15:37 | []      |
| 10 |       16 |           1 |     4.2400000000 | 2022-07-16 22:23:25 | []      |
| 11 |       10 |           4 | 45000.0000000000 | 2022-07-16 22:28:22 | []      |
| 12 |       16 |           4 | 45000.0000000000 | 2022-07-16 22:35:40 | []      |
| 13 |        6 |           4 | 25000.0000000000 | 2022-07-16 22:37:27 | []      |
| 14 |       16 |           4 |  4633.0000000000 | 2022-07-31 16:56:33 | []      |
| 15 |        7 |           4 | 25584.0000000000 | 2022-07-31 16:57:11 | []      |
| 16 |        4 |           4 |  8485.0000000000 | 2022-07-31 18:32:36 | []      |
+----+----------+-------------+------------------+---------------------+---------+

需求 #

• 获取每个realtyId对应最高priceTypeId的price
• 若同一realtyId存在多个相同最高priceTypeId的记录，取date最新的那条

错误尝试及结果 #

执行以下SQL：

select id,realtyId,max(priceTypeId),price from prices group by realtyId

得到不符合预期的结果，例如realtyId=2返回的price为2.34，正确值应为23950：

+----+----------+------------------+--------------+
| id | realtyId | max(priceTypeId) | price        |
+----+----------+------------------+--------------+
|  1 |        1 |                1 | 7.1100000000 |
|  2 |        2 |                2 | 2.3400000000 |
|  4 |        4 |                4 | 6.1800000000 |
|  5 |        5 |                1 | 6.1800000000 |
|  6 |        6 |                4 | 6.1800000000 |
|  7 |        7 |                4 | 3.9200000000 |
|  8 |       10 |                4 | 3.4500000000 |
|  9 |       11 |                1 | 4.6600000000 |
| 10 |       16 |                4 | 4.2400000000 |
+----+----------+------------------+--------------+

错误原因 #

直接按realtyId分组后，price和id字段会取分组内的任意一条记录（不同数据库处理逻辑有差异），无法保证与max(priceTypeId)对应同一条数据，也无法满足日期最新的要求。

正确解法 #

使用窗口函数ROW_NUMBER()对每个realtyId内的记录排序：

1. 优先按priceTypeId降序，确保取最高类型
2. 同类型下按date降序，确保取最新日期的记录
3. 筛选出排序后序号为1的记录

SELECT id, realtyId, priceTypeId, price, date, comment
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY realtyId 
            ORDER BY priceTypeId DESC, date DESC
        ) AS rn
    FROM prices
) t
WHERE rn = 1;

执行结果 #

+----+----------+-------------+------------------+---------------------+---------+
| id | realtyId | priceTypeId | price            | date                | comment |
+----+----------+-------------+------------------+---------------------+---------+
|  1 |        1 |           1 |     7.1100000000 | 2022-07-16 20:51:47 | []      |
|  3 |        2 |           2 | 23950.0000000000 | 2022-07-16 21:03:58 | []      |
| 16 |        4 |           4 |  8485.0000000000 | 2022-07-31 18:32:36 | []      |
|  5 |        5 |           1 |     6.1800000000 | 2022-07-16 21:28:12 | []      |
| 13 |        6 |           4 | 25000.0000000000 | 2022-07-16 22:37:27 | []      |
| 15 |        7 |           4 | 25584.0000000000 | 2022-07-31 16:57:11 | []      |
| 11 |       10 |           4 | 45000.0000000000 | 2022-07-16 22:28:22 | []      |
|  9 |       11 |           1 |     4.6600000000 | 2022-07-16 22:15:37 | []      |
| 14 |       16 |           4 |  4633.0000000000 | 2022-07-31 16:56:33 | []      |
+----+----------+-------------+------------------+---------------------+---------+

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