mysql中"WHERE"子句中的倍数"IN"表达式的求值 [英] Evaluation of multiples 'IN' Expressions in 'WHERE' clauses in mysql

问题描述

根据@Cesar的请求进行更新.希望我了解您想要的内容，如果没有，请还原.夸斯诺伊.

Updating by @Cesar's request. Hope I understood what you want, if not, please revert. Quassnoi.

如果我这样查询SQL:SELECT * FROM TABLE_NAME WHERE b IN (2, 7) AND c IN (3, 9)，我可以假设MySQL仅匹配每个列表中具有相同编号的元素对吗?

If I make an SQL query like this: SELECT * FROM TABLE_NAME WHERE b IN (2, 7) AND c IN (3, 9), can I assume that MySQL will match only pairs from elements with same number in each list?

即(2, 3)，(7, 9)，...?

例如，假设我们有一个这样的表:

For example, suppose we have a table like this:

 +----------+----------+----------+
 |    PK    |     b    |     c    |
 +----------+----------+----------+
 |     1    |     2    |     3    |
 +----------+----------+----------+
 |     2    |     5    |     4    |
 +----------+----------+----------+
 |     3    |     7    |     9    |
 +----------+----------+----------+
 |     4    |     7    |     4    |
 +----------+----------+----------+
 |     5    |     2    |     9    |
 +----------+----------+----------+

假设返回的仅 行是1和3(而不是5)是否正确?

Is it correct to assume that the only rows returned are 1 and 3 (and not 5)?

推荐答案

SELECT * FROM TABLE_NAME WHERE b IN(5,7) AND c IN(4,4)

此查询将返回行，其中b是5或7，而c是4.

This query will return rows, where b is either 5 or 7, AND c is 4.

成对评估"是什么意思?

What do you mean by "evaluation in pairs?"

更新:

我将在示例中再添加一行:

I'll add one more row to the sample:

 +----------+----------+----------+
 |    PK    |     b    |     c    |
 +----------+----------+----------+
 |     1    |     2    |     3    |
 +----------+----------+----------+
 |     2    |     5    |     4    |
 +----------+----------+----------+
 |     3    |     7    |     9    |
 +----------+----------+----------+
 |     4    |     7    |     4    |
 +----------+----------+----------+
 |     5    |     2    |     9    |
 +----------+----------+----------+

如果要匹配整个集合，可以使用以下语法:

If you want to match the whole sets, you can use this syntax:

SELECT  *
FROM    table_name
WHERE   (b, c) IN ((2, 3), (7, 9))

这意味着:同时返回b是2和c是3或b是7和с是9的所有行时间."

This means: "return all rows where b is 2 and c is 3 at the same time, OR b is 7 and с is 9 at the same time."

在上面的示例中，此查询将返回1和3

In the example above, this query will return rows 1 and 3

但是，如果您以其他方式重写此查询，例如:

But if you rewrite this query the other way around, like this:

SELECT  *
FROM    table_name
WHERE   b IN (2, 7)
        AND c IN (3, 9)

，这意味着返回b为2或7，而c为3或9的所有行.)

, this will mean "return all rows where b is either 2 or 7, AND c is either 3 or 9).

这将返回行1，3和5，因为行5满足第二个查询的条件，但不满足第一个查询的条件.

This will return rows 1, 3 and 5, since row 5 satisfies the condition for the second query but not for the first one.

这篇关于mysql中"WHERE"子句中的倍数"IN"表达式的求值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！