MySQL 带有多个where语句的sql查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8645773/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
sql query with multiple where statements
提问by user1117774
I am having a rather, for me, complicated mysql query on which I am totally stuck and cannot find any answer for online.
我有一个相当复杂的 mysql 查询，我完全陷入困境，无法在线找到任何答案。
Here's my query:
这是我的查询：
SELECT
items.*
FROM
items
INNER JOIN
items_meta_data
WHERE
(
(meta_key = 'lat' AND meta_value >= '55')
OR
(meta_key = 'lat' AND meta_value <= '65')
)
AND
(
(meta_key = 'long' AND meta_value >= '20')
OR
(meta_key = 'long' AND meta_value <= '30')
)
GROUP BY
item_id
Of course I have tested the query with only 1 statement and that works fine. So if I only pass the long or lat part then I get results. Only when I try to stitch them together I get different results.
当然，我只用 1 条语句测试了查询，效果很好。所以如果我只通过 long 或 lat 部分，那么我就会得到结果。只有当我尝试将它们拼接在一起时，才会得到不同的结果。
Thanks for the help in advance!
我在这里先向您的帮助表示感谢！
The table structure is as follows:
表结构如下：
Table items: ID item_name item_description
表项：ID item_name item_description
Table meta: meta_id item_id meta_key meta_value
表元：meta_id item_id meta_key meta_value
Solution
解决方案
To whoever is interested I finally managed to tackle this problem. Thank you all for your help and insides.
对于感兴趣的人，我终于设法解决了这个问题。感谢大家的帮助和内幕。
SELECT
SQL_CALC_FOUND_ROWS items.*
FROM
items
INNER JOIN
items_meta ON (items.ID = items_meta.post_id)
INNER JOIN
items_meta AS m1 ON (items.ID = m1.post_id)
WHERE
1=1
AND
items.post_type = 'post'
AND
(items.post_status = 'publish')
AND
( (items_meta.meta_key = 'lat' AND CAST(items_meta.meta_value AS SIGNED) BETWEEN '55' AND '65')
AND
(m1.meta_key = 'long' AND CAST(m1.meta_value AS SIGNED) BETWEEN '20' AND '30') )
GROUP BY
items.ID
ORDER BY
items.date
DESC
采纳答案by codeling
You need to consider that GROUP BYhappens after the WHEREclause conditions have been evaluated. And the WHEREclause always considers only one row, meaning that in your query, the meta_keyconditions will always prevent any records from being selected, since one column cannot have multiple values for one row.
您需要考虑在评估子句条件GROUP BY之后会发生这种情况WHERE。并且该WHERE子句始终只考虑一行，这意味着在您的查询中，meta_key条件将始终阻止选择任何记录，因为一列不能有一行的多个值。
And what about the redundant meta_value checks? If a value is allowed to be both smaller and greater than a given value, then its actual value doesn't matter at all - the check can be omitted.
那么冗余的元值检查呢？如果一个值允许小于和大于给定值，那么它的实际值根本无关紧要——可以省略检查。
According to one of your comments you want to check for places less than a certain distance from a given location. To get correct distances, you'd actually have to use some kind of proper distance function (see e.g. this questionfor details). But this SQL should give you an idea how to start:
根据您的评论之一，您想检查距给定位置小于一定距离的地方。要获得正确的距离，您实际上必须使用某种适当的距离函数（有关详细信息，请参见例如此问题）。但是这个 SQL 应该让你知道如何开始：
SELECT items.* FROM items i, meta_data m1, meta_data m2
WHERE i.item_id = m1.item_id and i.item_id = m2.item_id
AND m1.meta_key = 'lat' AND m1.meta_value >= 55 AND m1.meta_value <= 65
AND m2.meta_key = 'lng' AND m2.meta_value >= 20 AND m2.meta_value <= 30
回答by gbn
This..
这个..
(
(meta_key = 'lat' AND meta_value >= '60.23457047672217')
OR
(meta_key = 'lat' AND meta_value <= '60.23457047672217')
)
is the same as
是相同的
(
(meta_key = 'lat')
)
Adding it all together (the same applies to the longfilter) you have this impossible WHERE clause which will give no rows because meta_keycannot be 2 values in one row
把它们加在一起（同样适用于long过滤器）你有这个不可能的 WHERE 子句，它不会给出任何行，因为一行meta_key中不能有 2 个值
WHERE
(meta_key = 'lat' AND meta_key = 'long' )
You need to review your operators to make sure you get the correct logic
您需要检查您的运算符以确保您获得正确的逻辑
回答by David
What is meta_key? Strip out all of the meta_valueconditionals, reduce, and you end up with this:
什么是meta_key？去掉所有的meta_value条件，减少，你最终得到这个：
SELECT
*
FROM
meta_data
WHERE
(
(meta_key = 'lat')
)
AND
(
(meta_key = 'long')
)
GROUP BY
item_id
Since meta_keycan never simultaneously equal two different values, no results will be returned.
由于meta_key永远不能同时等于两个不同的值，因此不会返回任何结果。
Based on comments throughout this question and answers so far, it sounds like you're looking for something more along the lines of this:
根据到目前为止在这个问题和答案中的评论，听起来你正在寻找更多类似的东西：
SELECT
*
FROM
meta_data
WHERE
(
(meta_key = 'lat')
AND
(
(meta_value >= '60.23457047672217')
OR
(meta_value <= '60.23457047672217')
)
)
OR
(
(meta_key = 'long')
AND
(
(meta_value >= '24.879140853881836')
OR
(meta_value <= '24.879140853881836')
)
)
GROUP BY
item_id
Note the ORbetween the top-level conditionals. This is because you want records which are latorlong, since no single record will ever be latandlong.
注意OR顶级条件之间的。这是因为您想要的记录是lat或long，因为没有单个记录是lat和long。
I'm still not sure what you're trying to accomplish by the inner conditionals. Anynon-null value will match those numbers. So maybe you can elaborate on what you're trying to do there. I'm also not sure about the purpose of the GROUP BYclause, but that might be outside the context of this question entirely.
我仍然不确定您要通过内部条件来完成什么。 任何非空值都将匹配这些数字。所以也许你可以详细说明你在那里尝试做什么。我也不确定该GROUP BY条款的目的，但这可能完全超出了这个问题的上下文。
回答by Wes Crow
Can we see the structure of your table? If I am understanding this, then the assumption made by the query is that a record can be only meta_key - 'lat'or meta_key = 'long'not both because each row only has one meta_keycolumn and can only contain 1 corresponding value, not 2. That would explain why you don't get results when you connect the with an AND; it's impossible.
我们可以看看你的表的结构吗？如果我理解这一点，那么查询所做的假设是一条记录只能是meta_key - 'lat'或meta_key = 'long'不是两者，因为每行只有一meta_key列并且只能包含 1 个相应的值，而不是 2。这将解释为什么你没有得到连接AND; 时的结果 不可能。
