From ecc14b8398e4b7072a3e4b6096e084fadd4a51d8 Mon Sep 17 00:00:00 2001
From: linwei <872216696@qq.com>
Date: Thu, 02 Apr 2026 11:29:51 +0800
Subject: [PATCH] opt: sql改造其他模块改造3
---
drone-service/drone-fw/src/main/java/org/sxkj/fw/area/mapper/FwAreaDivideExtMapper.xml | 4 ++--
drone-service/drone-system/src/main/java/org/sxkj/system/mapper/DeptMapper.xml | 2 +-
drone-service/drone-fw/src/main/java/org/sxkj/fw/area/mapper/FwAreaDivideMapper.xml | 4 ++--
drone-ops/drone-resource/src/main/java/org/sxkj/resource/mapper/AttachMapper.xml | 12 ++++++------
drone-service/drone-fw/src/main/java/org/sxkj/fw/area/mapper/FwDefenseZoneMapper.xml | 4 ++--
drone-service/drone-fw/src/main/java/org/sxkj/fw/area/mapper/FwDefenseSceneManageMapper.xml | 2 +-
drone-ops/drone-resource/src/main/java/org/sxkj/resource/mapper/IFileMapper.xml | 14 +++++++-------
drone-service/drone-gd/src/main/java/org/sxkj/gd/workorder/mapper/GdClueEventMapper.xml | 8 ++++----
drone-task/drone-odm/src/main/java/org/sxkj/odm/mapper/OdmTaskInfoMapper.xml | 4 ++--
9 files changed, 27 insertions(+), 27 deletions(-)
diff --git a/drone-ops/drone-resource/src/main/java/org/sxkj/resource/mapper/AttachMapper.xml b/drone-ops/drone-resource/src/main/java/org/sxkj/resource/mapper/AttachMapper.xml
index 434714d..7d28d2d 100644
--- a/drone-ops/drone-resource/src/main/java/org/sxkj/resource/mapper/AttachMapper.xml
+++ b/drone-ops/drone-resource/src/main/java/org/sxkj/resource/mapper/AttachMapper.xml
@@ -52,7 +52,7 @@
DISTINCT attach.id,
attach.link,
attach.name,
- ifNull( attach.nick_name,SUBSTRING_INDEX( SUBSTRING_INDEX( attach.NAME, '/', - 1 ), '.', 1 )) AS nick_name,
+ COALESCE( attach.nick_name,SUBSTRING_INDEX( SUBSTRING_INDEX( attach.NAME, '/', - 1 ), '.', 1 )) AS nick_name,
attach.original_name,
attach.extension,
attach.device_sn,
@@ -219,7 +219,7 @@
attach.link,
attach.name,
attach.attach_size,
- ifNull( attach.nick_name,SUBSTRING_INDEX( SUBSTRING_INDEX( attach.NAME, '/', - 1 ), '.', 1 )) nick_name,
+ COALESCE( attach.nick_name,SUBSTRING_INDEX( SUBSTRING_INDEX( attach.NAME, '/', - 1 ), '.', 1 )) nick_name,
attach.original_name,
attach.extension,
attach.device_sn,
@@ -272,7 +272,7 @@
#{attach.startTime} and #{attach.endTime} or rela.create_time between #{attach.startTime} and #{attach.endTime} )
</if>
<if test="attach.name!=null and attach.name!=''">
- and ifNull(attach.nick_name, SUBSTRING_INDEX(SUBSTRING_INDEX(attach.NAME, '/', -1), '.', 1))
+ and COALESCE(attach.nick_name, SUBSTRING_INDEX(SUBSTRING_INDEX(attach.NAME, '/', -1), '.', 1))
like concat('%',#{attach.name},'%')
</if>
<if test="attach.jobName!=null and attach.jobName!=''">
@@ -515,7 +515,7 @@
attach.create_time,
attach.link,
rela.event_name,
- ifnull(attach.result_type,2) "result_type"
+ COALESCE(attach.result_type,2) "result_type"
FROM blade_attach attach
LEFT JOIN drone_job_event_rela rela ON attach.id = rela.attach_id::VARCHAR
WHERE
@@ -552,7 +552,7 @@
select attach.create_time,
attach.id,
attach.name,
- ifNull(attach.nick_name, SUBSTRING_INDEX(SUBSTRING_INDEX(attach.NAME, '/', - 1), '.', 1)) nick_name,
+ COALESCE(attach.nick_name, SUBSTRING_INDEX(SUBSTRING_INDEX(attach.NAME, '/', - 1), '.', 1)) nick_name,
attach.link,
attach.device_sn,
attach.metadata,
@@ -579,7 +579,7 @@
select attach.create_time,
attach.id,
attach.name,
- ifNull(attach.nick_name, SUBSTRING_INDEX(SUBSTRING_INDEX(attach.NAME, '/', - 1), '.', 1)) nick_name,
+ COALESCE(attach.nick_name, SUBSTRING_INDEX(SUBSTRING_INDEX(attach.NAME, '/', - 1), '.', 1)) nick_name,
attach.link,
attach.device_sn,
attach.metadata,
diff --git a/drone-ops/drone-resource/src/main/java/org/sxkj/resource/mapper/IFileMapper.xml b/drone-ops/drone-resource/src/main/java/org/sxkj/resource/mapper/IFileMapper.xml
index be133bc..797415e 100644
--- a/drone-ops/drone-resource/src/main/java/org/sxkj/resource/mapper/IFileMapper.xml
+++ b/drone-ops/drone-resource/src/main/java/org/sxkj/resource/mapper/IFileMapper.xml
@@ -97,7 +97,7 @@
SELECT
odm.base_path basePath,
wj.dkbh,
- IFNULL(odm.ort_tb_path,odm.orthoimage_path) path,
+ COALESCE(odm.ort_tb_path,odm.orthoimage_path) path,
FROM_UNIXTIME( wj.completed_time/ 1000) as createTime,
md.nickname as airportName
FROM
@@ -145,12 +145,12 @@
<!--根据类型获取数据成果数量-->
<select id="getMediaFileCountByResultType" resultType="org.sxkj.resource.vo.MediaFileCountVO">
SELECT
- IFNULL( sum( CASE WHEN result_type = 0 THEN 1 ELSE 0 END ), 0 ) AS "phoneNum",
- IFNULL( sum( CASE WHEN result_type = 1 THEN 1 ELSE 0 END ), 0 ) AS "videoNum",
- IFNULL( sum( CASE WHEN result_type = 2 THEN 1 ELSE 0 END ), 0 ) AS "aiNum",
- IFNULL( sum( CASE WHEN result_type = 3 THEN 1 ELSE 0 END ), 0 ) AS "gisNum",
- IFNULL( sum( CASE WHEN result_type = 4 THEN 1 ELSE 0 END ), 0 ) AS "tifNum",
- IFNULL( sum( CASE WHEN result_type = 5 THEN 1 ELSE 0 END ), 0 ) AS "fullNum"
+ COALESCE( sum( CASE WHEN result_type = 0 THEN 1 ELSE 0 END ), 0 ) AS "phoneNum",
+ COALESCE( sum( CASE WHEN result_type = 1 THEN 1 ELSE 0 END ), 0 ) AS "videoNum",
+ COALESCE( sum( CASE WHEN result_type = 2 THEN 1 ELSE 0 END ), 0 ) AS "aiNum",
+ COALESCE( sum( CASE WHEN result_type = 3 THEN 1 ELSE 0 END ), 0 ) AS "gisNum",
+ COALESCE( sum( CASE WHEN result_type = 4 THEN 1 ELSE 0 END ), 0 ) AS "tifNum",
+ COALESCE( sum( CASE WHEN result_type = 5 THEN 1 ELSE 0 END ), 0 ) AS "fullNum"
FROM
blade_attach a
LEFT JOIN wayline_job wj ON a.wayline_job_id = wj.job_id::VARCHAR
diff --git a/drone-service/drone-fw/src/main/java/org/sxkj/fw/area/mapper/FwAreaDivideExtMapper.xml b/drone-service/drone-fw/src/main/java/org/sxkj/fw/area/mapper/FwAreaDivideExtMapper.xml
index eb0c193..95ae3e2 100644
--- a/drone-service/drone-fw/src/main/java/org/sxkj/fw/area/mapper/FwAreaDivideExtMapper.xml
+++ b/drone-service/drone-fw/src/main/java/org/sxkj/fw/area/mapper/FwAreaDivideExtMapper.xml
@@ -54,8 +54,8 @@
#{item.createTime},
#{item.updateUser},
#{item.updateTime},
- IFNULL(#{item.status}, '0'),
- IFNULL(#{item.isDeleted}, 0)
+ COALESCE(#{item.status}, '0'),
+ COALESCE(#{item.isDeleted}, 0)
)
</foreach>
</insert>
diff --git a/drone-service/drone-fw/src/main/java/org/sxkj/fw/area/mapper/FwAreaDivideMapper.xml b/drone-service/drone-fw/src/main/java/org/sxkj/fw/area/mapper/FwAreaDivideMapper.xml
index 46c6ddd..d86a258 100644
--- a/drone-service/drone-fw/src/main/java/org/sxkj/fw/area/mapper/FwAreaDivideMapper.xml
+++ b/drone-service/drone-fw/src/main/java/org/sxkj/fw/area/mapper/FwAreaDivideMapper.xml
@@ -608,8 +608,8 @@
#{createTime},
#{updateUser},
#{updateTime},
- IFNULL(#{status}, '0'),
- IFNULL(#{isDeleted}, 0)
+ COALESCE(#{status}, '0'),
+ COALESCE(#{isDeleted}, 0)
)
</insert>
diff --git a/drone-service/drone-fw/src/main/java/org/sxkj/fw/area/mapper/FwDefenseSceneManageMapper.xml b/drone-service/drone-fw/src/main/java/org/sxkj/fw/area/mapper/FwDefenseSceneManageMapper.xml
index d8653ca..92c59f1 100644
--- a/drone-service/drone-fw/src/main/java/org/sxkj/fw/area/mapper/FwDefenseSceneManageMapper.xml
+++ b/drone-service/drone-fw/src/main/java/org/sxkj/fw/area/mapper/FwDefenseSceneManageMapper.xml
@@ -61,7 +61,7 @@
else length(ds.area_divide_ids) - length(replace(ds.area_divide_ids, ',', '')) + 1
end as area_count,
(
- select ifnull(sum(
+ select COALESCE(sum(``
case
when ad.device_ids is null or ad.device_ids = '' then 0
else length(ad.device_ids) - length(replace(ad.device_ids, ',', '')) + 1
diff --git a/drone-service/drone-fw/src/main/java/org/sxkj/fw/area/mapper/FwDefenseZoneMapper.xml b/drone-service/drone-fw/src/main/java/org/sxkj/fw/area/mapper/FwDefenseZoneMapper.xml
index 30f003a..bfd6cf1 100644
--- a/drone-service/drone-fw/src/main/java/org/sxkj/fw/area/mapper/FwDefenseZoneMapper.xml
+++ b/drone-service/drone-fw/src/main/java/org/sxkj/fw/area/mapper/FwDefenseZoneMapper.xml
@@ -266,8 +266,8 @@
#{createTime},
#{updateUser},
#{updateTime},
- IFNULL(#{status}, '0'),
- IFNULL(#{isDeleted}, 0)
+ COALESCE(#{status}, '0'),
+ COALESCE(#{isDeleted}, 0)
)
</insert>
diff --git a/drone-service/drone-gd/src/main/java/org/sxkj/gd/workorder/mapper/GdClueEventMapper.xml b/drone-service/drone-gd/src/main/java/org/sxkj/gd/workorder/mapper/GdClueEventMapper.xml
index 2a6d831..634f005 100644
--- a/drone-service/drone-gd/src/main/java/org/sxkj/gd/workorder/mapper/GdClueEventMapper.xml
+++ b/drone-service/drone-gd/src/main/java/org/sxkj/gd/workorder/mapper/GdClueEventMapper.xml
@@ -86,7 +86,7 @@
<select id="selectGdClueEventList" resultMap="gdClueEventVoResultMap">
select ce.*,
- ifnull(bu.real_name, bu.name) as dispose_user_name,
+ COALESCE(bu.real_name, bu.name) as dispose_user_name,
bd.dept_name as dispose_dept_name
from (select * from ja_gd_clue_event ${ew.customSqlSegment}) ce
left join blade_user bu on bu.id = ce.dispose_user::VARCHAR and bu.is_deleted = 0
@@ -111,9 +111,9 @@
<select id="selectGdClueEventDetailById" resultMap="gdClueEventVoResultMap">
select ce.*,
- ifnull(bu.real_name, bu.name) as dispose_user_name,
+ COALESCE(bu.real_name, bu.name) as dispose_user_name,
bd.dept_name as dispose_dept_name,
- ifnull(cu.real_name, cu.name) as distribute_user_name,
+ COALESCE(cu.real_name, cu.name) as distribute_user_name,
cd.dept_name as distribute_dept_name,
ce.create_time as distribute_time,
concat(ce.longitude, ',', ce.latitude) as event_location,
@@ -132,7 +132,7 @@
<select id="selectGdClueEventCount" resultMap="gdClueEventCountVoResultMap">
select count(1) as total_count,
- ifnull(sum(case when ce.dispose_user = #{userId} then 1 else 0 end), 0) as my_count
+ COALESCE(sum(case when ce.dispose_user = #{userId} then 1 else 0 end), 0) as my_count
from ja_gd_clue_event ce
<include refid="gdClueEventDeptWhere"/>
and ce.event_status in (1, 3)
diff --git a/drone-service/drone-system/src/main/java/org/sxkj/system/mapper/DeptMapper.xml b/drone-service/drone-system/src/main/java/org/sxkj/system/mapper/DeptMapper.xml
index a774c34..434ce18 100644
--- a/drone-service/drone-system/src/main/java/org/sxkj/system/mapper/DeptMapper.xml
+++ b/drone-service/drone-system/src/main/java/org/sxkj/system/mapper/DeptMapper.xml
@@ -198,7 +198,7 @@
left join manage_device_per_share ddps on ddps.loan_to_dept_id = bd.id::VARCHAR
where bd.is_deleted = 0
and bd.id != #{deptId}
- and ifnull(device_sn,'') != #{deviceSn}
+ and COALESCE(device_sn,'') != #{deviceSn}
and bd.id not in (
-- 排除当前部门的所有上级部门
select parent_dept.id
diff --git a/drone-task/drone-odm/src/main/java/org/sxkj/odm/mapper/OdmTaskInfoMapper.xml b/drone-task/drone-odm/src/main/java/org/sxkj/odm/mapper/OdmTaskInfoMapper.xml
index 9c86e74..7640bb6 100644
--- a/drone-task/drone-odm/src/main/java/org/sxkj/odm/mapper/OdmTaskInfoMapper.xml
+++ b/drone-task/drone-odm/src/main/java/org/sxkj/odm/mapper/OdmTaskInfoMapper.xml
@@ -124,7 +124,7 @@
oti.vox_grid_tiles_path,
oti.device_sn,
wj.dkbh,
- IFNULL( ST_AsText ( oti.geom ), ifnull( tli.sdfw, tli.dkfw )) AS geom
+ COALESCE( ST_AsText ( oti.geom ), COALESCE( tli.sdfw, tli.dkfw )) AS geom
FROM odm_task_info oti
left join wayline_job wj on wj.job_id = oti.wayline_job_id::VARCHAR
left join tb_lot_info tli on tli.dkbh = wj.dkbh::VARCHAR
@@ -174,7 +174,7 @@
oti.vox_grid_tiles_path,
oti.device_sn,
tli.dkbh,
- IFNULL( ST_AsText ( oti.geom ), ifnull( tli.sdfw, tli.dkfw )) AS geom
+ COALESCE( ST_AsText ( oti.geom ), COALESCE( tli.sdfw, tli.dkfw )) AS geom
FROM
odm_task_info oti
LEFT JOIN wayline_job wj ON wj.job_id = oti.wayline_job_id::VARCHAR AND wj.is_deleted = 0
--
Gitblit v1.9.3