mysql - 查询获取城市名称的 3 个变体

Question

SELECT * 
FROM orders
WHERE date_ord >=  '2015-02-16'
AND date_ord <=  '2015-02-26'
AND active =1
AND (
(
ship_city =  'NYC'
OR ship_city =  'New York'
OR ship_city =  'New York City'
)
AND ship_state =  'NY'
)
OR (
(
bill_city =  'NYC'
OR bill_city =  'New York'
OR bill_city =  'New York City'
)
AND ship_state =  ''
)
ORDER BY ordId

客户添加了 3 种变体的“纽约”城市名称，例如“NYC”、“New York”和“New York City”。我需要查询所有帐单城市为所有 3 个变体且运送状态为空或 NULL 的记录，以及运送城市为纽约且使用城市名称的所有 3 个变体且运送状态设置为“NY”的所有记录。简而言之，所有订单均运往纽约市。 (账单州可以是 CA 或 FL)

此查询并不完美。我收到的记录早于日期范围。请建议修复此问题。

根据@Ash和@DovesandChicks的建议，我尝试使用IN函数来做到这一点

SELECT * 
FROM orders
WHERE date_ord >=  '2015-02-16'
AND date_ord <=  '2015-02-26'
AND active =1
AND ship_city
IN ('NYC',  'New York',  'New York City')
OR (bill_city IN ('NYC',  'New York',  'New York City') AND ship_state =  'Unlisted')
ORDER BY ordId

查询仍然有问题。请帮忙

Answer 1

1)你可以使用
AND Ship_city IN ('NYC', '纽约', '纽约市')2) OR 子句周围没有足够的括号。您需要将所有 OR 放在一个括号中

所以

WHERE(日期位)AND(带有 OR 的城市位)

如果你需要我可以把它全部写出来:

    SELECT * FROM tblorders 
WHERE dateord >= '2015-02-16' 
AND dateord <= '2015-02-26' 
AND activeFlag =1 
AND (s_city IN ( 'NYC', 'New York', 'New York City' ) OR ( b_city IN ( 'NYC', 'New York', 'New York City' ) AND s_state = 'Unlisted' )) 
ORDER BY ordId