MySQL方法GROUP_CONCAT的应用，多对多联表查询，以A表为主表，通过关联表C查询出B表关联A表任意记录的多条记录的某个字段的合并值

开发中遇到这样的一个需求：用户表为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%' 而实现模糊查询。

最后编辑于：2017.11.27 17:51:09