sql进阶 如何利用group_concat, find_in_set, exists解决mysql一对多关联条件查询的问题

现有test1,test2,test3三张表，test2是test1和test3的中间关联表

test1

test2

test3

题目一：查询所有test1,并查询test1关联的所有b_id

SELECT 
t1.id, 
t1.name, 
GROUP_CONCAT(t2.`b_id` ORDER BY t2.id ) AS bids 
FROM test1 t1 
LEFT JOIN test2 t2 ON t2.`a_id` = t1.`id` 
WHERE 1 = 1 GROUP BY t1.`id`

结果

题目二：查询所有test1,并查询test1关联的所有b.name

SELECT 
t1.id, 
t1.name, 
GROUP_CONCAT(t2.`b_id` ORDER BY t2.id)AS bids ,
GROUP_CONCAT(t3.`name` ORDER BY t3.id) AS bnames  
FROM test1 t1 
LEFT JOIN test2 t2 ON t2.`a_id` = t1.`id` 
LEFT JOIN test3 t3 ON t2.`b_id` = t3.`id` 
WHERE 1 = 1 GROUP BY t1.`id` 

结果：

题目三：查询所有跟b1和b2关联过的test1记录。

分析：如上面的图，a2正好满足，但是a1和a4也关联过b1,b2，所有，结果应该是a1,a2,a4三条记录，a3因为仅仅跟b1关联，没有跟b2关联，所以不显示。

方法1：使用group_concat，并利用find_in_set过滤

SELECT 
t1.id, 
t1.name, 
GROUP_CONCAT(t2.`b_id` ORDER BY t2.id ) AS bids 
FROM test1 t1 
LEFT JOIN test2 t2 ON t2.`a_id` = t1.`id` 
WHERE 1 = 1 GROUP BY t1.`id`  
HAVING FIND_IN_SET(1, bids) AND FIND_IN_SET(2, bids);

 

结果：

方法2：使用group_concat和exists

SELECT 
t1.id, 
t1.`name`, 
GROUP_CONCAT(t3.`b_id` ORDER BY t3.id) AS bids 
FROM test1 t1 
LEFT JOIN test2 t3 ON t3.`a_id` = t1.`id` 
WHERE 
EXISTS 
(SELECT 1 FROM test2 t2 WHERE t2.`a_id` = t1.`id` AND t2.`b_id` = 1)
AND 
EXISTS 
(SELECT 1 FROM test2 t2 WHERE t2.`a_id` = t1.`id` AND t2.`b_id` = 2)
GROUP BY t1.`id`

结果：

和方法1结果相同！