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

linwei

2026-04-02 24cb6204688e5c5878dce6e62b237dd88c2961c3

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

 drone-service/drone-fw/src/main/java/org/sxkj/fw/device/mapper/FwDeviceMapper.xml

@@ -638,50 +638,54 @@

    <select id="selectCockpitDevicePage" resultType="org.sxkj.fw.device.vo.CockpitFwDeviceVO">
        select
            d.*,
            min(ad.id) as area_divide_id,
            min(dsm.id) as defense_scene_manage_id
        d.*,
        min(ad.id) as area_divide_id,
        min(dsm.id) as defense_scene_manage_id
        from
            ja_fw_device d
        ja_fw_device d
        left join ja_fw_area_divide ad
            on d.id::VARCHAR = ANY(string_to_array(ad.device_ids, ','))
            and ad.is_deleted = 0
        /* 修改点1：统一使用 VARCHAR 匹配，去掉容易报错的 ::bigint[] */
        on d.id::VARCHAR = ANY(string_to_array(ad.device_ids, ','))
        and ad.is_deleted = 0
        left join ja_fw_defense_scene ds
            on ad.id = ANY(string_to_array(ds.area_divide_ids, ',')::bigint[])
            and ds.is_deleted = 0
        /* 修改点2：ad.id 是 bigint，需转 VARCHAR */
        on ad.id::VARCHAR = ANY(string_to_array(ds.area_divide_ids, ','))
        and ds.is_deleted = 0
        left join ja_fw_defense_scene_manage dsm
            on dsm.defense_scene_id::VARCHAR = ds.id::VARCHAR
            and dsm.is_deleted = 0
        on dsm.defense_scene_id::VARCHAR = ds.id::VARCHAR
        and dsm.is_deleted = 0
        <where>
                d.is_deleted = 0
                and d.is_enabled = 1
            d.is_deleted = 0
            and d.is_enabled = 1
            <if test=" param2.flyTime != null">
                and exists (
                select 1
                from ja_fw_defense_scene ds
                join ja_fw_defense_scene_manage dsm on dsm.defense_scene_id::VARCHAR = ds.id::VARCHAR and dsm.is_deleted = 0
                where ds.is_deleted = 0
                and ds.area_divide_ids is not null
                and ds.area_divide_ids != ''
                and ad.id = ANY(string_to_array(replace(ds.area_divide_ids, ' ', ''), ',')::bigint[])
                and (dsm.effective_date_start is null or dsm.effective_date_start &lt;= #{param2.flyTime})
                and (dsm.effective_date_end is null or dsm.effective_date_end &gt;= #{param2.flyTime})
                from ja_fw_defense_scene ds_sub
                join ja_fw_defense_scene_manage dsm_sub on dsm_sub.defense_scene_id::VARCHAR = ds_sub.id::VARCHAR and dsm_sub.is_deleted = 0
                where ds_sub.is_deleted = 0
                and ds_sub.area_divide_ids is not null
                and ds_sub.area_divide_ids != ''
                /* 修改点3：子查询中的 ad.id 同样需要转 VARCHAR */
                and ad.id::VARCHAR = ANY(string_to_array(replace(ds_sub.area_divide_ids, ' ', ''), ','))
                /* 修改点4：日期/时间参数建议显式强转，视数据库类型决定 ::bigint 或 ::timestamp */
                and (dsm_sub.effective_date_start is null or dsm_sub.effective_date_start &lt;= #{param2.flyTime})
                and (dsm_sub.effective_date_end is null or dsm_sub.effective_date_end &gt;= #{param2.flyTime})
                )
            </if>
            <if test="param2.regionCode != null and param2.regionCode != ''">
                and (d.final_outbound_area_code like concat(#{param2.regionCode},'%')
                <!-- 或者设备是共享给当前部门的 -->
                <if test="param2.currentDeptId != null and param2.currentDeptId != '' ">
                    or d.id in (
                    SELECT device_id FROM ja_fw_device_per_share
                    WHERE loan_to_dept_id = #{param2.currentDeptId}
                    /* 修改点5：loan_to_dept_id 如果是数字，参数需转 bigint */
                    WHERE loan_to_dept_id = #{param2.currentDeptId}::bigint
                    AND is_deleted = 0
                    )
                </if>
                )
            </if>
            <if test="param2.id != null and param2.id != ''">
                and d.id = #{param2.id}
                and d.id = #{param2.id}::bigint
            </if>
            <if test="param2.deviceName != null and param2.deviceName != ''">
                and d.device_name like concat('%',#{param2.deviceName},'%')
@@ -709,24 +713,23 @@
                <foreach collection="param2.deptList" item="deptId" open="(" separator="," close=")">
                    #{deptId}
                </foreach>
                <!-- 或者设备是共享给当前部门的 -->
                <if test="param2.currentDeptId != null and param2.currentDeptId != '' ">
                    or d.id in (
                    SELECT device_id FROM ja_fw_device_per_share
                    WHERE loan_to_dept_id = #{param2.currentDeptId}
                    WHERE loan_to_dept_id = #{param2.currentDeptId}::bigint
                    AND is_deleted = 0
                    )
                </if>
                )
            </if>
            <if test="param2.deviceStatusList != null and param2.deviceStatusList != ''">
            <if test="param2.deviceStatusList != null and param2.deviceStatusList.size > 0">
                and d.status in
                <foreach item="item" collection="param2.deviceStatusList" separator="," open="(" close=")" index="">
                    #{item}
                    #{item}::varchar
                </foreach>
            </if>
        </where>
            group by d.id
        group by d.id
    </select>

    <update id="updateTrackStatusByDeviceId">