复杂的SQL查询，以便在给定的时间框架内检索与预订系统中的房间相匹配的可用床位。

Question

我正在开发一个预订系统，在这个系统中，我需要在给定的时间范围内选择所有可用的床，并将它们与它们所在的房间相匹配。

不知何故，我的查询似乎部分工作，但不幸的是，它返回了一些错误的行。例如，从小提琴中可以看出，由于WHERE NOT (ro.status = 0)子句，不应该返回第1房间中的床，因为第1房间的room_status设置为0(例如，由于清洁而没有可用的房间)。

所使用的表格如下：

床(包含所有床，匹配房间与FK room_id)房间(包含所有房间，房间可以禁用eq。用于清洗room_status字段中的目标。如果房间被禁用，所有相关床位都不可用)预订(包含所有预订) reservation_bed (与预定的床相匹配)。

我的查询如下：

SELECT b.id AS bed_id, b.name AS bed_name, b.room_id AS room_id, ro.name AS room_name, ro.status AS room_status
FROM bed b
LEFT JOIN room ro ON ro.id = b.id
WHERE NOT (ro.status = 0) AND b.id NOT IN(SELECT rb.bed_id FROM reservation_bed rb
LEFT JOIN reservation r ON r.id = rb.reservation_id
WHERE r.end BETWEEN str_to_date('$start', '%d.%m.%Y') AND str_to_date('$end', '%d.%m.%Y') OR
  str_to_date('$start', '%d.%m.%Y') BETWEEN r.start AND r.end
ORDER BY b.room_id

SQLFiddle这里http://sqlfiddle.com/#!9/cca72/4

提前谢谢你

Answer 1

我想你的问题是日期比较。您正在将日期作为字符串进行比较。虽然您不应该这样做，但如果格式是YYYY DD，则是可以的.相反，使用日期变量正确地比较日期：

WHERE r.end BETWEEN str_to_date('$start', '%d.%m.%Y') AND str_to_date('$end', '%d.%m.%Y') OR
      str_to_date('$start', '%d.%m.%Y') BETWEEN r.start AND r.end

实际上，您应该插入参数，而不是将字符串插入到查询中，您可以为这些参数提供一种日期类型--而无需在查询本身中进行类型转换。(而且，这是避免SQL注入和防止数据库中运行时错误的良好做法。)

您的查询也可能有其他问题。

Answer 2

发现了问题严重的错误..。

我做到了

LEFT JOIN room ro ON ro.id = b.id 

但这应该是

LEFT JOIN room ro ON ro.id = b.room_id