sql:如何正确形成具有如此多的and的where子句

Question

以下内容忽略日期范围。请有人解释一下这里出了什么问题吗？不确定某些语句是否需要括号，但无论如何也不明白为什么。

WHERE
     booking_date BETWEEN '2011-11-01' AND '2011-12-21'
 AND booking_status = 'Confirmed'
  OR booking_status = 'Cancelled'
  OR booking_status = 'Rearranged'
 AND invoice_number=''
ORDER BY booking_date ASC

Answer 1

在 OR 条件两边加上括号:

WHERE
     booking_date BETWEEN '2011-11-01' AND '2011-12-21'
 AND (booking_status = 'Confirmed'
      OR booking_status = 'Cancelled'
      OR booking_status = 'Rearranged')
 AND invoice_number=''
ORDER BY booking_date ASC

您还可以使用 IN 这可能更容易阅读和理解:

WHERE
     booking_date BETWEEN '2011-11-01' AND '2011-12-21'
 AND booking_status IN ('Confirmed', 'Cancelled', 'Rearranged')
 AND invoice_number=''
ORDER BY booking_date ASC