基于时间范围匹配的工单表与班次表关联查询方案

Got it, let's figure out how to map your work orders to their correct shifts. First, let's clarify the tables you're working with (I've formatted them into readable tables for clarity):

Work Orders Table #

Shifts Table #

Implementation Solution #

The key challenge here is handling the overnight night shift (where StartTime is later than EndTime). Here's a SQL query that works across most databases, with adjustments for your table structure:

For SQL Server #

SELECT 
    wo.Date,
    wo.TimeOfEntry,
    wo.Plant,
    wo.ManufacturingLine,
    wo.OrderNumber,
    s.Shift AS ShiftName
FROM 
    WorkOrders wo
JOIN 
    Shifts s ON wo.Plant = s.Plant
WHERE 
    -- Handle regular shifts (Day/Afternoon: StartTime <= EndTime)
    (s.StartTime <= s.EndTime 
     AND CAST(CAST(wo.Date AS DATETIME) + CAST(wo.TimeOfEntry AS DATETIME) AS TIME) 
         BETWEEN s.StartTime AND s.EndTime)
    -- Handle overnight Night shifts (StartTime > EndTime)
    OR (s.StartTime > s.EndTime 
        AND (CAST(CAST(wo.Date AS DATETIME) + CAST(wo.TimeOfEntry AS DATETIME) AS TIME) >= s.StartTime 
             OR CAST(CAST(wo.Date AS DATETIME) + CAST(wo.TimeOfEntry AS DATETIME) AS TIME) <= s.EndTime))
ORDER BY 
    wo.Date, wo.TimeOfEntry;

For MySQL #

SELECT 
    wo.Date,
    wo.TimeOfEntry,
    wo.Plant,
    wo.ManufacturingLine,
    wo.OrderNumber,
    s.Shift AS ShiftName
FROM 
    WorkOrders wo
JOIN 
    Shifts s ON wo.Plant = s.Plant
WHERE 
    -- Regular shifts
    (s.StartTime <= s.EndTime 
     AND TIME(CONCAT(wo.Date, ' ', wo.TimeOfEntry)) 
         BETWEEN s.StartTime AND s.EndTime)
    -- Overnight shifts
    OR (s.StartTime > s.EndTime 
        AND (TIME(CONCAT(wo.Date, ' ', wo.TimeOfEntry)) >= s.StartTime 
             OR TIME(CONCAT(wo.Date, ' ', wo.TimeOfEntry)) <= s.EndTime))
ORDER BY 
    wo.Date, wo.TimeOfEntry;

Logic Explanation #

1. Plant Matching: We first join the two tables on the Plant field to ensure we only look at shifts relevant to the work order's plant.
2. Regular Shifts: For Day and Afternoon shifts (where StartTime is earlier than EndTime), we check if the work order's time falls directly between the shift's start and end times.
3. Overnight Shifts: For Night shifts (which span midnight), we check if the work order's time is either after the shift starts (22:01+) or before the shift ends (06:00-). This accounts for the shift crossing into the next day.

This query will correctly assign every work order to its corresponding shift, including edge cases like entries right at shift boundaries.

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