请教一个 关联查询 的 条件问题，再线等，急

[1010011010]

这里有三个表：

a 表
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+

b 表
+----+------+------+
| id | a_id | c_id |
+----+------+------+
|  1 |    1 |    1 |
|  2 |    1 |    2 |
|  3 |    2 |    2 |
|  4 |    2 |    3 |
|  5 |    3 |    1 |
|  6 |    3 |    3 |
+----+------+------+

c 表
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+

b 表 关联了 a 表 和 c 表

查询全表时：

SELECT a.id, group_concat(c.id)

FROM a

LEFT JOIN b ON b.a_id = a.id

LEFT JOIN c ON c.id = b.c_id

GROUP BY a.id;

+----+--------------------+
| id | group_concat(c.id) |
+----+--------------------+
|  1 | 1,2                |
|  2 | 2,3                |
|  3 | 1,3                |
+----+--------------------+

然后有条件： where c.id = 2

我想要的：

+----+--------------------+
| id | group_concat(c.id) |
+----+--------------------+
|  1 | 1,2                |
|  2 | 2,3                |
+----+--------------------+

然而结果是：

+----+--------------------+
| id | group_concat(c.id) |
+----+--------------------+
|  1 | 2                  |
|  2 | 2                  |
+----+--------------------+

如何写 查询条件？

源氏大雕

结果：

先倒过来筛选出 符合条件的 a表 再查询

SELECT a1.id, group_concat(c.id)

FROM
(
	SELECT a.id

	FROM a

	LEFT JOIN b ON b.a_id = a.id

	LEFT JOIN c ON c.id = b.c_id

	WHERE c.id = 2
)
AS a1 #筛选出的a表

LEFT JOIN b ON b.a_id = a1.id

LEFT JOIN c ON c.id = b.c_id

GROUP BY a1.id

还有其他答案吗

[noNOno]

b.c_id=2

[1010011010]

@noNOno #1
一样的。

[noNOno]

@1010011010 不连 a 表应该就对了，我在脑补...

[zeraba]

SELECT b.a_id, group_concat(t1.c_id)
from b
left join (select a_id from b where b_id = 2) t1
on t1.a_id = b.a_id
left join c on c.id = b.c_id
没测试 大概是需要子查询 拿到 c.id = 2 之后的 b.a_id 然后再合并

[1010011010]

@zeraba #4

由此扩展得

SELECT a1.id, group_concat(c.id)

FROM
(
SELECT a.id

FROM a

LEFT JOIN b ON b.a_id = a.id

WHERE b.c_id = 2
)
AS a1

LEFT JOIN b ON b.a_id = a1.id

LEFT JOIN c ON c.id = b.c_id

GROUP BY a1.id

先倒过来筛选出 符合条件的 a 表 再查询

结果是对的

有没有更简单的

(つ*⊂)∀ﾟ)

[zeraba]

@1010011010
'''
SELECT
b.a_id,
group_concat(b.c_id)
FROM
(SELECT a_id FROM b WHERE c_id = 2) t1
LEFT JOIN b ON t1.a_id = b.a_id
LEFT JOIN c ON c.id = b.c_id
GROUP BY
b.a_id;

理论上更优？本来要打这个的