如何实现多表多日期逻辑匹配？获取SUSPENDED前最近Job_ID

Let's break down why your current query isn't working as expected: your join is pairing every Job entry that falls within the 7-day window of any SUSPENDED step, and the CASE WHEN doesn't filter down to the latest job for each specific suspension event. Instead, it's returning all matches (but you're only seeing the earliest one due to implicit ordering or lack of aggregation).

Here are two reliable approaches to get the result you want:

Approach 1: Using Correlated Subquery #

This method directly fetches the latest job_id for each SUSPENDED event by filtering jobs that meet the time criteria and selecting the one with the most recent stopped timestamp.

SELECT
    (SELECT j.job_id
     FROM Job j
     WHERE j.task_id = wfh.task_id
       AND j.stopped >= wfh.dateChange - INTERVAL 7 DAY
       AND j.stopped < wfh.dateChange
     ORDER BY j.stopped DESC
     LIMIT 1) AS susp_job_id,
    j.stopped,
    wfh.wfh_id,
    wfh.dateChange,
    wfh.step
FROM WorkFlowHistory wfh
JOIN Job j ON j.job_id = (
    SELECT job_id
    FROM Job
    WHERE task_id = wfh.task_id
      AND stopped >= wfh.dateChange - INTERVAL 7 DAY
      AND stopped < wfh.dateChange
    ORDER BY stopped DESC
    LIMIT 1
)
WHERE wfh.step = 'SUSPENDED'
  AND wfh.task_id = 100;

Approach 2: Using Window Functions (RANK()) #

If you prefer using window functions, this will rank jobs by their stopped time (descending) for each suspension event, then pick the top-ranked (latest) job.

WITH JobSuspensionMatches AS (
    SELECT
        j.job_id,
        j.stopped,
        wfh.wfh_id,
        wfh.dateChange,
        wfh.step,
        RANK() OVER (PARTITION BY wfh.wfh_id ORDER BY j.stopped DESC) AS job_rank
    FROM Job j
    JOIN WorkFlowHistory wfh
        ON j.task_id = wfh.task_id
    WHERE wfh.step = 'SUSPENDED'
      AND wfh.task_id = 100
      AND j.stopped >= wfh.dateChange - INTERVAL 7 DAY
      AND j.stopped < wfh.dateChange
)
SELECT job_id, stopped, wfh_id, dateChange, step
FROM JobSuspensionMatches
WHERE job_rank = 1;

Why This Works: #

• Both approaches ensure we're matching each SUSPENDED event to only the latest job that completed within the 7-day window before the suspension.
• The correlated subquery is straightforward for this one-to-one matching scenario, while the window function approach is more flexible if you ever need to see multiple top jobs later.

Testing either query with your sample data should return exactly the expected result set you provided.

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