开始和结束时间之间的 SQL 连接表 [英] SQL Join tables on Time Between Start and End

问题描述

我有两张桌子:

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.

如果我不关心没有视频的事件，那么这很好(我可以获得 SQL)，但是在我的情况下，我希望显示最接近的视频文件名和秒差.

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.

编辑

样本数据

活动

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

视频

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

结果(事件 ID、视频 ID、文件名、IsBetween、SecondsDifference)

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 

奖金

如果最近的视频不考虑录像机，我会更好(但第一个边界(开始和结束)检查将其考虑在内)如果这太难了，那很好.

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.

推荐答案

这有点笨拙，但我想到了:

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.

现在唯一的问题是有些视频的秒差完全相同.我不知道你是否希望它返回 2 行，但现在，我把 GROUP BY 放在只选择一个.

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.

这篇关于开始和结束时间之间的 SQL 连接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！