优化sql

lyh

21 小时以前 82c1b82a6d46620a5ae493353c37d40da691f716

优化sql

 lxzn-module-dnc/src/main/java/org/jeecg/modules/dnc/mapper/xml/PermissionStreamNewMapper.xml

@@ -3,31 +3,35 @@
<mapper namespace="org.jeecg.modules.dnc.mapper.PermissionStreamNewMapper">
    <select id="loadProductMix" resultType="org.jeecg.modules.dnc.entity.ProductMix">
        SELECT DISTINCT
            mix.id,
            mix.tree_code,
                mix.tree_name,
            mix.parent_id,
            mix.tree_type AS 'type',
                mix.extend,
                mix.create_time
        FROM
            nc_product_mix mix -- 连接权限表，筛选部门相关记录
                LEFT JOIN nc_permission_stream_new nps_depart ON mix.id = nps_depart.business_id
                AND nps_depart.delete_flag = '0'
                        <if test="productIdList != null and productIdList.size() > 0">
                            AND nps_depart.depart_id IN
                            <foreach collection="productIdList" item="productId" index="index" open="(" close=")" separator=",">
                                #{productId}
                            </foreach>
                        </if>
                AND nps_depart.user_id IS NULL -- 连接权限表，筛选用户相关记录
                LEFT JOIN nc_permission_stream_new nps_user ON mix.id = nps_user.business_id
                AND nps_user.user_id = #{userId}
                AND nps_user.delete_flag = '0'
        WHERE
            nps_depart.business_id IS NOT NULL
          AND nps_user.business_id IS NOT NULL
        order by mix.tree_type, mix.create_time asc
        mix.id,
        mix.tree_code AS 'code',
        mix.tree_name AS 'name',
        mix.parent_id,
        mix.tree_type AS 'type',
        mix.extend,
        mix.create_time
        FROM nc_product_mix mix
        WHERE EXISTS (
        SELECT 1
        FROM nc_permission_stream_new nps_depart
        WHERE nps_depart.business_id = mix.id
        AND nps_depart.delete_flag = '0'
        AND nps_depart.user_id IS NULL
        <if test="productIdList != null and productIdList.size() > 0">
            AND nps_depart.depart_id IN
            <foreach collection="productIdList" item="productId" open="(" close=")" separator=",">
                #{productId}
            </foreach>
        </if>
        )
        AND EXISTS (
        SELECT 1
        FROM nc_permission_stream_new nps_user
        WHERE nps_user.business_id = mix.id
        AND nps_user.user_id = #{userId}
        AND nps_user.delete_flag = '0'
        )
        ORDER BY mix.tree_type, mix.create_time ASC;
    </select>
    <select id="loadProductMixAll" resultType="org.jeecg.modules.dnc.entity.ProductMix">
        SELECT DISTINCT mix.id,