From 27dab2d7e2c2940c9a623d2084860868c9e06509 Mon Sep 17 00:00:00 2001
From: xieb <vip_xiaobin810@163.com>
Date: Tue, 21 May 2024 14:45:36 +0800
Subject: [PATCH] 解决sql报错问题
---
skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/mapper/DamSecurityMapper.xml | 52 --------------------------
skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/mapper/TbProjectMapper.xml | 12 ------
skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/mapper/TbResDykeMapper.xml | 12 ------
skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/message/mapper/MessageRecordMapper.xml | 3 -
skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/mapper/TbResGeneralInvestigationMapper.xml | 21 ----------
5 files changed, 0 insertions(+), 100 deletions(-)
diff --git a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/mapper/DamSecurityMapper.xml b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/mapper/DamSecurityMapper.xml
index fba93fa..c048b19 100644
--- a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/mapper/DamSecurityMapper.xml
+++ b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/mapper/DamSecurityMapper.xml
@@ -110,8 +110,6 @@
left join sjzt_md."att_ad_base" adc on (adc."guid" = adb."p_ad_code")
left join sjzt_md."att_ad_base" addd on (addd."guid" = adc."p_ad_code")
where res."interior_ad_guid" = aab."guid" or ada."guid" = aab."guid" or adb."guid" = aab."guid" or adc."guid" = aab."guid" or addd."guid" = aab."guid"
-
-
) as errorNum,
(
SELECT COUNT(*) FROM (
@@ -231,7 +229,6 @@
OR b."ad_code" = aab."guid" )
)
) allNum
-
FROM SJZT_MD."att_ad_base" aab
where
<choose>
@@ -244,21 +241,14 @@
</choose>
)
)TMP
-
-
-
-
-
</select>
<select id="listRegionCount" resultType="cn.gistack.sm.damSecurity.vo.DamSecurityStatistics">
-
SELECT allNum,errorNum, allNum-errorNum normalNum,adName,adCode FROM
(
-
SELECT
(
SELECT COUNT(*) FROM
@@ -339,7 +329,6 @@
or c."ad_code" = aab."guid" or b."ad_code" = aab."guid" )
)
) allNum,
-
(
SELECT COUNT(*)FROM (SELECT distinct DAM_ID FROM NKY_ALARM_GET NAG
LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = NAG.DAM_ID
@@ -347,14 +336,12 @@
left join SJZT_MD."att_ad_base" c on b."p_ad_code" = c."ad_code"
left join SJZT_MD."att_ad_base" d on c."p_ad_code" = d."ad_code"
where (d."ad_code" = aab."guid" or c."ad_code" = aab."guid" or b."ad_code" = aab."guid" )
-
<if test="dam.startTime != null and dam.startTime != ''">
AND time >= #{dam.startTime}
</if>
<if test="dam.endTime != null and dam.endTime !='' ">
AND time <= #{dam.endTime}
</if>
-
)) errorNum,
aab."ad_name" adName , aab."guid" adCode from SJZT_MD."att_ad_base" aab
where
@@ -367,7 +354,6 @@
</otherwise>
</choose>
)
-
</select>
<select id="listRegionCountOptimize" resultType="cn.gistack.sm.damSecurity.vo.DamSecurityStatistics">
select "ad_code","ad_name",allNum,errorNum,allNum-errorNum normalNum from (
@@ -385,8 +371,6 @@
left join sjzt_md."att_ad_base" adc on (adc."guid" = adb."p_ad_code")
left join sjzt_md."att_ad_base" addd on (addd."guid" = adc."p_ad_code")
where res."interior_ad_guid" = aab."guid" or ada."guid" = aab."guid" or adb."guid" = aab."guid" or adc."guid" = aab."guid" or addd."guid" = aab."guid"
-
-
) as errorNum,
(
SELECT COUNT(*) FROM (
@@ -506,7 +490,6 @@
OR b."ad_code" = aab."guid" )
)
) allNum
-
FROM SJZT_MD."att_ad_base" aab
where
<choose>
@@ -818,7 +801,6 @@
<select id="tableResCountPageOptimize" resultType="cn.gistack.sm.damSecurity.vo.DamStationStatistics">
-
SELECT
info."guid" AS resCd,
info."res_reg_code" as resRegCode,
@@ -863,30 +845,22 @@
d."order_id" ELSE e."order_id" END AS "province_order_id"
FROM sjzt_md."att_res_base" a
LEFT JOIN (
-
-
SELECT
distinct (dri."res_cd")
FROM
sjzt_dw."dim_res_info_a" dri
LEFT JOIN sjzt_ods."dsm_spg_spprmp" dss ON dri."oth_reg_cd" = dss."rscd"
where dss."rscd" is not null
-
) f ON a."guid" = f."res_cd"
LEFT JOIN (
-
-
SELECT distinct(dri."res_cd")
from sjzt_dw."dim_res_info_a" dri
left join sjzt_ods."dsm_spg_spqnmp" dss on trim(dss."rscd") = dri."oth_reg_cd"
where dss."rscd" is not null
-
) G on a."guid" = g."res_cd"
LEFT JOIN (
-
SELECT distinct("res_cd") from sjzt_md."att_wy_cd_base" awcb
where awcb."cd" != '00'
-
) h ON a."guid" = h."res_cd"
LEFT JOIN sjzt_md."att_ad_base" b ON a."interior_ad_guid" = b."guid"
LEFT JOIN sjzt_md."att_ad_base" c ON b."p_ad_code" = c."guid"
@@ -896,23 +870,15 @@
<if test="dam.adCode != null and dam.adCode !='' ">
AND (c."ad_code" = #{dam.adCode} or d."ad_code" =#{dam.adCode}or e."ad_code" = #{dam.adCode})
</if>
-
) baseRes
LEFT JOIN (
-
-
select res."res_cd",count(*) wyStation from(
-
SELECT distinct("res_cd") from sjzt_md."att_wy_cd_base" awcb
where awcb."cd" != '00'
-
)res LEFT JOIN SJZT_MD."att_wy_cd_base" acb ON acb."res_cd" = res."res_cd"
where acb."cd_type" = 'wy' group by res."res_cd"
-
-
) wy ON wy."res_cd" = baseRes."guid"
LEFT JOIN(
-
select res."res_cd",count(*) syStation from(
SELECT
distinct (dri."res_cd")
@@ -922,40 +888,27 @@
where dss."rscd" is not null
)res LEFT JOIN SJZT_MD."att_cd_base" acb ON acb."res_cd" = res."res_cd"
where acb."cd_type" = 'sy' group by res."res_cd"
-
) sy ON sy."res_cd" = baseRes."guid"
LEFT JOIN (
-
-
select res."res_cd",count(*) slStation from(
-
-
SELECT distinct(dri."res_cd")
from sjzt_dw."dim_res_info_a" dri
left join sjzt_ods."dsm_spg_spqnmp" dss on trim(dss."rscd") = dri."oth_reg_cd"
where dss."rscd" is not null
-
-
)res LEFT JOIN SJZT_MD."att_cd_base" acb ON acb."res_cd" = res."res_cd"
where acb."cd_type" = 'sl' group by res."res_cd"
-
-
) sl ON sl."res_cd" = baseRes."guid"
LEFT JOIN (
SELECT DAM_ID ,COUNT(*) errorNum FROM NKY_ALARM_GET
WHERE 1=1
-
<if test="dam.startTime != null and dam.startTime != ''">
AND time >= #{dam.startTime}
</if>
<if test="dam.endTime != null and dam.endTime !='' ">
AND time <= #{dam.endTime}
</if>
-
-
GROUP BY "DAM_ID"
) alarm ON alarm.DAM_ID = baseRes."guid"
-
LEFT JOIN (
SELECT DAM_ID ,COUNT(*) errorCountNum
FROM (
@@ -969,12 +922,7 @@
</if>
GROUP BY DAM_ID,POINT_ID
) baseCount GROUP BY DAM_ID) c1 ON c1.dam_id = baseRes."guid"
-
-
WHERE 1=1
- <!--这里加条件-->
-
-
<if test="dam.resName != null and dam.resName !='' ">
AND "name" LIKE CONCAT('%',#{dam.resName},'%')
</if>
diff --git a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/message/mapper/MessageRecordMapper.xml b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/message/mapper/MessageRecordMapper.xml
index 9a5de40..362260d 100644
--- a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/message/mapper/MessageRecordMapper.xml
+++ b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/message/mapper/MessageRecordMapper.xml
@@ -26,12 +26,9 @@
<if test="messageRecord.source!=null and messageRecord.source!=''">
and source = #{messageRecord.source}
</if>
-
<if test="messageRecord.createTime != null">
AND DATE_FORMAT(create_time,'%Y-%m-%d') = DATE_FORMAT(#{messageRecord.createTime},'%Y-%m-%d')
</if>
-
-
ORDER BY ID DESC
</select>
diff --git a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/mapper/TbProjectMapper.xml b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/mapper/TbProjectMapper.xml
index 164b3ff..cc335f4 100644
--- a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/mapper/TbProjectMapper.xml
+++ b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/mapper/TbProjectMapper.xml
@@ -133,16 +133,13 @@
aab."ad_name" adName,
paab."guid" padCode,
paab."ad_name" padName,
-
tps.curMonthActualFund,
project.totalActualFund,
-
ifNull(resNum,0) resNum,
ifNull(bigNum,0) bigNum,
ifNull(midNum,0) midNum,
ifNull(smallOneNum,0) smallOneNum,
ifNull(smallTwoNum,0) smallTwoNum,
-
dykeLength,
levelOneLength,
levelTwoLength,
@@ -176,7 +173,6 @@
SELECT
aab."guid",
aab."ad_name",
-
SUM(CASE WHEN trd."dyke_guid" is not null then trd.dangerLength else 0 END) as dykeLength,
SUM(CASE WHEN trd."dyke_level" = '1级' THEN trd.dangerLength ELSE 0 END) AS levelOneLength,
SUM(CASE WHEN trd."dyke_level" = '2级' THEN trd.dangerLength ELSE 0 END) AS levelTwoLength,
@@ -218,8 +214,6 @@
SUM(CASE WHEN (tdi."danger_length") THEN tdi."danger_length" ELSE 0 END) AS dangerLength
from sjzt_md."tb_dyke_investigation" tdi
where 1=1
-
-
<if test="params.tbYear != null and params.tbYear != ''">
AND tdi."tb_year" = #{params.tbYear}
</if>
@@ -235,7 +229,6 @@
)trd on ((trd.countyCode = aab."guid") or (trd.cityCode = aab."guid") or (trd.provinceCode = aab."guid"))
group by aab."guid",aab."ad_name"
)dyke on dyke."guid" = aab."guid"
-
left join (
SELECT
aab."guid",
@@ -248,20 +241,17 @@
tb."county_code" countyCode,
province."guid" provinceCode,
tps."actual_fund"
-
from sjzt_md."tb_project" tb
LEFT JOIN SJZT_MD."tb_project_schedule" tps on tps."tb_project_id" = tb."guid"
LEFT JOIN YWXT."att_ad_base" province ON province."ad_code" = tb."city_code"
)tb on ((tb.countyCode = aab."guid") or (tb.cityCode = aab."guid") or (tb.provinceCode = aab."guid"))
group by aab."guid",aab."ad_name"
) project on project."guid" = aab."guid"
-
left join (
SELECT
aab."guid",
aab."ad_name",
tb."actual_fund" curMonthActualFund
-
FROM YWXT."att_ad_base" aab
LEFT JOIN (
select
@@ -269,7 +259,6 @@
tb."county_code" countyCode,
province."guid" provinceCode,
tps."actual_fund"
-
from sjzt_md."tb_project" tb
LEFT JOIN SJZT_MD."tb_project_schedule" tps on tps."tb_project_id" = tb."guid"
LEFT JOIN YWXT."att_ad_base" province ON province."ad_code" = tb."city_code"
@@ -283,7 +272,6 @@
</if>
)tb on ((tb.countyCode = aab."guid") or (tb.cityCode = aab."guid") or (tb.provinceCode = aab."guid"))
) tps on tps."guid" = aab."guid"
-
left join ywxt."att_ad_base" paab on paab."guid" = aab."p_ad_code"
where
1=1
diff --git a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/mapper/TbResDykeMapper.xml b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/mapper/TbResDykeMapper.xml
index a855c20..5e296ac 100644
--- a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/mapper/TbResDykeMapper.xml
+++ b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/mapper/TbResDykeMapper.xml
@@ -61,30 +61,21 @@
<!-- 堤防普查列表 -->
<select id="findTbResDykePcBy"
resultType="cn.gistack.sm.sjztmd.vo.TbResDykeVO">
-
SELECT base.* from (
-
select
trd.*,
tdis."check_state" AS tb_state,
tdis."guid" as tbStateId,
aa."p_ad_code" as pAdCode,
aa."p_ad_name" as pAdName,
-
(CASE WHEN tmp.totalDangerNum>0 THEN 1 ELSE 2 END) as hasDanger,
-
tmp.totalDangerNum,
tmp.termiteNum,
tmp.otherNum
-
from sjzt_md."tb_res_dyke" trd
left join "SJZT_MD"."tb_dyke_investigation_state" tdis on trd."guid" = tdis."dyke_guid"
-
left join YWXT."att_ad_base" aa on aa."guid" = trd."ad_code"
-
-
left join
-
(
select
temp2."tb_state_id",
@@ -117,7 +108,6 @@
temp2.totalDangerNum,
temp2.termiteNum,
temp2.otherNum
-
) tmp on tmp."tb_state_id" = tdis."guid"
where 1 = 1
<if test="vo.name !=null and vo.name !=''">
@@ -138,7 +128,5 @@
<if test="vo.hasDanger != null and vo.hasDanger != '' ">
and base.hasDanger = #{vo.hasDanger}
</if>
-
-
</select>
</mapper>
diff --git a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/mapper/TbResGeneralInvestigationMapper.xml b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/mapper/TbResGeneralInvestigationMapper.xml
index 42ee2a0..5b25abe 100644
--- a/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/mapper/TbResGeneralInvestigationMapper.xml
+++ b/skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/sjztmd/mapper/TbResGeneralInvestigationMapper.xml
@@ -387,7 +387,6 @@
order by aab."guid" asc
</select>
<select id="getStatisticsTable" resultType="cn.gistack.sm.sjztmd.statisticsVO.StatisticsTable">
-
SELECT
aab."guid" adCode,
aab."ad_name" adName,
@@ -402,7 +401,6 @@
(town."ad_code" = aab."guid" or county."ad_code" = aab."guid" or city."ad_code" = aab."guid" or province."ad_code" = aab."guid")
)
) resNum,
-
(
SELECT COUNT(*) FROM
SJZT_MD."tb_res_general_investigation_state" trgis
@@ -411,16 +409,13 @@
LEFT JOIN YWXT."att_ad_base" county ON county."ad_code" = town."p_ad_code"
LEFT JOIN YWXT."att_ad_base" city ON city."ad_code" = county."p_ad_code"
LEFT JOIN YWXT."att_ad_base" province ON province."ad_code" = city."p_ad_code"
-
where 1=1
and (town."ad_code" = aab."guid" or county."ad_code" = aab."guid" or city."ad_code" = aab."guid" or province."ad_code" = aab."guid")
<if test="params.tbYear != null and params.tbYear !=''">
and trgis."tb_year" = #{params.tbYear}
</if>
and trgis."check_state" = 1
-
)investigationResNum,
-
(
SELECT COUNT(*) FROM
SJZT_MD."tb_res_general_investigation_state" trgis
@@ -429,17 +424,13 @@
LEFT JOIN YWXT."att_ad_base" county ON county."ad_code" = town."p_ad_code"
LEFT JOIN YWXT."att_ad_base" city ON city."ad_code" = county."p_ad_code"
LEFT JOIN YWXT."att_ad_base" province ON province."ad_code" = city."p_ad_code"
-
where 1=1
and (town."ad_code" = aab."guid" or county."ad_code" = aab."guid" or city."ad_code" = aab."guid" or province."ad_code" = aab."guid")
<if test="params.tbYear != null and params.tbYear !=''">
and trgis."tb_year" = #{params.tbYear}
</if>
-
and trgis."check_state" = 2
-
)checkResNum,
-
(
SELECT COUNT(*)
FROM SJZT_MD."tb_res_dyke" trd
@@ -449,7 +440,6 @@
where 1=1
and ( county."ad_code" = aab."guid" or city."ad_code" = aab."guid" or province."ad_code" = aab."guid")
)dykeNum,
-
(
SELECT COUNT(*) FROM
SJZT_MD."tb_dyke_investigation_state" tdis
@@ -457,7 +447,6 @@
LEFT JOIN YWXT."att_ad_base" county ON county."ad_code" = trd."ad_code"
LEFT JOIN YWXT."att_ad_base" city ON city."ad_code" = county."p_ad_code"
LEFT JOIN YWXT."att_ad_base" province ON province."ad_code" = city."p_ad_code"
-
where 1=1
and (county."ad_code" = aab."guid" or city."ad_code" = aab."guid" or province."ad_code" = aab."guid")
<if test="params.tbYear != null and params.tbYear !=''">
@@ -465,7 +454,6 @@
</if>
and tdis."check_state" = 1
)investigationDykeNum,
-
(
SELECT COUNT(*) FROM
SJZT_MD."tb_dyke_investigation_state" tdis
@@ -473,7 +461,6 @@
LEFT JOIN YWXT."att_ad_base" county ON county."ad_code" = trd."ad_code"
LEFT JOIN YWXT."att_ad_base" city ON city."ad_code" = county."p_ad_code"
LEFT JOIN YWXT."att_ad_base" province ON province."ad_code" = city."p_ad_code"
-
where 1=1
and (county."ad_code" = aab."guid" or city."ad_code" = aab."guid" or province."ad_code" = aab."guid")
<if test="params.tbYear != null and params.tbYear !=''">
@@ -481,8 +468,6 @@
</if>
and tdis."check_state" = 2
)checkDykeNum,
-
-
IFNULL(
(
SELECT
@@ -493,8 +478,6 @@
LEFT JOIN ywxt."att_ad_base" province on province."ad_code" = city."p_ad_code"
where (county."ad_code" = aab."guid" or city."ad_code" = aab."guid" or province."ad_code" = aab."guid")
),0) AS dykeTotalLength,
-
-
IFNULL((
SELECT
SUM(CASE WHEN trd.dangerLength THEN trd.dangerLength ELSE 0 END)
@@ -525,7 +508,6 @@
<if test="params.tbYear != null and params.tbYear != ''">
AND tdi."tb_year" = #{params.tbYear}
</if>
-
group by tdi."tb_state_id",tdi."dyke_guid",tdi."unit"
)TEMP1
) tdi on tdi."tb_state_id" = tdis."guid"
@@ -536,9 +518,6 @@
LEFT JOIN YWXT."att_ad_base" province ON province."ad_code" = city."p_ad_code"
where (county."ad_code" = aab."guid" or city."ad_code" = aab."guid" or province."ad_code" = aab."guid")
),0) AS dangerTotalLength
-
-
-
FROM YWXT."att_ad_base" aab
LEFT JOIN YWXT."att_ad_base" paab ON paab."guid" = aab."p_ad_code"
where
--
Gitblit v1.9.3