带有连接和多个联合的 mySQL 查询

Question

我有一个连接两个满足条件的表的查询:

SELECT * FROM order
JOIN reservations
ON order.reservationsID=reservations.ID
WHERE posname='Fixed'

根据这个结果，我需要从预订表中筛选出特定的日期范围。如果我单独查询预订表(有年、月、 block 列)，我将有以下查询:

(SELECT * FROM reservations 
WHERE YEAR=2016 AND MONTH=11 AND BLOCK>1)
UNION
(SELECT * FROM reservations 
WHERE YEAR=2016 AND MONTH>11)
UNION
(SELECT * FROM reservations 
WHERE YEAR>2016)

如何将这两个查询合并为一个？任何指导将不胜感激。

Answer 1

试试这个:

SELECT * FROM [order]
JOIN reservations ON [order].reservationsID=reservations.ID
WHERE posname='Fixed' AND ( (YEAR=2016 AND MONTH=11 AND BLOCK>1) OR
                            (YEAR=2016 AND MONTH>11) OR
                            (YEAR>2016) )