IN 子查询中的 GROUP_CONCAT [英] GROUP_CONCAT in IN Subquery

问题描述

SELECT  A.id, A.title, 
FROM (`table`) as A
WHERE A.active = '1'
AND A.id IN (SELECT GROUP_CONCAT(B.id) from B where user = 3)

如果我启动子查询 SELECT GROUP_CONCAT(B.id) from B where user = 3 仅，我获得 1,2,3,4.但是，如果我启动整个查询，我只会获得一行.

If i launch subquery SELECT GROUP_CONCAT(B.id) from B where user = 3 only, i obtain 1,2,3,4. But if i launch entire query i obtain only one row.

但是如果我尝试用它的值 (1,2,3,4) 替换子查询

But if i try to substitute the subquery with its value (1,2,3,4)

SELECT  A.id, A.title, 
FROM (`table`) as A
WHERE A.active = '1'
AND A.id IN (1,2,3,4)

我根据需要获得了 4 行....

i obtain the 4 rows ... as i need.

我的错误在哪里?

推荐答案

MySQL 看到子查询只返回单个字段/行，因此将其视为类似:

MySQL is seeing the subquery return only a single field/row, and therefore treats it as something like:

... and A.id IN ('1,2,3,4')

归结为 A.id = '1,2,3,4'.

对于in"查询，不需要 group_concat 的东西，只需:

For an 'in' query, there's no need for the group_concat stuff, simply do:

... and A.id IN (select B.id FROM b where user = 3)

这篇关于IN 子查询中的 GROUP_CONCAT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！