MYSQL join results set wiped results during IN () in where clause?

Question

大量编辑!

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

那么，修改后的问题:MySQL 中是否有任何东西可以循环遍历来自 WHERE 子句的连接的一列结果？

这是我正在使用的 super 简化代码，从复杂的 crm 搜索功能中剥离出来。左连接和一般想法是该查询的遗物。因此，对于此查询，它必须是排他性搜索 - 查找具有所有指定标签的人，而不仅仅是任何一个。

首先是数据库

表 1:人物

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

表2:标签

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

漂亮又简单。所以，很自然地:

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 ...) 因为这在更广泛的查询中不起作用(有一个单独的标签继承缓存和大量其他内容)。

这是我的原始示例查询 - 基于 IN() 会一次遍历所有行的错误想法。

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)

这是我最新的失败尝试，旨在说明我的目标...

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() 将视为列表的表达式列表？

本质上，我试图让 IN() 在类似于数组或动态表达式列表的对象上迭代循环，其中包含来自联接的所有数据行。

Answer 1

从逻辑上考虑您的代码在做什么:

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

相当于

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

tag.tag_id 不可能同时满足这两个条件，因此 AND 永远不会为真。

看起来您在问题中引用的 OR 版本是您真正想要的版本:

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 子句，您可以将其写为:

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 条件的一部分:

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