SQL Join tables on Time Between Start and End

问题:

I have two tables:

Events (ID, Name, Time, Recorder)
Video (ID, StartTime, EndTime, Recorder, Filename)

I wish to join the Event data to the video data, so that for every event I get the video filename. The recorder field is used to specify which recorder was operable at the event's time, and assists with multiple recorders recording video at the same time.

If i wasn't concerned about the events which have no video then this is fine (I can get the SQL), however in my case i wish to show the closest video filename and the seconds difference.

EDIT

Sample Data

Events

1, EV1, 2012-01-01 12:00, A
2, EV2, 2012-01-01 13:00, B
3, EV3, 2012-01-01 12:15, B
4, EV4, 2012-01-01 11:45, A

Video

1, 2012-01-01 12:00, 2012-01-01 12:30, A, 1.mpg
2, 2012-01-01 13:00, 2012-01-01 13:30, A, 2.mpg 
3, 2012-01-01 12:00, 2012-01-01 12:30, B, 3.mpg

Result (EventID, VideoID, Filename, IsBetween, SecondsDifference)

1, 1, 1.mpg, TRUE, 0
2, 3, 3.mpg, FALSE, 1800 //1800 seconds from the end of video 3
3, 3, 3.mpg, TRUE, 900
4, 1, 1.mpg, FALSE, 900  //900 seconds from the start of video 1 

BONUS

I would be even nicer if the closest video did not take the recorder into account (but the first bounds (Start and End) check to take it into account) If this is too difficult then thats fine.

回答1:

It's a little clunky, but here's what I came up with:

SELECT
    *
FROM
    (
        SELECT
            a.ID AS EventID,
            b.ID AS VideoID,
            b.Filename,
            (
                CASE
                    WHEN a.Time < b.StartTime THEN UNIX_TIMESTAMP(b.StartTime) - UNIX_TIMESTAMP(a.Time)
                    WHEN a.Time > b.EndTime THEN UNIX_TIMESTAMP(a.Time) - UNIX_TIMESTAMP(b.EndTime)
                END
            ) AS distance_factor
        FROM
            `Events` a
        CROSS JOIN
            video b
        WHERE
            NOT EXISTS
            (
                SELECT NULL
                FROM Video
                WHERE a.Time BETWEEN StartTime AND EndTime
            )
    ) c
WHERE 
    c.distance_factor = 
    (
        SELECT
            MIN(CASE WHEN d.Time < e.StartTime THEN UNIX_TIMESTAMP(e.StartTime) - UNIX_TIMESTAMP(d.Time) WHEN d.Time > e.EndTime THEN UNIX_TIMESTAMP(d.Time) - UNIX_TIMESTAMP(e.EndTime) END)
        FROM
            `Events` d
        CROSS JOIN
            video e
        WHERE d.ID = c.EventID
    )
GROUP BY
    c.EventID

This returns events whose dates don't fall between any of the time ranges of any video, but then returns the video that falls the closest to that event date.

The only thing right now is that there are some videos where the seconds difference is exactly the same. I don't know if you want it to return 2 rows, but for now, I put in the GROUP BY to just select one.

Let me know how that works.

回答2:

My final result was:

SELECT * FROM
    (SELECT * FROM
        (SELECT * FROM
            (SELECT *, (CASE WHEN Time < StartTime THEN UNIX_TIMESTAMP(StartTime) - UNIX_TIMESTAMP(Time)
                    WHEN Time > EndTime THEN UNIX_TIMESTAMP(Time) - UNIX_TIMESTAMP(EndTime)
                END
                ) AS SecondsDifference 

            FROM
            (
                SELECT * FROM Events E
                    LEFT JOIN Video V ON (E.Time >= V.StartTime AND E.Time <= V.EndTime)
                    WHERE DVID IS NULL GROUP BY E.EventID
            ) A ORDER BY A.EventID, A.SecondsDifference
    ) B GROUP BY EventID
) C WHERE C.SecondsDifference IS NOT NULL

Essentially this first gets all events without any video, then joins this result on the entire video list, orders it by the EventID and ClosestSeconds, and then Groups the result by the EventID to remove the duplicates. Finally, I needed to remove any Events where the SecondsDifference was null.

It produces the same result as Zane's answer.

Thanks a lot Zane.