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

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

如何写查询条件？

源氏大雕

第 1 条附言 2017-03-07 18:12:51 +08:00

结果：

先倒过来筛选出符合条件的 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 条回复 2017-03-06 20:50:49 +08:00

noNOno

2017-03-06 18:22:04 +08:00

b.c_id=2

1010011010

2017-03-06 18:22:39 +08:00

@noNOno #1
一样的。

noNOno

2017-03-06 18:28:32 +08:00

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

zeraba

2017-03-06 18:55:42 +08:00 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

2017-03-06 19:57:49 +08:00

@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

2017-03-06 20:50:49 +08:00

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

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

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

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