如何为现有MySQL查询添加近一周日期范围限制？

你需要将现有拉取全量数据的MySQL查询限制为当前日期起一周内的记录，同时优化查询速度，最佳修改位置是在最内层的子查询（包含所有表JOIN的部分）中添加WHERE过滤条件——这样能在数据进行去重、排序和窗口函数计算前就过滤掉无关数据，大幅减少后续处理的数据量，从根源提升查询效率。

具体修改步骤 #

在最内层查询的ORDER BY子句之前，添加针对payment_date的日期范围过滤条件：

• 若需求是过去7天内（包含今天）的记录，使用：

  WHERE hmpayment.payment_date >= CURDATE() - INTERVAL 7 DAY
  

• 若需求是从当前日期开始往后7天内的记录，使用：

  WHERE hmpayment.payment_date BETWEEN CURDATE() AND CURDATE() + INTERVAL 7 DAY
  

修改后的完整查询代码 #

$sql = "SELECT tbase.property_name, 
  tbase.room_number, 
  tbase.customer_first_name, 
  tbase.customer_last_name, 
  tbase.amount_paid, 
  tbase.payment_date, 
  tbase.payment_method, 
  CASE 
    WHEN tbase.RowNumber = 1 THEN tbase.refund_amount 
    ELSE '' 
  END AS refundamount, 
  tbase.recipt_number, 
  tbase.room_standard_weekly_rate, 
  tbase.discount_amount, 
  tbase.Weekly_tariff 
FROM ( 
  SELECT 
    base.property_name, 
    base.room_number, 
    base.customer_first_name, 
    base.customer_last_name, 
    base.amount_paid, 
    base.payment_date, 
    base.payment_method, 
    ROW_NUMBER() OVER (PARTITION BY base.property_name, base.room_number, base.refund_amount ORDER BY base.property_name, base.room_number, base.refund_amount) AS RowNumber, 
    base.refund_amount, 
    base.recipt_number, 
    base.room_standard_weekly_rate, 
    base.discount_amount, 
    base.Weekly_tariff 
  FROM ( 
    SELECT 
      DISTINCT 
      hmprop.property_name AS property_name, 
      hmroom.room_number AS room_number, 
      hmcust.customer_first_name AS customer_first_name, 
      hmcust.customer_last_name AS customer_last_name, 
      hmpayment.amount_paid AS amount_paid, 
      hmpayment.payment_date AS payment_date, 
      hmpayment.payment_method AS payment_method, 
      hmbook.refund_amount AS refund_amount, 
      hmpayment.recipt_number AS recipt_number, 
      hmroom.room_standard_weekly_rate AS room_standard_weekly_rate, 
      discount.discount_amount AS discount_amount, 
      CASE 
        WHEN hmroom.room_standard_weekly_rate <> hmbook.weekly_tariff THEN 'Yes' 
        ELSE 'No' 
      END AS Weekly_tariff 
    FROM 
      hm_booking AS hmbook 
      JOIN hm_room AS hmroom ON hmbook.room_id = hmroom.room_id 
      INNER JOIN hm_customer AS hmcust ON hmbook.customer_id = hmcust.customer_id 
      INNER JOIN hm_booking_payment AS hmpayment ON hmbook.booking_id = hmpayment.booking_id 
      INNER JOIN hm_property AS hmprop ON hmprop.property_id = hmroom.property_id 
      LEFT JOIN hm_booking_discount AS discount ON discount.id = hmpayment.discount_id 
    -- 新增的日期过滤条件，根据需求二选一
    WHERE hmpayment.payment_date >= CURDATE() - INTERVAL 7 DAY
    -- WHERE hmpayment.payment_date BETWEEN CURDATE() AND CURDATE() + INTERVAL 7 DAY
    ORDER BY 
      hmprop.property_name, 
      hmroom.room_number ASC 
  ) AS base 
) AS tbase";

额外优化建议 #

1. 如果payment_date是DATETIME类型（含时分秒），可替换CURDATE()为NOW()来精确到时间范围，比如hmpayment.payment_date >= NOW() - INTERVAL 7 DAY。
2. 给hmpayment表的payment_date字段添加索引，避免全表扫描，进一步提升过滤效率。

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