opt: sql改造其他模块改造10

linwei

2026-04-02 3cbe09c3d14734798ba8662aeac633289afa2cf6

opt: sql改造其他模块改造10

 drone-ops/drone-resource/src/main/java/org/sxkj/resource/mapper/AttachMapper.xml

@@ -23,30 +23,34 @@
    <!--自定义分页查询-->
    <select id="selectAttachPage" resultType="org.sxkj.resource.vo.AttachVO">
        select
            ba.*,
            bd.dept_name,
            coalesce(ba.nick_name,ba.original_name) nickName
        ba.*,
        bd.dept_name,
        coalesce(ba.nick_name,ba.original_name) nickName
        from
            blade_attach ba
        blade_attach ba
        left join
                blade_dept bd on ba.create_dept = bd.id::VARCHAR
        /* 修改点1：双向转为 text 对齐 */
        blade_dept bd on ba.create_dept::text = bd.id::text
        where
            ba.is_deleted = 0
        ba.is_deleted = 0
        <if test="attach.originalName!=null and attach.originalName!=''">
            and original_name like concat('%',#{attach.originalName},'%')
            and ba.original_name like concat('%',#{attach.originalName},'%')
        </if>
        <if test="attach.resultType!=null">
            and result_type = #{attach.resultType}
            /* 修改点2：如果 resultType 是字符串传入，也需加权转 */
            and ba.result_type::text = #{attach.resultType}::text
        </if>
        <if test="attach.deptList != null and attach.deptList.size > 0">
            and ba.create_dept in
            and ba.create_dept::text in
            <foreach collection="attach.deptList" item="deptId" open="(" separator="," close=")">
                #{deptId}
                #{deptId}::text
            </foreach>
        </if>
            and result_type in (1,2,3,4,5)
        order by create_time desc,id desc
        /* 修改点3：result_type 如果是字符型字段，数字必须加单引号 */
        and ba.result_type::text in ('1','2','3','4','5')
        order by ba.create_time desc, ba.id desc
    </select>

    <select id="findAiAttachImages" resultType="org.sxkj.resource.vo.AttachVO">
        select
        DISTINCT attach.id,

 drone-service/drone-gd/src/main/java/org/sxkj/gd/orderdata/mapper/GdDataObjectionMapper.xml

@@ -30,39 +30,52 @@

    <select id="selectGdDataObjectionPage" resultMap="gdDataObjectionResultMap">
        select
            obj.*,
        string_agg(da.attach_name SEPARATOR ',') as attach_names,
            bd.dept_name as create_dept_name
        obj.id,
        /* 建议显式列出 obj 的其他必要字段，或者确保 id 是主键 */
        obj.title,
        obj.objection_status,
        obj.create_dept,
        obj.apply_target_dept_id,
        obj.create_time,
        obj.is_deleted,
        /* 修改点1：修正 string_agg 语法，去掉 SEPARATOR */
        string_agg(da.attach_name::text, ',') as attach_names,
        bd.dept_name as create_dept_name
        from
            ja_gd_data_objection obj
        ja_gd_data_objection obj
        left join
                ja_gd_data_objection_attachment da
                on obj.id = da.objection_id::VARCHAR and da.is_deleted = 0
        ja_gd_data_objection_attachment da
        /* 修改点2：双向转型对齐 */
        on obj.id::text = da.objection_id::text
        and da.is_deleted = 0
        left join
                blade_dept bd on bd.id = obj.create_dept::VARCHAR
        blade_dept bd on bd.id::text = obj.create_dept::text
        <where>
                obj.is_deleted = 0
                and da.is_deleted = 0
            obj.is_deleted = 0
            /* 修改点3：da.is_deleted 已移至 ON 条件，此处删除以保证 LEFT JOIN 有效 */
            <if test="param2.title != null and param2.title != ''">
                and obj.title::text like concat('%',#{param2.title},'%')
            </if>
            <if test="param2.objectionStatus != null and param2.objectionStatus != ''">
                and obj.objection_status = #{param2.objectionStatus}
                and obj.objection_status::text = #{param2.objectionStatus}::text
            </if>
            <if test="param2.deptList != null and param2.deptList.size > 0">
                and (
                obj.create_dept in
                obj.create_dept::text in
                <foreach collection="param2.deptList" item="deptId" open="(" separator="," close=")">
                    #{deptId}
                    #{deptId}::text
                </foreach>
                or obj.apply_target_dept_id::text in
                <foreach collection="param2.deptList" item="deptId" open="(" separator="," close=")">
                    #{deptId}::text
                </foreach>
                )
                or obj.apply_target_dept_id in
                <foreach collection="param2.deptList" item="deptId" open="(" separator="," close=")">
                    #{deptId}
                </foreach>
            </if>
        </where>
        group by obj.id,bd.dept_name
        /* 修改点4：GROUP BY 必须包含 SELECT 中所有非聚合字段 */
        group by
        obj.id, obj.title, obj.objection_status, obj.create_dept,
        obj.apply_target_dept_id, obj.create_time, obj.is_deleted, bd.dept_name
    </select>



 drone-service/drone-gd/src/main/java/org/sxkj/gd/workorder/mapper/GdManageDeviceMapper.xml

@@ -19,30 +19,32 @@

    <select id="selectGdManageDevicePage" resultMap="gdManageDeviceResultMap">
        select
            md.*,
            bd.dept_name as device_dept_name
        md.*,
        bd.dept_name as device_dept_name
        from
            ja_gd_manage_device md
        ja_gd_manage_device md
        left join
                blade_dept bd on md.create_dept = bd.id::VARCHAR
        /* 修改点1：双向转型对齐，确保 bigint 和 varchar 都能匹配 */
        blade_dept bd on md.create_dept::text = bd.id::text
        <where>
                and md.is_deleted = 0
            /* 修改点2：去掉开头多余的 and，保持 SQL 语法严谨 */
            md.is_deleted = 0
            <if test="param2.nickname != null and param2.nickname != ''">
                and md.nickname like concat('%',#{param2.nickname},'%')
            </if>
            <if test="param2.deptId != null">
                and md.create_dept = #{param2.deptId}
                /* 修改点3：参数过滤也建议加转型，防止 param2.deptId 被 MyBatis 传为 String */
                and md.create_dept::text = #{param2.deptId}::text
            </if>
            <if test="param2.location != null and param2.location != ''">
                and md.location like concat('%',#{param2.location},'%')
            </if>
            <if test="param2.modeCode != null and param2.modeCode != ''">
                and md.mode_code = #{param2.modeCode}
                and md.mode_code::text = #{param2.modeCode}::text
            </if>
            <if test="param2.deviceType != null and param2.deviceType != ''">
                and md.device_type = #{param2.deviceType}
                and md.device_type::text = #{param2.deviceType}::text
            </if>

        </where>
    </select>


 drone-service/drone-gd/src/main/java/org/sxkj/gd/workorder/mapper/GdPatrolTaskMapper.xml

@@ -31,47 +31,49 @@

    <select id="selectGdPatrolTaskPage" resultMap="gdPatrolTaskResultMap">
        select
            pt.*,
            md.nickname as device_name,
            wo.work_order_name,
            COALESCE(tr.result_count, 0) as result_count,
            COALESCE(tr.issue_event_count, 0) as issue_event_count
        pt.*,
        md.nickname as device_name,
        wo.work_order_name,
        COALESCE(tr.result_count, 0) as result_count,
        COALESCE(tr.issue_event_count, 0) as issue_event_count
        from
            ja_gd_patrol_task pt
        ja_gd_patrol_task pt
        left join
            ja_gd_work_order wo on pt.work_order_id = wo.id::VARCHAR
            ja_gd_work_order wo on pt.work_order_id::VARCHAR = wo.id::VARCHAR
        left join
                ja_gd_manage_device md on md.id = pt.device_id::VARCHAR
                ja_gd_manage_device md on md.id::VARCHAR = pt.device_id::VARCHAR
        left join
            (
            select patrol_task_id,
                count(*) as result_count,
                sum(case when distribute_status in (1,2) then 1 else 0 end) as issue_event_count
            from
                ja_gd_task_result
            where
                is_deleted = 0
            group by
                patrol_task_id
            ) tr on tr.patrol_task_id = pt.id::VARCHAR
        (
        select patrol_task_id,
        count(*) as result_count,
        sum(case when distribute_status in (1,2) then 1 else 0 end) as issue_event_count
        from
        ja_gd_task_result
        where
        is_deleted = 0
        group by
        patrol_task_id
        ) tr on tr.patrol_task_id::VARCHAR = pt.id::VARCHAR
        <where>
                pt.is_deleted = 0
            pt.is_deleted = 0
            <if test="param2.workOrderId != null and param2.workOrderId != ''">
                and pt.work_order_id = #{param2.workOrderId}
                /* 重点：参数也强制转类型 */
                and pt.work_order_id::VARCHAR = #{param2.workOrderId}::VARCHAR
            </if>
            <if test="param2.patrolTaskName != null and param2.patrolTaskName != ''">
                and pt.patrol_task_name like concat(#{param2.patrolTaskName},'%')
            </if>
            <if test="param2.patrolTaskType != null and param2.patrolTaskType != ''">
                and pt.patrol_task_type = #{param2.patrolTaskType}
                and pt.patrol_task_type::VARCHAR = #{param2.patrolTaskType}::VARCHAR
            </if>
            <if test="param2.taskStatus != null and param2.taskStatus != ''">
                and pt.task_status = #{param2.taskStatus}
                /* 报错信息中出现了这个参数，务必加强转 */
                and pt.task_status::VARCHAR = #{param2.taskStatus}::VARCHAR
            </if>
            <if test="param2.deptList != null and param2.deptList.size > 0">
                and pt.create_dept in
                and pt.create_dept::VARCHAR in
                <foreach collection="param2.deptList" item="deptId" open="(" separator="," close=")">
                    #{deptId}
                    #{deptId}::VARCHAR
                </foreach>
            </if>
            <if test="param2.startTime != null and param2.startTime != '' and param2.endTime != null and param2.endTime != '' ">

 drone-service/drone-gd/src/main/java/org/sxkj/gd/workorder/mapper/GdWorkOrderMapper.xml

@@ -81,50 +81,54 @@

    <select id="selectGdWorkOrderPage" resultMap="gdWorkOrderResultMap">
        select
            wo.id,
            wo.work_order_name,
            wo.work_order_code,
            wo.work_order_type,
            wo.device_load_demand,
            wo.recommend_device_ids,
            wo.work_order_status,
            wo.execute_start_time,
            wo.execute_end_time,
            wo.service_party,
            ST_AsText(wo.geom) geom,
            wo.remark,
            wo.area_code,
            wo.create_user,
            wo.create_dept,
            wo.create_time,
            wo.update_user,
            wo.update_time,
            wo.status,
            wo.is_deleted,
            bu.name create_user_name
        wo.id,
        wo.work_order_name,
        wo.work_order_code,
        wo.work_order_type,
        wo.device_load_demand,
        wo.recommend_device_ids,
        wo.work_order_status,
        wo.execute_start_time,
        wo.execute_end_time,
        wo.service_party,
        ST_AsText(wo.geom) geom,
        wo.remark,
        wo.area_code,
        wo.create_user,
        wo.create_dept,
        wo.create_time,
        wo.update_user,
        wo.update_time,
        wo.status,
        wo.is_deleted,
        bu.name create_user_name
        from ja_gd_work_order wo
            left join blade_user bu on wo.create_user = bu.id::VARCHAR
        /* 修改点1：关联两端全部转为 VARCHAR */
        left join blade_user bu on wo.create_user::VARCHAR = bu.id::VARCHAR
        <where>
                and wo.is_deleted = 0
            /* 修正语法：去掉开头的 and */
            wo.is_deleted = 0
            <if test="param2.workOrderStatus != null and param2.workOrderStatus != ''">
                and wo.work_order_status like concat(#{param2.workOrderStatus},'%')
                and wo.work_order_status::VARCHAR like concat(#{param2.workOrderStatus},'%')
            </if>
            <if test="param2.workOrderName != null and param2.workOrderName != ''">
                and wo.work_order_name like concat('%',#{param2.workOrderName},'%')
            </if>
            <if test="param2.workOrderType != null and param2.workOrderType != ''">
                and wo.work_order_type = #{param2.workOrderType}
                and wo.work_order_type::VARCHAR = #{param2.workOrderType}::VARCHAR
            </if>
            <if test="param2.createUser != null ">
                and wo.create_user = #{param2.createUser}
                /* 修改点2：过滤条件也进行类型对齐 */
                and wo.create_user::VARCHAR = #{param2.createUser}::VARCHAR
            </if>
            <if test="param2.startTime != null and param2.startTime != '' and param2.endTime != null and param2.endTime != ''">
                and wo.create_time BETWEEN #{param2.startTime} AND #{param2.endTime}
            </if>
            <if test="param2.deptList != null and param2.deptList.size > 0">
                and wo.create_dept in
                /* 修改点3：IN 查询内部元素类型转换 */
                and wo.create_dept::VARCHAR in
                <foreach collection="param2.deptList" item="deptId" open="(" separator="," close=")">
                    #{deptId}
                    #{deptId}::VARCHAR
                </foreach>
            </if>
        </where>