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

linwei

2026-04-02 b9a0cdad894d9078994f1b836e9fb0963de68b36

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

 drone-service/drone-fw/src/main/java/org/sxkj/fw/record/mapper/FwDroneAlarmRecordMapper.xml

@@ -137,22 +137,26 @@

    <select id="alarmTypeStatistics" resultType="org.sxkj.fw.cockpit.vo.AlarmStatisticsVO">
        SELECT
        COUNT(CASE WHEN alarm_type = 1 THEN 1 END) AS realTimeAlarmCount,
        COUNT(CASE WHEN alarm_type = 2 THEN 2 END) AS historyAlarmCount
        /* 1 和 2 如果是字符类型请加单引号 '1'，如果是数字则保持现状 */
        COUNT(CASE WHEN alarm_type::text = '1' THEN 1 END) AS realTimeAlarmCount,
        COUNT(CASE WHEN alarm_type::text = '2' THEN 2 END) AS historyAlarmCount
        FROM
            ja_fw_drone_alarm_record  dar
        ja_fw_drone_alarm_record dar
        left join
            ja_fw_device d on dar.device_id = d.id::VARCHAR
        /* 修改点1：JOIN 两端对齐转为 text */
        ja_fw_device d on dar.device_id::text = d.id::text
        WHERE dar.is_deleted = 0
            AND d.is_deleted = 0
            and d.is_enabled = 1
        AND d.is_deleted = 0
        and d.is_enabled = 1
        <if test="regionCode != null and regionCode != ''">
            and (d.final_outbound_area_code like CONCAT(#{regionCode}, '%')
            <!-- 或者设备是共享给当前部门的 -->
            and (d.final_outbound_area_code::text like CONCAT(#{regionCode}, '%')
            <if test="currentDeptId != null and currentDeptId != '' ">
                or dar.device_id in (
                SELECT device_id FROM ja_fw_device_per_share
                WHERE loan_to_dept_id = #{currentDeptId}
                /* 修改点2：dar.device_id 转 text 以匹配子查询返回的文本数组 */
                or dar.device_id::text in (
                SELECT device_id::text
                FROM ja_fw_device_per_share
                /* 修改点3：参数强转为 bigint 匹配数据库字段类型 */
                WHERE loan_to_dept_id = #{currentDeptId}::bigint
                AND is_deleted = 0
                )
            </if>