mysql - SQL 获取比较聚合函数的结果

Question

我有一个如下所示的表格:

code int, primary key
reservation_code int,
indate date,
outdate date,
slot int,
num int,

该数据库的设计有点奇怪，它的工作方式是该表保留每个时段预订的日期，num 用于跟踪连续的预订，我认为这是遗留原因。

我正在尝试提出一个查询来检查数据库中的先前预订。我的想法是:

对于给定的槽号，检查是否存在一组具有相同reservation_code的行，并且该组中具有最小num值的行具有小于或等于给定日期和过时日期的indate日期具有最大 num 值的行高于同一给定日期。

我在 SQL 中最接近的方法:

编辑:在 Barmar 的帮助下

SELECT b.reservation_code
FROM bookings b
JOIN (SELECT reservation_code, MIN(num) minnum
      FROM bookings
      WHERE slot = "given_slot"
      AND indate <= "given_date"
      GROUP BY reservation_code) min
ON minnum = num and b.reservation_code = min.reservation_code
JOIN (SELECT reservation_code, MAX(num) maxnum
      FROM bookings
      WHERE slot = "given_slot"
      AND outdate > "given_date"
      GROUP BY reservation_code) max
ON maxnum = num and b.reservation_code = max.reservation_code
WHERE slot="given_slot"
AND indate <= "given_date"
AND outdate > "given_date"
GROUP BY b.reservation_code

向两个子查询添加 GROUP BY 使其适用于大多数情况，但第二次检查仍然返回错误的答案。

以下是一些示例行和查询，旨在使问题更加清晰:

示例行:

code    reservation_code    indate      outdate     slot    num
1       1                   01/01/13    03/01/13    1       0
2       1                   03/01/13    05/01/13    1       1
3       1                   05/01/13    10/01/13    1       2
4       2                   04/01/13    15/01/13    2       0
5       2                   15/01/13    19/01/13    2       1
6       3                   11/01/13    13/01/13    1       0
7       4                   15/01/13    16/01/13    3       0
8       5                   01/01/13    15/01/13    3       0
9       5                   15/01/13    25/01/13    4       1

样本检查:

slot 2, date 21/02/13, should return not booked.
slot 2, date 16/01/13, should return booked
slot 1, date 14/01/13, should return not booked
slot 1, date 12/01/13, should return booked
slot 1, date 10/01/13, should return not booked
slot 3, date 02/01/13, should return booked
slot 4, date 15/01/13, should return booked
slot 4, date 25/01/13, should return not booked

Answer 1

您需要对聚合表使用 JOIN

SELECT b.reservation_code, count(1)
FROM bookings b
JOIN (SELECT reservation_code, MAX(num) maxnum
      FROM bookings
      WHERE slot = "given slot"
      AND indate <= "given date"
      GROUP BY reservation_code) m
ON maxnum = num and b.reservation_code = m.reservation_code
WHERE slot="given slot"
AND indate <= "given date"
GROUP BY b.reservation_code