mysql GROUP_CONCAT() 与 find_in_set() 的使用

mysql GROUP_CONCAT() 与 find_in_set() 的使用

GROUP_CONCAT() 函数 , 多与 group by 连用

https://blog.csdn.net/u012620150/article/details/81945004

find_in_set() 函数
https://www.fujieace.com/mysql/functions/find_in_set.html

项目中的应用 场景 sys_user, 用户表, sys_role_user 用户角色中间表.
一个用户会有多个角色, 现需在用户分页查询sql中,进行用户角色的筛选,
如果用联查, 则一个用户有多个角色的时候会查出多条重复数据,
现使用

(select GROUP_CONCAT(t3.role_id) from sys_role_user t3 where t3.user_id=t1.id) roleids

将用户的所有角色放置到一个字段中;
然后使用

 where find_in_set(#{roleId}, t.roleids);

进行条件过滤, 注意 表 t 的存在

        select t.* from (select t1.*,
        (select t2.name from sys_dept t2 where t2.id=t1.dept_id) deptName,
        (select GROUP_CONCAT(t3.role_id) from sys_role_user t3 where t3.user_id=t1.id) roleids
        from sys_user t1
        where t1.del_flag = 0
        and t1.super_admin = 0
        and t1.super_tenant = 0
        and t1.tenant_code = #{tenantCode}
        <if test="username != null and username.trim() != ''">
            and t1.username like #{username}
        </if>
        <if test="deptId != null and deptId.trim() != ''">
            and t1.dept_id = #{deptId}
        </if>
        <if test="gender != null and gender.trim() != ''">
            and t1.gender = #{gender}
        </if>
        <if test="mobile != null and mobile.trim() != ''">
            and t1.mobile = #{mobile}
        </if>
        <if test="realName != null and realName.trim() != ''">
            and t1.realName = #{real_name}
        </if>
        <if test="deptIdList != null">
            and t1.dept_id in
            <foreach item="id" collection="deptIdList" open="(" separator="," close=")">
                #{id}
            </foreach>
        </if> ) t
        <if test="roleId != null and roleId.trim() != ''">
            where find_in_set(#{roleId}, t.roleids);
        </if>