MYSQL连接结果集在where子句中的IN()期间擦除了结果? [英] MYSQL join results set wiped results during IN () in where clause?

问题描述

进行大量编辑！

最初的问题是基于对IN()如何处理联接结果集中的列的误解.我以为IN(some_join.some_column)会将结果列视为列表，并遍历每行.事实证明，它仅查看第一行.

The original question was based on a misunderstanding of how IN() treats a column from a results set from a join. I thought IN( some_join.some_column ) would treat a results column as a list and loop through each row in place. It turns out it only looks at the first row.

那么，经过改编的问题是:MySQL中有什么东西可以循环遍历WHERE子句中的联接的结果列?

So, the adapted question: Is there anything in MySQL that can loop through a column of results from a join from a WHERE clause?

这是我正在使用的超级简化代码，从复杂的crm搜索功能中剔除了.左联接和一般思想是该查询的结果.因此，对于此查询，它必须是排他性搜索-查找具有所有指定标签的人，而不仅仅是所有标签.

Here's the super-simplified code I'm working with, stripped down from a complex crm search function. The left join and general idea are relics from that query. So for this query, it has to be an exclusive search - finding people with ALL specified tags, not just any.

首先是数据库

表1:人

+----+------+
| id | name |
+----+------+
|  1 | Bob  |
|  2 | Jill |
+----+------+

表2:标记

+-----------+--------+
| person_id | tag_id |
+-----------+--------+
|         1 |      1 |
|         1 |      2 |
|         2 |      2 |
|         2 |      3 |
+-----------+--------+

又好又简单.所以，自然地:

Nice and simple. So, naturally:

SELECT name, GROUP_CONCAT(tag.tag_id) FROM person LEFT JOIN tag ON person.id = tag.person_id GROUP BY name;
+------+--------------------------+
| name | GROUP_CONCAT(tag.tag_id) |
+------+--------------------------+
| Bob  | 1,2                      |
| Jill | 2,3                      |
+------+--------------------------+

到目前为止，一切都很好.所以我要寻找的是在第一种情况下只能找到Bob，在第二种情况下只能找到Jill的方法-不使用HAVING COUNT(DISTINCT ...)，因为这在更广泛的查询中不起作用(有单独的标签)继承缓存和大量其他内容.

So far so good. So what I'm looking for is something that would find only Bob in the first case and only Jill in the second - without using HAVING COUNT(DISTINCT ...) because that doesn't work in the broader query (there's a seperate tags inheritance cache and a ton of other stuff).

这是我最初的示例查询-基于错误的想法，即IN()会一次遍历所有行.

Here's my original sample queries - based on the false idea that IN() would loop through all rows at once.

SELECT DISTINCT name FROM person LEFT JOIN tag ON person.id = tag.person_id 
  WHERE ( ( 1 IN (tag.tag_id) ) AND ( 2 IN (tag.tag_id) ) );                            
Empty set (0.00 sec)

SELECT DISTINCT name FROM person LEFT JOIN tag ON person.id = tag.person_id 
  WHERE ( ( 2 IN (tag.tag_id) ) AND ( 3 IN (tag.tag_id) ) );
Empty set (0.00 sec)

这是我最新的一次失败尝试，目的是让我了解我的目标...

Here's my new latest failed attempt to give an idea of what I'm aiming for...

SELECT name, GROUP_CONCAT(tag.tag_id) FROM person LEFT JOIN tag ON person.id = tag.person_id 
  GROUP BY person.id HAVING ( ( 1 IN (GROUP_CONCAT(tag.tag_id) ) ) ) AND ( 2 IN (GROUP_CONCAT(tag.tag_id)) );
Empty set (0.00 sec)

因此，似乎要使用1,2或2,3的GROUP_CONCAT字符串，并将其视为单个实体而不是表达式列表.有没有办法将分组的列转换为IN()或= ANY()将被视为列表的表达式列表?

So it seems it's taking a GROUP_CONCAT string, of either 1,2 or 2,3, and is treating it as a single entity rather than an expression list. Is there any way to turn a grouped column into an expression list that IN () or =ANY() will treat as a list?

从本质上讲，我正在尝试使IN()在类似于数组或动态表达式列表的对象上迭代循环，该列表或数组包含来自联接的所有数据行.

Essentially, I'm trying to make IN() loop iteratively over something that resembles an array or a dynamic expression list, which contains all the rows of data that come from a join.

推荐答案

思考代码在逻辑上的作用:

Think about what your code is doing logically:

( 1 IN (tag.tag_id) ) AND ( 2 IN (tag.tag_id) )

等同于

( 1 = (tag.tag_id) ) AND (2 = (tag.tag_id) )

tag.tag_id不可能同时满足两个条件，因此AND永远都不成立.

There's no way tag.tag_id can satisfy both conditions at the same time, so the AND is never true.

您在问题中引用的OR版本似乎是您真正想要的版本:

It looks like the OR version you cited in your question is the one you really want:

SELECT DISTINCT name FROM person LEFT JOIN tag ON person.id = tag.person_id 
  WHERE ( ( 1 IN (tag.tag_id) ) OR ( 2 IN (tag.tag_id) ) );   

更恰当地使用IN子句，您可以将其写为:

Using the IN clause more appropriately, you could write that as:

SELECT DISTINCT name FROM person LEFT JOIN tag ON person.id = tag.person_id 
  WHERE tag.tag_id in (1,2);   

最后一点要注意，因为您要引用WHERE子句(tag.tag_id)中的LEFT JOINed表中的一列，所以实际上是在强迫它像INNER JOIN.要真正获得LEFT JOIN，您需要将条件从WHERE中移出，并使其成为JOIN条件的一部分:

One final note, because you're referencing a column from the LEFT JOINed table in your WHERE clause (tag.tag_id), you're really forcing that to behave like an INNER JOIN. To truly get a LEFT JOIN, you'd need to move the criteria out of the WHERE and make it part of the JOIN conditions instead:

SELECT DISTINCT name FROM person LEFT JOIN tag ON person.id = tag.person_id 
  AND tag.tag_id in (1,2);   

这篇关于MYSQL连接结果集在where子句中的IN()期间擦除了结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！