如何在MySQL中计算排除周末的2天前日期？

Alright, let's tackle this problem step by step. Your core need is to calculate "2 business days prior" while accounting for weekends and specific edge cases (like Mondays returning no results, Tuesdays handling Saturday data, etc.). Here's how to adjust your MySQL query to match exactly what you described:

First, formalize your rule mapping #

Let's clarify the relationship between your query date and the target dates you need to process:

• Monday: No results (since there are no valid "2 business days prior" dates that count as workdays)
• Tuesday: Target is the previous Saturday (3 days before the query date)
• Wednesday: Targets are the previous Sunday and current Monday (3 and 2 days before the query date)
• Thursday-Friday: Target is 2 days before the query date (the previous Tuesday/Wednesday)
• Saturday-Sunday: Target is 2 days before the query date (the previous Thursday/Friday)

Improved SELECT query (for verification) #

Instead of using LIKE (which is inefficient and error-prone for datetime fields), we'll use DATE() to extract the date part, and WEEKDAY() to check the current day of the week (0 = Monday, 1 = Tuesday, ..., 6 = Sunday). This ensures accurate and efficient matching:

SELECT * 
FROM oc_order 
WHERE 
  -- Exclude Mondays entirely
  WEEKDAY(CURDATE()) != 0
  AND
  (
    -- Tuesday: Target is 3 days prior (Saturday)
    (WEEKDAY(CURDATE()) = 1 AND 
     ((status_id = '2' AND DATE(date_added) = CURDATE() - INTERVAL 3 DAY) OR 
      (status_id = '21' AND DATE(date_modified) = CURDATE() - INTERVAL 3 DAY)))
    OR
    -- Wednesday: Targets are 3 days prior (Sunday) and 2 days prior (Monday)
    (WEEKDAY(CURDATE()) = 2 AND 
     ((status_id = '2' AND DATE(date_added) IN (CURDATE() - INTERVAL 3 DAY, CURDATE() - INTERVAL 2 DAY)) OR 
      (status_id = '21' AND DATE(date_modified) IN (CURDATE() - INTERVAL 3 DAY, CURDATE() - INTERVAL 2 DAY))))
    OR
    -- Thursday-Sunday: Target is 2 days prior
    (WEEKDAY(CURDATE()) IN (3, 4, 5, 6) AND 
     ((status_id = '2' AND DATE(date_added) = CURDATE() - INTERVAL 2 DAY) OR 
      (status_id = '21' AND DATE(date_modified) = CURDATE() - INTERVAL 2 DAY)))
  );

Convert to automatic UPDATE statement #

Since you want to automatically update the status of these records daily, here's the UPDATE version (replace '3' with your desired target status):

UPDATE oc_order 
SET status_id = '3' -- Replace with your actual target status
WHERE 
  WEEKDAY(CURDATE()) != 0
  AND
  (
    (WEEKDAY(CURDATE()) = 1 AND 
     ((status_id = '2' AND DATE(date_added) = CURDATE() - INTERVAL 3 DAY) OR 
      (status_id = '21' AND DATE(date_modified) = CURDATE() - INTERVAL 3 DAY)))
    OR
    (WEEKDAY(CURDATE()) = 2 AND 
     ((status_id = '2' AND DATE(date_added) IN (CURDATE() - INTERVAL 3 DAY, CURDATE() - INTERVAL 2 DAY)) OR 
      (status_id = '21' AND DATE(date_modified) IN (CURDATE() - INTERVAL 3 DAY, CURDATE() - INTERVAL 2 DAY))))
    OR
    (WEEKDAY(CURDATE()) IN (3, 4, 5, 6) AND 
     ((status_id = '2' AND DATE(date_added) = CURDATE() - INTERVAL 2 DAY) OR 
      (status_id = '21' AND DATE(date_modified) = CURDATE() - INTERVAL 2 DAY)))
  );

Set up daily automatic execution #

To run this update automatically every day, use MySQL's Event Scheduler:

1. First, enable the event scheduler (you may need superuser privileges):

SET GLOBAL event_scheduler = ON;

2. Create a daily event (replace the start date with when you want it to begin):

CREATE EVENT daily_order_status_update
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 00:00:00' -- Replace with your desired start date/time
DO
BEGIN
  UPDATE oc_order 
  SET status_id = '3' -- Replace with your actual target status
  WHERE 
    WEEKDAY(CURDATE()) != 0
    AND
    (
      (WEEKDAY(CURDATE()) = 1 AND 
       ((status_id = '2' AND DATE(date_added) = CURDATE() - INTERVAL 3 DAY) OR 
        (status_id = '21' AND DATE(date_modified) = CURDATE() - INTERVAL 3 DAY)))
      OR
      (WEEKDAY(CURDATE()) = 2 AND 
       ((status_id = '2' AND DATE(date_added) IN (CURDATE() - INTERVAL 3 DAY, CURDATE() - INTERVAL 2 DAY)) OR 
        (status_id = '21' AND DATE(date_modified) IN (CURDATE() - INTERVAL 3 DAY, CURDATE() - INTERVAL 2 DAY))))
      OR
      (WEEKDAY(CURDATE()) IN (3, 4, 5, 6) AND 
       ((status_id = '2' AND DATE(date_added) = CURDATE() - INTERVAL 2 DAY) OR 
        (status_id = '21' AND DATE(date_modified) = CURDATE() - INTERVAL 2 DAY)))
    );
END;

Verify it matches your examples #

Let's cross-check with your test cases:

• 2017-09-22 (Friday, WEEKDAY=4): Pulls 2 days prior (2017-09-20, Wednesday) ✅
• 2017-09-23 (Saturday, WEEKDAY=5): Pulls 2 days prior (2017-09-21, Thursday) ✅
• 2017-09-24 (Sunday, WEEKDAY=6): Pulls 2 days prior (2017-09-22, Friday) ✅
• Monday (WEEKDAY=0): No results ✅
• 2017-09-26 (Tuesday, WEEKDAY=1): Pulls 3 days prior (2017-09-23, Saturday) ✅
• 2017-09-27 (Wednesday, WEEKDAY=2): Pulls 3 days prior (2017-09-24, Sunday) and 2 days prior (2017-09-25, Monday) ✅

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