如何编写SQL获取分组数据的最值及对应日期

原始数据 #

期望查询结果 #

完善后的SQL方案 #

方案一：使用窗口函数（推荐，处理重复价格场景更灵活） #

WITH price_rankings AS (
    SELECT 
        ID_group,
        Price,
        Date,
        -- 按价格降序排序，取每个分组的最高价记录
        ROW_NUMBER() OVER (PARTITION BY ID_group ORDER BY Price DESC) AS max_price_rank,
        -- 按价格升序排序，取每个分组的最低价记录
        ROW_NUMBER() OVER (PARTITION BY ID_group ORDER BY Price ASC) AS min_price_rank
    FROM your_table -- 替换为实际表名
)
SELECT 
    max_rec.ID_group,
    max_rec.Price AS MaxPrice,
    max_rec.Date AS MaxPriceDate,
    min_rec.Price AS MinPrice,
    min_rec.Date AS MinPriceDate
FROM price_rankings max_rec
JOIN price_rankings min_rec 
    ON max_rec.ID_group = min_rec.ID_group
WHERE max_rec.max_price_rank = 1 
  AND min_rec.min_price_rank = 1
GROUP BY max_rec.ID_group, max_rec.Price, max_rec.Date, min_rec.Price, min_rec.Date;

方案二：使用关联子查询（写法简洁） #

SELECT 
    t.ID_group,
    MAX(t.Price) AS MaxPrice,
    -- 子查询获取对应分组最高价的日期
    (SELECT Date FROM your_table WHERE ID_group = t.ID_group AND Price = MAX(t.Price)) AS MaxPriceDate,
    MIN(t.Price) AS MinPrice,
    -- 子查询获取对应分组最低价的日期
    (SELECT Date FROM your_table WHERE ID_group = t.ID_group AND Price = MIN(t.Price)) AS MinPriceDate
FROM your_table t
GROUP BY t.ID_group;

说明 #

• 注意将代码中的your_table替换为实际的表名，避免使用table作为表名（属于SQL关键字，会引发语法错误）。
• 如果同一分组内存在多个相同的最高/最低价格，方案一中的ROW_NUMBER()会返回排序后的第一条记录；若需返回所有匹配记录，可改用RANK()函数。

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