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

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                  |
+----+--------------------+

如何写查询条件？

源氏大雕

Supplement 1 · Mar 7, 2017

结果：

先倒过来筛选出符合条件的 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

还有其他答案吗

6 replies • 2017-03-06 20:50:49 +08:00

noNOno

Mar 6, 2017

b.c_id=2

1010011010

Mar 6, 2017

@noNOno #1
一样的。

noNOno

Mar 6, 2017

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

zeraba

Mar 6, 2017 via Android

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

Mar 6, 2017

@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

Mar 6, 2017

@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;

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

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

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