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%' 而实现模糊查询。

上一篇下一篇

猜你喜欢

热点阅读