Union all，合并查询结果

Question

我有以下MySQL查询：

SELECT * 
FROM bookings 
WHERE record_id = 7 AND status = 'available' AND startdate >= '2015-05-02' AND startdate <= '2015-05-09'
UNION ALL
SELECT * 
FROM bookings 
WHERE record_id = 7 AND status = 'available' AND startdate >= '2015-05-11' AND startdate <= '2015-05-12'

有没有可能将这两个查询组合起来，而不是使用UNION ALL？

- djmzfKnm

Answer 1

您可以使用以下方式使用OR运算符：

SELECT * 
FROM bookings 
WHERE record_id = 7 
AND status = 'available' 
AND ((startdate >= '2015-05-11' AND startdate <= '2015-05-12') or (startdate >= '2015-05-02' AND startdate <= '2015-05-09'))

- Robert

Answer 2

这应该可以运行：

SELECT * 
FROM bookings 
WHERE record_id = 7 AND status = 'available' AND 
   ((startdate >= '2015-05-02' AND startdate <= '2015-05-09') or (startdate >= '2015-05-11' AND startdate <= '2015-05-12'))

- Jens

Answer 3

您可以使用“OR”运算符来获取两个日期范围：

SELECT * 
FROM   bookings 
WHERE  record_id = 7 AND
       status = 'available' AND 
       ((startdate >= '2015-05-02' AND startdate <= '2015-05-09') OR
        (startdate >= '2015-05-11' AND startdate <= '2015-05-12'))

- Mureinik

Answer 4

Leave this part:

SELECT * FROM bookings WHERE record_id = 7 AND status = 'available'

添加这个：

AND ((startdate >= '2015-05-02' AND startdate <= '2015-05-09') OR (startdate >= '2015-05-11' AND startdate <= '2015-05-12'))

如果第一个和条件或第二个和条件为true，则OR条件将返回true。

对于您正在使用的那些and条件，您可能需要查看SQL BETWEEN语句：http://www.w3schools.com/sql/sql_between.asp

在第一部分之后，您还可以这样做：

AND startDate >= '2015-05-02' AND startDate <= '2015-05-12' AND NOT startDate = '2015-05-10'

由于您已经在使用硬编码日期，并且在该范围内您不需要的是5月10日。

- Jeremy C.