MySQL查询问题：如何基于最大日期获取前一周数据

你的第一个查询只返回部分数据的核心原因是子查询未应用与主查询相同的过滤条件：子查询select max(date) from price_trend取的是整个price_trend表的最大日期，而非你需要的commodity_id=0且mandi_id in(3)筛选后的最大日期。这导致日期范围的上限被限制在更早的日期，从而丢失了上月的历史数据。

修改后的查询方案 #

方案一：子查询添加匹配过滤条件 #

直接在两个日期计算的子查询中加入和主查询一致的过滤条件，确保取到的是目标数据集的最大日期：

Select 
   date, location 
from price_trend mar
inner join location man on mar.id = man.id
where 
    commodity_id = 0 
    and mandi_id in (3)  
    and date between (
        select date_sub(max(date), INTERVAL 7 day) 
        from price_trend 
        where commodity_id = 0 and mandi_id in (3)
    )
    and (
        select max(date) 
        from price_trend 
        where commodity_id = 0 and mandi_id in (3)
    );

方案二：用CTE复用最大日期（更简洁） #

通过公共表表达式(CTE)提前计算出目标数据集的最大日期，避免重复编写子查询：

WITH max_date_cte AS (
    SELECT max(date) AS max_dt
    FROM price_trend
    WHERE commodity_id = 0 and mandi_id in (3)
)
Select 
   mar.date, man.location 
from price_trend mar
inner join location man on mar.id = man.id
cross join max_date_cte
where 
    mar.commodity_id = 0 
    and mar.mandi_id in (3)  
    and mar.date between date_sub(max_date_cte.max_dt, INTERVAL 7 day) and max_date_cte.max_dt;

这两种修改方式都会先筛选出commodity_id=0且mandi_id in(3)的记录中的最大日期（即示例中的2022-12-03），再计算前7天的日期范围，最终返回和手动指定日期完全一致的完整一周数据。

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