SELECT *
FROM `people`
WHERE `Cars` BETWEEN '0' AND '1'
AND `Name` LIKE ('%George%')
OR `Address` LIKE ('%George%')
OR `City` LIKE ('%George%')
ORDER BY Favorite DESC
目前,它返回2 cars
的人,如果他们的address
George
其中或他们的city
Georgetown
。
我怎样才能让它只返回那些0
过1
车并且他们的名字是乔治或城镇是乔治的人?
所以我认为我用错了OR
语句?但是,我很难改进该声明。任何建议都会很可爱。
您需要
在where
子句中使用parenthesis
来正确过滤行。
在您的查询中,即使Cars
不在 0
和 1
之间,如果Address LIKE ('%George%')
,也会返回该行and
因为运算符仅在 name
和 cars
之间应用。
为避免这种情况,只需使用运算符将parenthesis
添加到列OR
即可。试试这个。
SELECT *
FROM `people`
WHERE `Cars` BETWEEN '0' AND '1'
AND (`Name` LIKE ('%George%')
OR `Address` LIKE ('%George%')
OR `City` LIKE ('%George%'))
ORDER BY Favorite DESC
您遇到的问题是and
的优先级高于or
。
您的查询被解释为:
SELECT *
FROM `people`
WHERE (
`Cars` BETWEEN '0' AND '1'
AND `Name` LIKE ('%George%')
)
OR `Address` LIKE ('%George%')
OR `City` LIKE ('%George%'))
ORDER BY Favorite DESC
最好在括号内使用 or
子句编写查询:
SELECT *
FROM `people`
WHERE `Cars` BETWEEN '0' AND '1'
AND (
`Name` LIKE ('%George%')
OR `Address` LIKE ('%George%')
OR `City` LIKE ('%George%')
)
ORDER BY Favorite DESC
将
OR 语句分组()
,以便其中一个语句必须为真:
SELECT *
FROM `people`
WHERE `Cars` BETWEEN '0' AND '1'
AND (
`Name` LIKE ('%George%') OR
`Address` LIKE ('%George%') OR
`City` LIKE ('%George%')
)
ORDER BY Favorite DESC
显然不是。 的汽车不能是分数,所以应该使用in来检查0或1
SELECT *
FROM `people`
WHERE `Cars` in(0,1)
AND (`Name` LIKE ('%George%')
OR `Address` LIKE ('%George%')
OR `City` LIKE ('%George%'))
ORDER BY Favorite DESC
尝试添加"()":
SELECT *
FROM `people`
WHERE `Cars` BETWEEN '0' AND '1'
AND (`Name` LIKE ('%George%')
OR `Address` LIKE ('%George%')
OR `City` LIKE ('%George%') )
ORDER BY Favorite DESC
如果你愿意,你只能按一列获取,以后可以添加更多数据。我的意思是添加名为"搜索"的新列并抛出您想要的所有数据并仅按此搜索。
SELECT * FROM `people`
WHERE `Cars` BETWEEN '0' AND '1'
AND `search` LIKE ('%George%')
ORDER BY Favorite DESC
请记住,如果某些内容已更新,则还需要使用新数据更新"搜索"值。