Access SQL查询指定日期区间内可用场地的实现方案

表结构与数据 #

我在Access MDB数据库中有一张记录场地及其占用日期的locations表，表结构和数据如下：

CREATE TABLE locations (
  LocationID int(11) NOT NULL,
  Location varchar(255) NOT NULL,
  StartDate date NOT NULL,
  EndDate date NULL DEFAULT NULL
);

INSERT INTO locations VALUES (1, 'Venue A', '2023-10-01', '2023-10-10');
INSERT INTO locations VALUES (2, 'Venue B', '2023-10-15', '2023-10-20');
INSERT INTO locations VALUES (3, 'Venue C', '2023-10-05', '2023-10-12');
INSERT INTO locations VALUES (4, 'Venue D', '2023-10-08', NULL);
INSERT INTO locations VALUES (5, 'Venue E', '2023-10-25', NULL);
INSERT INTO locations VALUES (6, 'Venue F', '2023-10-21', '2023-10-25');
INSERT INTO locations VALUES (7, 'Venue G', '2023-10-30', '2023-11-05');
INSERT INTO locations VALUES (8, 'Venue H', '2023-11-01', '2023-11-10');
INSERT INTO locations VALUES (9, 'Venue I', '2023-11-15', NULL);
INSERT INTO locations VALUES (10, 'Venue J', '2023-12-01', '2023-12-05');
INSERT INTO locations VALUES (11, 'Venue K', '2023-12-10', '2023-12-20');
INSERT INTO locations VALUES (12, 'Venue L', '2023-12-15', NULL);

需求 #

需要编写SQL查询，获取指定日期区间内的可用场地。例如查询2023-10-21至2023-11-20的可用场地，预期结果如下：

解决方案 #

场地可用的核心逻辑是：该场地的占用时间段与查询目标区间完全不重叠，具体分为两类情况：

1. 场地占用已结束（EndDate不为空），且结束日期早于查询区间的开始日期；
2. 场地占用尚未开始，且开始日期晚于查询区间的结束日期（包括EndDate为空的持续占用场地，只要其开始日期在查询区间之后）。

适配Access MDB的SQL语法，查询语句如下：

SELECT Location
FROM locations
WHERE 
  -- 占用已结束，且在查询区间开始前结束
  (EndDate IS NOT NULL AND EndDate < #2023-10-21#)
  OR
  -- 占用未开始，且在查询区间结束后开始
  (StartDate > #2023-11-20#);

逻辑说明 #

• Access中日期常量需用#包裹，这是与其他数据库的语法差异点；
• 语句会自动排除所有与查询区间有重叠的场地（包括区间内正在占用、区间前开始但持续到区间内、区间内开始的场地）；
• 剩余结果即为完全不与查询区间重叠的可用场地，与预期输出一致。

内容的提问来源于stack exchange，提问作者davor.geci