如何编写MySQL查询获取指定日期范围内湖泊未被预订的Peg列表

现有表结构 #

• Lakes表：包含字段id、name、lat、lon
• Pegs表：包含字段id、pegNumber、pegName、lake_id
• Reservations表：包含字段id、start_day、end_day、peg_id

原语句问题 #

你当前的查询以Reservations表为驱动表左联Peg、Lakes表，且WHERE条件限定了r.start_day is not null，只能匹配到已产生预约记录的Peg，逻辑方向和需求完全相反。

正确实现逻辑 #

要查询未被预订的Peg，需以Peg表为基础，关联对应湖泊后，左联和查询日期范围重叠的预约记录，最终筛选出没有匹配到预约的Peg即可。
判断预约和查询日期重叠的条件为：预约开始日期 <= 查询结束日期 AND 预约结束日期 >= 查询开始日期，如果是单日查询，将查询起止日期设为同一个值即可。

最终查询语句 #

-- 可替换参数：
-- @target_lake_id：目标查询的湖泊ID，也可替换为l.name = '目标湖泊名称'按名称筛选
-- @query_start_date：查询起始日期，格式如'2024-05-20'
-- @query_end_date：查询结束日期，单日查询时和@query_start_date赋值相同
SELECT 
    l.name AS lake_name,
    p.id AS peg_id,
    p.pegNumber,
    p.pegName
FROM 
    pegs p
INNER JOIN 
    lakes l ON p.lake_id = l.id AND l.id = @target_lake_id
LEFT JOIN 
    reservations r 
    ON p.id = r.peg_id 
    AND r.start_day <= @query_end_date 
    AND r.end_day >= @query_start_date
WHERE 
    r.id IS NULL
ORDER BY 
    p.pegNumber ASC;

逻辑说明 #

1. 先从Peg表出发，关联指定湖泊，过滤出目标湖泊下所有的Peg
2. 左联预约表时，将日期重叠判断写在关联条件中，避免写在WHERE里将左联转为内联
3. 最终r.id IS NULL的过滤条件，会保留所有没有匹配到重叠预约的Peg，也就是目标时间范围内未被预订的Peg

内容的提问来源于stack exchange，提问作者Dawid Sieradzki