MySQL FIND_IN_SET 一种匹配逗号分割字符串参数方法

一枚小蜗牛H

于 2024-05-27 10:00:09 发布

阅读量248

点赞数 1

分类专栏： Java驿站文章标签： mysql 数据库

本文链接：https://blog.csdn.net/Ying_ph/article/details/139230129

版权

Java驿站专栏收录该内容

123 篇文章 8 订阅

订阅专栏

类似于下面这种sql

SELECT
            cpi.id AS personnelId,
            cpi.user_id AS userId,
            cpi.staff_name AS staffName,
            cpi.phonenumber AS phone,
            cpi.login_date AS loginDate,
            sd.dept_name AS deptName,
            su.user_name AS account,
            su.`status` AS accountStatus,
            cpi.dept_id AS deptId,
            cpi.staff_role_id AS roleIds,
            (
            SELECT GROUP_CONCAT(sr.role_name SEPARATOR ',')
            FROM sys_role sr
            WHERE FIND_IN_SET(sr.role_id, cpi.staff_role_id)
            ) AS roleNames
        FROM ca_personnel_info cpi
                 LEFT JOIN sys_dept sd ON sd.dept_id = cpi.dept_id
                 LEFT JOIN sys_user su ON su.user_id = cpi.user_id
        WHERE cpi.del_flag = 1

SELECT
            tmp.property_id AS propertyId,
            tmp.pPropertyId AS pId,
            tmp.property_name AS propertyName,
#             tmp.staff_name AS staffName,
            tmp.update_time AS updateTime,
            tmp.create_user AS createUser,
            tmp.create_user AS userId,
            tmp.file_size AS fileSize,
            tmp.file_type AS fileType,
            tmp.file_id AS fileId,
            tmp.dept_id AS deptId,
#             tmp.is_hide AS hide,
            tmp.labelIds AS labelIds,
            tmp.labelNames AS labelNames,
            tmp.path AS path,
            tmp.pathId AS pathId
        FROM
            (
                SELECT
                    asset.id AS property_id,
                    asset.p_property_id AS pPropertyId,
                    asset.property_name,
#                     cpi.staff_name,
                    asset.update_time,
                    asset.create_user,
                    fs.file_size,
                    fs.file_type,
                    asset.file_id,
                    su.dept_id,
#                     asset.is_hide,
                    (
                        SELECT GROUP_CONCAT(ca_label_info.id)
                        FROM ca_rel_property_label
                                 LEFT JOIN ca_label_info ON ca_label_info.id = ca_rel_property_label.label_id
                        WHERE property_id = asset.id
                          AND ca_rel_property_label.is_valid = 1
                          AND asset.ownership_type IN ('P001', 'P002', 'P003', 'P004')
                    ) AS labelIds,
                    (
                        SELECT GROUP_CONCAT(ca_label_info.label_name)
                        FROM ca_rel_property_label
                                 LEFT JOIN ca_label_info ON ca_label_info.id = ca_rel_property_label.label_id
                        WHERE property_id = asset.id
                          AND ca_rel_property_label.is_valid = 1
                          AND asset.ownership_type IN ('P001', 'P002', 'P003', 'P004')
                    ) AS labelNames,
                    (
                        WITH RECURSIVE cte (n, id, property_name, property_type, p_property_id, create_user) AS (
                            SELECT
                                1 AS n,
                                id,
                                property_name,
                                property_type,
                                p_property_id,
                                create_user
                            FROM
                                ca_property_ownership
                            WHERE
                                id = asset.id
                            UNION ALL
                            SELECT
                                n + 1 AS n,
                                parent.id,
                                parent.property_name,
                                parent.property_type,
                                parent.p_property_id,
                                parent.create_user
                            FROM
                                ca_property_ownership parent,
                                cte
                            WHERE
                                parent.id = cte.p_property_id
                        )
                        SELECT GROUP_CONCAT(property_name ORDER BY n DESC SEPARATOR '/')
                        FROM cte
                        WHERE id != asset.id
                    ) AS path,
                    (
                        WITH RECURSIVE cte (n, id, property_name, property_type, p_property_id, create_user) AS (
                            SELECT
                                1 AS n,
                                id,
                                property_name,
                                property_type,
                                p_property_id,
                                create_user
                            FROM
                                ca_property_ownership
                            WHERE
                                id = asset.id
                            UNION ALL
                            SELECT
                                n + 1 AS n,
                                parent.id,
                                parent.property_name,
                                parent.property_type,
                                parent.p_property_id,
                                parent.create_user
                            FROM
                                ca_property_ownership parent,
                                cte
                            WHERE
                                parent.id = cte.p_property_id
                        )
                        SELECT GROUP_CONCAT(id ORDER BY n DESC)
                        FROM cte
                        WHERE id != asset.id
                    ) AS pathId
                FROM
                    ca_property_ownership asset
#                         LEFT JOIN ca_personnel_info cpi ON cpi.user_id = asset.create_user
                        LEFT JOIN ca_file_storage fs ON fs.id = asset.file_id
                        LEFT JOIN sys_user su ON su.user_id = asset.create_user
                WHERE
                    asset.is_retrieve = 0
                  AND asset.property_type = 0
#                   AND asset.is_approval = 1
                  AND asset.ownership_type IN ('P001', 'P002', 'P003', 'P004')
               ORDER BY asset.update_time DESC
            ) tmp
        WHERE 1 = 1
        <if test="query.keyword != null and query.keyword != ''">
            AND tmp.property_name LIKE CONCAT('%',#{query.keyword},'%')
        </if>
        <if test="query.fileType != null">
            AND tmp.file_type = #{query.fileType}
        </if>
        <if test="query.startTime != null and query.endTime != null">
            AND tmp.update_time BETWEEN #{query.startTime} and #{query.endTime}
        </if>
        <if test="query.deptId != null and query.deptId != 0">
            AND tmp.dept_id = #{query.deptId}
        </if>
        <if test="query.userId != null and query.userId != 0">
            AND tmp.create_user = #{query.userId}
        </if>
<!--        <if test="query.adminIdFlag == false">
            AND tmp.is_hide = 0
        </if>-->
          /*这里不可以用模糊查询*/
        <if test="query.pid != null and query.pid != 0">
            AND find_in_set(#{query.pid},tmp.pathId)
        </if>
        <if test="query.labelIds != null and query.labelIds.size() > 0">
            AND (
            <foreach item="id" index="index" collection="query.labelIds" open="" separator=" OR " close="">
                FIND_IN_SET(#{id}, tmp.labelIds) > 0
            </foreach>
            )
        </if>