sql - 范围联接，无重复项

Question

我有一个仅包含日期时间的表，如下所示：

           datetime
2016-05-16 10:00:00
2016-05-16 10:30:00
2016-05-16 11:00:00
2016-05-16 11:30:00
2016-05-16 12:00:00
2016-05-16 12:30:00
2016-05-16 13:00:00
2016-05-16 13:30:00
2016-05-16 14:00:00
2016-05-16 14:30:00
2016-05-16 15:00:00
2016-05-16 15:30:00
2016-05-16 16:00:00
2016-05-16 16:30:00
2016-05-16 17:00:00
2016-05-16 17:30:00
2016-05-16 18:00:00
2016-05-16 18:30:00
2016-05-16 19:00:00
2016-05-16 19:30:00

我想将此表与将某些时间间隔标记为 closed的表进行范围连接：

              start                stop closed
2016-05-04 16:56:07 2016-05-04 17:26:20   TRUE
2016-05-13 09:29:23 2016-05-14 21:27:17   TRUE
2016-05-14 21:00:39 2016-05-15 20:48:37   TRUE
2016-05-16 10:26:56 2016-05-16 11:48:24   TRUE
2016-05-16 11:33:42 2016-05-16 12:24:31   TRUE
2016-05-16 13:08:15 2016-05-16 14:27:42   TRUE
2016-05-16 15:46:07 2016-05-16 16:19:38   TRUE
2016-05-16 16:33:49 2016-05-16 17:52:31   TRUE
2016-05-16 16:34:12 2016-05-16 17:52:50   TRUE
2016-05-16 16:35:00 2016-05-16 17:53:33   TRUE

如您所见，其中一些间隔重叠，因此当我将两个表与

SELECT d.*, c.closed 
FROM dates d 
LEFT JOIN closures c
ON c.start <= d.datetime AND d.datetime <= c.stop

我在结果表中得到重复的行。如果至少有一个时间跨度而不复制任何行，是否可以将左表中的日期时间标记为已关闭？

Answer 1

您可以使用EXISTS()完全满足您的要求：

SELECT d.*,
       CASE WHEN EXISTS(SELECT 1 FROM closures c
                        WHERE c.start <= d.datetime AND d.datetime <= c.stop)
            THEN 'TRUE'
            ELSE 'FALSE'
       END as Your_Ind
FROM dates d

或者只是使用 DISTINCT：

SELECT DISTINCT d.*, c.closed 
FROM dates d 
LEFT JOIN closures c
ON c.start <= d.datetime AND d.datetime <= c.stop

如果只对已关闭的记录感兴趣，请在 EXISTS()子句上使用 WHERE。

 SELECT d.*,'TRUE'
FROM dates d
WHERE EXISTS(SELECT 1 FROM closures c
             WHERE c.start <= d.datetime AND d.datetime <= c.stop)