SQL查询问题：获取2022年每月最高收盘价及对应日期

你的原查询存在逻辑问题：当你按month(date)分组时，select中的date字段既不是聚合函数也不在分组条件里，数据库会返回该月份分组内的任意一条日期（比如你看到的当月最后一天），而非对应最高收盘价的日期。

以下是两种通用的解决方案：

方法1：使用窗口函数（推荐，适用于MySQL 8+、PostgreSQL、SQL Server等） #

窗口函数可以在不分组的情况下，对每个月份内的记录进行排序，直接定位到最高收盘价的那条数据：

SELECT date, close
FROM (
    SELECT 
        date,
        close,
        ROW_NUMBER() OVER (PARTITION BY YEAR(date), MONTH(date) ORDER BY close DESC) AS rn
    FROM commodity
    WHERE commodity = 'rice' 
      AND location = 'pune' 
      AND YEAR(date) = 2022
) t
WHERE rn = 1
ORDER BY date DESC;

• 说明：
  • PARTITION BY YEAR(date), MONTH(date)：将数据按年份和月份拆分成独立分组
  • ORDER BY close DESC：在每个月的分组内，按收盘价从高到低排序
  • ROW_NUMBER()：给每个分组内的记录编号，最高收盘价的记录编号为1
  • 外层查询筛选编号为1的记录，就是每个月最高收盘价对应的日期和价格

如果当月存在多个相同最高收盘价的记录，想要返回所有并列结果，把ROW_NUMBER()换成RANK()即可。

方法2：使用子查询关联 #

先计算每个月的最高收盘价，再关联原表获取对应日期：

SELECT c.date, c.close
FROM commodity c
JOIN (
    SELECT 
        YEAR(date) AS year,
        MONTH(date) AS month,
        MAX(close) AS max_close
    FROM commodity
    WHERE commodity = 'rice' 
      AND location = 'pune' 
      AND YEAR(date) = 2022
    GROUP BY YEAR(date), MONTH(date)
) m ON YEAR(c.date) = m.year 
   AND MONTH(c.date) = m.month 
   AND c.close = m.max_close
WHERE c.commodity = 'rice' 
  AND c.location = 'pune' 
  AND YEAR(c.date) = 2022
ORDER BY c.date DESC;

• 说明：
  • 子查询先算出2022年每个月rice在pune的最高收盘价
  • 外层查询通过年份、月份和收盘价关联原表，匹配到对应的日期

额外提示 #

原查询的where条件存在语法错误，year(date) ='2022'前面需要添加AND运算符。

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