这个问题从
MYSQL join results set wiped results during IN () in where clause?开始
所以,短版的问题.如何将GROUP_CONCAT返回的字符串转换为逗号分隔的表达式列表,IN()将被视为要循环的多个项目的列表?
注: MySQL文档看起来是将IN()用作“表达式列表”的“(逗号,分隔的列表)”,有趣的是,IN()中的页面似乎或多或少是MySQL文档中的唯一页面曾经提到表达式列表.所以我不知道用于制作数组或临时表的函数是否可以在这里使用.
长基于示例的版本的问题:从这样的2表数据库:
SELECT id, name, GROUP_CONCAT(tag_id) FROM person INNER JOIN tag ON person.id = tag.person_id GROUP BY person.id;
+----+------+----------------------+
| id | name | GROUP_CONCAT(tag_id) |
+----+------+----------------------+
| 1 | Bob | 1,2 |
| 2 | Jill | 2,3 |
+----+------+----------------------+
我怎么能这样做,因为它使用一个字符串被视为逻辑等价于(1 = X)AND(2 = X)…
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.01 sec)
…到GROUP_CONCAT结果被视为一个列表的东西,所以对于Bob,它将相当于:
SELECT name, GROUP_CONCAT(tag.tag_id) FROM person INNER JOIN tag ON person.id = tag.person_id AND person.id = 1
GROUP BY person.id HAVING ( ( 1 IN (1,2) ) AND ( 2 IN (1,2) ) );
+------+--------------------------+
| name | GROUP_CONCAT(tag.tag_id) |
+------+--------------------------+
| Bob | 1,2 |
+------+--------------------------+
1 row in set (0.00 sec)
…和吉尔,这将相当于:
SELECT name, GROUP_CONCAT(tag.tag_id) FROM person INNER JOIN tag ON person.id = tag.person_id AND person.id = 2
GROUP BY person.id HAVING ( ( 1 IN (2,3) ) AND ( 2 IN (2,3) ) );
Empty set (0.00 sec)
…所以整体结果将是排他性搜索条款,要求所有列出的标签不使用HAVING COUNT(DISTINCT …)?
(注意:该逻辑在没有AND的情况下工作,适用于字符串的第一个字符.
SELECT name, GROUP_CONCAT(tag.tag_id) FROM person LEFT JOIN tag ON person.id = tag.person_id
GROUP BY person.id HAVING ( ( 2 IN (GROUP_CONCAT(tag.tag_id) ) ) );
+------+--------------------------+
| name | GROUP_CONCAT(tag.tag_id) |
+------+--------------------------+
| Jill | 2,3 |
+------+--------------------------+
1 row in set (0.00 sec)