SQL 日期范围查询 - 表比较

Question

我有两个包含以下信息的 SQL Server 表:

表t_venues:

venue_id 是唯一的

venue_id  |  start_date  |  end_date
       1  |  01/01/2014  |  02/01/2014
       2  |  05/01/2014  |  05/01/2014
       3  |  09/01/2014  |  15/01/2014
       4  |  20/01/2014  |  30/01/2014

表t_venueuser:

venue_id 不是唯一的

venue_id  |  start_date  |  end_date
       1  |  02/01/2014  |  02/01/2014
       2  |  05/01/2014  |  05/01/2014
       3  |  09/01/2014  |  10/01/2014
       4  |  23/01/2014  |  25/01/2014

从这两个表中，我需要找到尚未为每个范围选择的日期，因此输出将如下所示:

venue_id  |  start_date  |  end_date
       1  |  01/01/2014  |  01/01/2014
       3  |  11/01/2014  |  15/01/2014
       4  |  20/01/2014  |  22/01/2014
       4  |  26/01/2014  |  30/01/2014

我可以比较两个表，并使用“异常(exception)”获取 t_venues 中的日期范围以显示在我的查询中，但我无法让查询生成未选定的日期。任何帮助将不胜感激。

Answer 1

日历表!

日历表的另一个完美候选者。如果您懒得去搜索，here's one I made earlier .

设置数据

DECLARE @t_venues table (
   venue_id   int
 , start_date date
 , end_date   date
);

INSERT INTO @t_venues (venue_id, start_date, end_date)
  VALUES (1, '2014-01-01', '2014-01-02')
       , (2, '2014-01-05', '2014-01-05')
       , (3, '2014-01-09', '2014-01-15')
       , (4, '2014-01-20', '2014-01-30')
;

DECLARE @t_venueuser table (
   venue_id   int
 , start_date date
 , end_date   date
);

INSERT INTO @t_venueuser (venue_id, start_date, end_date)
  VALUES (1, '2014-01-02', '2014-01-02')
       , (2, '2014-01-05', '2014-01-05')
       , (3, '2014-01-09', '2014-01-10')
       , (4, '2014-01-23', '2014-01-25')
;

查询

SELECT t_venues.venue_id
     , calendar.the_date
     , CASE WHEN t_venueuser.venue_id IS NULL THEN 1 ELSE 0 END As is_available
FROM   dbo.calendar /* see: http://gvee.co.uk/files/sql/dbo.numbers%20&%20dbo.calendar.sql for an example */
 INNER
  JOIN @t_venues As t_venues
    ON t_venues.start_date <= calendar.the_date
   AND t_venues.end_date   >= calendar.the_date
 LEFT
  JOIN @t_venueuser As t_venueuser
    ON t_venueuser.venue_id = t_venues.venue_id
   AND t_venueuser.start_date <= calendar.the_date
   AND t_venueuser.end_date   >= calendar.the_date
ORDER
    BY t_venues.venue_id
     , calendar.the_date
;

结果

venue_id    the_date                is_available
----------- ----------------------- ------------
1           2014-01-01 00:00:00.000 1
1           2014-01-02 00:00:00.000 0
2           2014-01-05 00:00:00.000 0
3           2014-01-09 00:00:00.000 0
3           2014-01-10 00:00:00.000 0
3           2014-01-11 00:00:00.000 1
3           2014-01-12 00:00:00.000 1
3           2014-01-13 00:00:00.000 1
3           2014-01-14 00:00:00.000 1
3           2014-01-15 00:00:00.000 1
4           2014-01-20 00:00:00.000 1
4           2014-01-21 00:00:00.000 1
4           2014-01-22 00:00:00.000 1
4           2014-01-23 00:00:00.000 0
4           2014-01-24 00:00:00.000 0
4           2014-01-25 00:00:00.000 0
4           2014-01-26 00:00:00.000 1
4           2014-01-27 00:00:00.000 1
4           2014-01-28 00:00:00.000 1
4           2014-01-29 00:00:00.000 1
4           2014-01-30 00:00:00.000 1

(21 row(s) affected)

说明

我们的日历表包含每个日期的条目。

我们加入我们的t_venues (顺便说一句，如果您有选择，请丢掉 t_ 前缀!)每天在 start_date 之间返回和end_date 。 venue_id=4 的示例输出仅此加入:

venue_id    the_date
----------- -----------------------
4           2014-01-20 00:00:00.000
4           2014-01-21 00:00:00.000
4           2014-01-22 00:00:00.000
4           2014-01-23 00:00:00.000
4           2014-01-24 00:00:00.000
4           2014-01-25 00:00:00.000
4           2014-01-26 00:00:00.000
4           2014-01-27 00:00:00.000
4           2014-01-28 00:00:00.000
4           2014-01-29 00:00:00.000
4           2014-01-30 00:00:00.000

(11 row(s) affected)

现在我们每天一行，我们[外部]加入我们的t_venueuser table 。我们加入的方式与以前基本相同，但有一个额外的变化:我们需要基于 venue_id 加入也是!

运行此命令为 venue_id=4给出这个结果:

venue_id    the_date                t_venueuser_venue_id
----------- ----------------------- --------------------
4           2014-01-20 00:00:00.000 NULL
4           2014-01-21 00:00:00.000 NULL
4           2014-01-22 00:00:00.000 NULL
4           2014-01-23 00:00:00.000 4
4           2014-01-24 00:00:00.000 4
4           2014-01-25 00:00:00.000 4
4           2014-01-26 00:00:00.000 NULL
4           2014-01-27 00:00:00.000 NULL
4           2014-01-28 00:00:00.000 NULL
4           2014-01-29 00:00:00.000 NULL
4           2014-01-30 00:00:00.000 NULL

(11 row(s) affected)

看看我们如何拥有NULL没有 t_venueuser 的行的值记录。天才，不是吗？ ;-)

因此，在我的第一个查询中，我给了您一个显示可用性的快速 CASE 语句(1=可用，0=不可用)。这仅用于说明，但可能对您有用。

然后，您可以打包查询，然后在此计算列上应用额外的过滤器，或者仅在以下位置添加一个 where 子句: WHERE t_venueuser.venue_id IS NULL这会起到同样的作用。