基于tb2更新临时表tb1餐饮数据的SQL求助（含工作日/周末规则）

Alright, let’s break down how to solve this problem step by step. First, let’s confirm I’ve got the requirements straight: we need to update tb1’s breakfast, lunch, and dinner fields using the count of matching rows from tb2 where tb1.date falls between tb2.arrival date and tb2.departure date. Plus, we have rules:

• Only update breakfast and dinner if the day is a weekday
• Only update breakfast and lunch if the day is a weekend

First: Clarify Weekday/Weekend Logic #

First, we need to align on how tb1.[day of week] is formatted. For most databases, common conventions are:

• Weekdays: Monday–Friday (values 1–5, adjust if your system uses 1=Sunday)
• Weekends: Saturday–Sunday (values 6–7)

I’ll use this convention in the examples, but I’ll note how to adjust for other systems later.

Step 1: The Core Update Query #

Here’s a robust implementation using a subquery to calculate the matching row count from tb2, then joining it to tb1 for the update. This works for MySQL out of the box:

UPDATE tb1
JOIN (
    -- Subquery to get the number of tb2 stays covering each tb1 date
    SELECT 
        t1.date,
        COUNT(t2.*) AS stay_count
    FROM tb1 t1
    LEFT JOIN tb2 t2
        ON t1.date BETWEEN t2.`arrival date` AND t2.`departure date`
    GROUP BY t1.date
) AS date_matches ON tb1.date = date_matches.date
SET 
    -- Update breakfast + dinner only on weekdays
    breakfast = CASE WHEN tb1.`day of week` IN (1,2,3,4,5) THEN date_matches.stay_count ELSE breakfast END,
    dinner = CASE WHEN tb1.`day of week` IN (1,2,3,4,5) THEN date_matches.stay_count ELSE dinner END,
    -- Update breakfast + lunch only on weekends
    lunch = CASE WHEN tb1.`day of week` IN (6,7) THEN date_matches.stay_count ELSE lunch END;

Adjustments for Other Databases #

• SQL Server: If your day of week uses 1=Sunday, adjust the weekday range to 2–6 (Monday–Friday). The UPDATE syntax also needs a small tweak:

  UPDATE tb1
  SET 
      breakfast = CASE WHEN tb1.[day of week] BETWEEN 2 AND 6 THEN date_matches.stay_count ELSE breakfast END,
      dinner = CASE WHEN tb1.[day of week] BETWEEN 2 AND 6 THEN date_matches.stay_count ELSE dinner END,
      lunch = CASE WHEN tb1.[day of week] IN (1,7) THEN date_matches.stay_count ELSE lunch END
  FROM tb1
  JOIN (
      SELECT 
          t1.date,
          COUNT(t2.*) AS stay_count
      FROM tb1 t1
      LEFT JOIN tb2 t2
          ON t1.date BETWEEN t2.[arrival date] AND t2.[departure date]
      GROUP BY t1.date
  ) AS date_matches ON tb1.date = date_matches.date;
  

• PostgreSQL: Use EXTRACT(DOW FROM t1.date) where 0=Sunday, 1=Monday…6=Saturday. Adjust the CASE conditions to match (e.g., weekdays = 1–5, weekends = 0,6).

Step 2: Verify the Results #

After running the update, double-check the changes with a simple select:

SELECT * FROM tb1 ORDER BY date;

Example Result Screenshot #

Let’s say we have sample data:

• tb1 has dates: 2024-05-20 (Monday, weekday) and 2024-05-25 (Saturday, weekend)
• tb2 has two stays: one covering 2024-05-19 to 2024-05-22, another covering 2024-05-24 to 2024-05-26

The updated tb1 would look like this:

Sample Updated tb1 Table
(Replace with your actual screenshot URL once you execute the query)

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