如何修复 MySQL select 语句以获得正确的结果

发布于 2024-10-20 07:26:21 字数 434 浏览 1 评论 0原文

如何为下面的 select 语句获得正确的结果

SELECT * FROM ads 
WHERE ad_status='1' 
AND ad_region='Location One' OR ad_region='Location Two' OR ad_region='Location Three' 
AND ad_type='For Rent' 
ORDER BY ad_id 
DESC LIMIT 10 

此语句将返回所有 ad_type(For Rent、Wanted、For Sale 等),但我想要 For Rent仅有的。

我认为问题出在 ad_region 上,因为有 OR。如果没有“OR”(单个位置),结果是正确的。

让我知道。

How to get a correct result for my select statement below

SELECT * FROM ads 
WHERE ad_status='1' 
AND ad_region='Location One' OR ad_region='Location Two' OR ad_region='Location Three' 
AND ad_type='For Rent' 
ORDER BY ad_id 
DESC LIMIT 10 

This statement will return to all of ad_type (For Rent, Wanted, For Sale, etc), but I want For Rent only.

I thought the problem is on ad_region because have OR. If without 'OR' (single location) the result is correct.

Let me know.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(4

昵称有卵用 2024-10-27 07:26:21

您需要将 OR 条件放入括号中或使用 IN

SELECT * FROM ads 
WHERE 
    ad_status='1' 
  AND 
    ad_region IN ('Location One', 'Location Two', 'Location Three')     
  AND 
    ad_type='For Rent' 
ORDER BY ad_id 
DESC LIMIT 10 

You need to put OR condition into brackets or use IN:

SELECT * FROM ads 
WHERE 
    ad_status='1' 
  AND 
    ad_region IN ('Location One', 'Location Two', 'Location Three')     
  AND 
    ad_type='For Rent' 
ORDER BY ad_id 
DESC LIMIT 10 
灯角 2024-10-27 07:26:21

尝试将 OR 部分加上括号:

SELECT * FROM ads 
WHERE ad_status='1' 
AND ( ad_region='Location One' OR
      ad_region='Location Two' OR
      ad_region='Location Three' )
AND ad_type='For Rent' 
ORDER BY ad_id 
DESC LIMIT 10 

AND 的优先级高于 OR,如图 此处 因此,您的问题中的内容相当于:

SELECT * FROM ads 
WHERE (ad_status='1' AND ad_region='Location One')
OR ad_region='Location Two'
OR (ad_region='Location Three' AND ad_type='For Rent')
ORDER BY ad_id 
DESC LIMIT 10 

这将为您提供位置 1 的所有内容,广告状态为1,加上位置 2 的所有内容以及位置 3 的所有租赁广告。

Try parenthesizing your OR section:

SELECT * FROM ads 
WHERE ad_status='1' 
AND ( ad_region='Location One' OR
      ad_region='Location Two' OR
      ad_region='Location Three' )
AND ad_type='For Rent' 
ORDER BY ad_id 
DESC LIMIT 10 

AND has a higher precedence than OR as shown here so what you have in your question is equivalent to:

SELECT * FROM ads 
WHERE (ad_status='1' AND ad_region='Location One')
OR ad_region='Location Two'
OR (ad_region='Location Three' AND ad_type='For Rent')
ORDER BY ad_id 
DESC LIMIT 10 

which will give you everything from location 1 with an ad status of 1, plus everything from location 2 plus all rental ads from location 3.

猥琐帝 2024-10-27 07:26:21

你的逻辑并不完全清楚。您需要将逻辑从属子句括在括号中,以便让 MySQL 知道您真正想要什么。

你想要

ad_status='1'
    AND (ad_region='Location One' OR ad_region='Location Two' OR ad_region='Location Three')
    AND ad_type='For Rent'

如果这样做,您最好使用 IN(),如下所示:

SELECT * FROM ads 
WHERE ad_status='1'
    AND ad_region IN ('Location One', 'Location Two', 'Location Three')
    AND ad_type='For Rent' 
ORDER BY ad_id 
DESC LIMIT 10
;

your logic is not entirely clear. You need to enclose the logically subordinate clauses in parentheses in order to let MySQL know what you actually want.

Do you want

ad_status='1'
    AND (ad_region='Location One' OR ad_region='Location Two' OR ad_region='Location Three')
    AND ad_type='For Rent'

?

If you do, you're probably better off using IN(), like this:

SELECT * FROM ads 
WHERE ad_status='1'
    AND ad_region IN ('Location One', 'Location Two', 'Location Three')
    AND ad_type='For Rent' 
ORDER BY ad_id 
DESC LIMIT 10
;
赴月观长安 2024-10-27 07:26:21

只需在第三行周围加上括号,即

SELECT * FROM ads 
WHERE ad_status='1' 
AND (ad_region='Location One' OR ad_region='Location Two' OR ad_region='Location Three')
AND ad_type='For Rent' 
ORDER BY ad_id 
DESC LIMIT 10 

您也可以使用“in”,但我尝试远离这些,因为它们通常很慢。

Just put brackets around your 3rd line, i.e.

SELECT * FROM ads 
WHERE ad_status='1' 
AND (ad_region='Location One' OR ad_region='Location Two' OR ad_region='Location Three')
AND ad_type='For Rent' 
ORDER BY ad_id 
DESC LIMIT 10 

You could also use an "in" but I try stay away from these since they are generally quite slow.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文