带版本控制的节日活动表中基于日期关联筛选归属节日活动的SQL查询问题

Let's break down your problem and simplify the SQL query to get the exact results you need:

Key Requirements Recap #

1. Only use the latest version of each festival and event (since you're using version control with new records for edits).
2. Filter festivals that are active within your target date range (2021-01-01 to 2021-03-31).
3. Include only events that:
   • Fall within or overlap the target date range.
   • Have a date overlap with at least one active festival (event start ≤ festival end AND event end ≥ festival start).

Simplified SQL Query #

SET @exportStart = '2021-01-01';
SET @exportEnd = '2021-03-31';

WITH latest_records AS (
    -- Step 1: Get the highest version number for each eventId
    SELECT eventId, MAX(version) AS max_version
    FROM table1
    GROUP BY eventId
),
latest_data AS (
    -- Step 2: Retrieve the full latest record for each eventId
    SELECT t.*
    FROM table1 t
    INNER JOIN latest_records lr 
        ON t.eventId = lr.eventId AND t.version = lr.max_version
),
active_festivals AS (
    -- Step3: Filter festivals that are active in the target date range
    SELECT *
    FROM latest_data
    WHERE type = 'festival'
    AND (
        startDate BETWEEN @exportStart AND @exportEnd
        OR endDate BETWEEN @exportStart AND @exportEnd
        OR (startDate < @exportStart AND endDate > @exportEnd)
    )
)
-- Step4: Select events that overlap with active festivals and are in the target range
SELECT ld.eventId, ld.type, ld.name, ld.startDate, ld.endDate, ld.version
FROM latest_data ld
INNER JOIN active_festivals af 
    ON ld.startDate <= af.endDate 
    AND ld.endDate >= af.startDate
WHERE ld.type = 'event'
AND (
    ld.startDate BETWEEN @exportStart AND @exportEnd
    OR ld.endDate BETWEEN @exportStart AND @exportEnd
    OR (ld.startDate < @exportStart AND ld.endDate > @exportEnd)
);

Why This Works #

• CTEs for Reusability: The latest_records and latest_data CTEs eliminate the need to repeat the "get latest version" subquery multiple times, making the query cleaner and easier to maintain.
• Clear Separation of Concerns: We first isolate active festivals, then join events to them using the date overlap condition you specified.
• Exact Filtering: The join condition ld.startDate <= af.endDate AND ld.endDate >= af.startDate correctly captures all events that overlap with a festival (including events that start before the festival begins or end after it finishes, as long as there's any overlap).

Expected Result #

This query will return exactly the events you want:

• event2 (overlaps the start of the festival)
• event3 (falls entirely within the festival)
• event4 (overlaps the end of the festival)

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