MySQL方法GROUP_CONCAT的应用,多对多联表查询,以
2017-11-27 本文已影响0人
李北北
开发中遇到这样的一个需求:用户表为A,小区表为B,用户小区关系通过C表多对多关联,我们需要查询出每一个用户所拥有的小区,以下图的格式显示:
image.png在解决问题中发现了两种方式可实现该功能,下面直接贴出sql语句
方式一
SELECT
m.user_id AS userId,
(
SELECT
GROUP_CONCAT(ca.`name` SEPARATOR '、')
FROM
common_user_apartment ua
LEFT JOIN common_apartment ca ON ca.id = ua.apartment_id
WHERE
ua.user_id = m.user_id
) AS apartmentNames
FROM
basics_users m
LEFT JOIN common_apartment ca ON ca.id = m.apartment_id
LEFT JOIN common_region cr ON cr.id = ca.city_id
WHERE
m.isdel = 'N'
方式一是把查询c表作为主表,并且left join B表的一个子查询,查出每一个用户id拥有的小区名称拼接结果作为拥有小区字段值的,我们看看其查询性能
image.png可见查询一万条左右数据需要17秒左右,这种速度我们显然是不能接受的,而且需要以拥有小区的名称做模糊查询时候也无从下手。于是后来继续想办法优化,就找到了下面的方式二。
方式二
SELECT
m.user_id AS userId,
m.user_name AS userName,
cua.apartmentNames AS apartmentNames
FROM
basics_users m
LEFT JOIN common_apartment ca ON ca.id = m.apartment_id
LEFT JOIN common_region cr ON cr.id = ca.city_id
LEFT JOIN (
SELECT
ua.user_id,
GROUP_CONCAT(
ua.apartment_id SEPARATOR ','
) AS apartmentIds,
GROUP_CONCAT(ca.`name` SEPARATOR '、') AS apartmentNames
FROM
common_user_apartment ua
LEFT JOIN common_apartment ca ON ca.id = ua.apartment_id
GROUP BY
ua.user_id
) cua ON cua.user_id = m.user_id
WHERE
m.isdel = 'N';
方式二依然有一个查询用户拥有小区名称拼接结果的子查询,只是这个子查询不是直接作为结果字段返回,而是根据用户id为group规则查询出来每一个用户的拥有小区结果字符串,然后作为A表的left join的虚拟表,下面看一下测试结果
image.png可见同样查询一万条数据一秒钟都不用,查询速度提高了至少20倍,而且因为是虚拟关联表,可以直接用 cua.apartmentNames LIKE '%小区1%' 而实现模糊查询。