From d95f78488e734c1defcad4af8b7e79c712937111 Mon Sep 17 00:00:00 2001
From: zmc <zmc_li@foxmail.com>
Date: 星期二, 15 八月 2023 16:00:26 +0800
Subject: [PATCH] 多表联查改为用join on
---
src/main/java/com/job/zsc/mapper/Vue3FumeMapper.java | 10 ++---
src/main/resources/com/job/zsc/mapper/FugitiveDustMapper.xml | 22 -----------
src/main/resources/com/job/zsc/mapper/SqlMapper.xml | 4 -
src/main/resources/com/job/zsc/mapper/Vue3FumeMapper.xml | 60 +++--------------------------
src/main/java/com/job/zsc/controller/AnalysisDataController.java | 2 -
src/main/resources/com/job/zsc/mapper/AnalysisDataMapper.xml | 12 ++---
6 files changed, 17 insertions(+), 93 deletions(-)
diff --git a/src/main/java/com/job/zsc/controller/AnalysisDataController.java b/src/main/java/com/job/zsc/controller/AnalysisDataController.java
index 54cac17..4ac79a3 100644
--- a/src/main/java/com/job/zsc/controller/AnalysisDataController.java
+++ b/src/main/java/com/job/zsc/controller/AnalysisDataController.java
@@ -55,8 +55,6 @@
//鍘婚噸 瀵筬rom_web_analysis_data鏁版嵁搴�
@GetMapping("/search")
public Result search(String shopname,String value,String begin, String end){
- String begin1=begin;
- String end1=end;
Integer count=analysisDataService.search(shopname,value,begin,end);
return Result.success(count);
}
diff --git a/src/main/java/com/job/zsc/mapper/Vue3FumeMapper.java b/src/main/java/com/job/zsc/mapper/Vue3FumeMapper.java
index 74826e9..f42960a 100644
--- a/src/main/java/com/job/zsc/mapper/Vue3FumeMapper.java
+++ b/src/main/java/com/job/zsc/mapper/Vue3FumeMapper.java
@@ -16,8 +16,7 @@
//涓ゅ紶琛ㄨ仈鍚堟煡璇�
@Select("select a.MV_ID,a.MV_Stat_Code,b.DI_Name,a.MV_Create_Time,a.MV_Data_Time,a.MV_Fume_Concentration2\n" +
- "from fd_t_minutevalue as a ,ea_t_device_info as b\n" +
- "where a.MV_Stat_Code = b.DI_Code ")
+ "from fd_t_minutevalue as a join ea_t_device_info as b on a.MV_Stat_Code = b.DI_Code ")
List<FumeHistoryData> findall();
List<FumeHistoryData> conditionQuery(String devId, String beginTime, String endTime);
@@ -31,8 +30,7 @@
List<DeviceInfo> allDeviceInfo();
@Select("select a1.dev_id,b.DI_Name,a1.exception,a1.exception_type,a1.region,a1.begin_time,a1.end_time\n" +
- "from abnormal_data as a1,ea_t_device_info as b\n" +
- "where a1.dev_id = b.DI_Code")
+ "from abnormal_data as a1 join ea_t_device_info as b on a1.dev_id = b.DI_Code\n")
List<FumeAbnormalData> allAbnormalData();
List<FumeAbnormalData> conditonQueryAbnormalData(String devId, String beginTime, String endTime);
@@ -41,8 +39,8 @@
//杩斿洖璇ュ紓甯哥被鍨嬪搴旂殑搴楅摵鍚嶅拰璁惧缂栧彿
@Select("select DISTINCT b1.DI_Name,a1.dev_id\n" +
- "from abnormal_data as a1,ea_t_device_info as b1\n" +
- "where a1.dev_id = b1.DI_Code and a1.exception_type = #{exceptionType} ")
+ "from abnormal_data as a1 join ea_t_device_info as b1 on a1.dev_id = b1.DI_Code\n" +
+ "where a1.exception_type = #{exceptionType}")
List<FumeAbnormalData> findShopName(String exceptionType);
List<FumeHistoryData> exportByShopName(@Param("a") String[] shops, String exportBeginTime, String exportEndTime);
diff --git a/src/main/resources/com/job/zsc/mapper/AnalysisDataMapper.xml b/src/main/resources/com/job/zsc/mapper/AnalysisDataMapper.xml
index 27e68c5..54d1bc3 100644
--- a/src/main/resources/com/job/zsc/mapper/AnalysisDataMapper.xml
+++ b/src/main/resources/com/job/zsc/mapper/AnalysisDataMapper.xml
@@ -120,21 +120,19 @@
<!-- 鍘婚噸-->
<select id="search" resultType="Integer">
- select count(*) from fm_web_analysis_data
+ select count(*)
+ from fm_web_analysis_data as a join ea_t_device_info as b on a.fume_dev_id = b.DI_Code
<where>
<if test="shopname != null and shopname != '' ">
- fume_dev_id=(select fume_dev_id
- from shopname_data
- where shop_name like concat('%',#{shopname},'%'))
+ a.fume_dev_id like concat('%',#{shopname},'%')
</if>
<if test="value != null and value != null">
- or shop_name like concat('%',#{value},'%')
+ and b.DI_Name = #{value}
</if>
<if test="begin != null and end != null">
- and (fume_date between #{begin} and #{end})
+ and a.fume_date between #{begin} and #{end}
</if>
</where>
- group by fume_dev_id
</select>
</mapper>
\ No newline at end of file
diff --git a/src/main/resources/com/job/zsc/mapper/FugitiveDustMapper.xml b/src/main/resources/com/job/zsc/mapper/FugitiveDustMapper.xml
index 1d320aa..645f9fc 100644
--- a/src/main/resources/com/job/zsc/mapper/FugitiveDustMapper.xml
+++ b/src/main/resources/com/job/zsc/mapper/FugitiveDustMapper.xml
@@ -43,28 +43,6 @@
</select>
-<!-- 鏍规嵁绔欑偣鍚嶅瓧鍜屾椂娈佃繘琛岀粺璁″垎鏋�-->
-<!-- <select id="analysisByTime" resultType="com.job.zsc.pojo.AnalysisDustData">-->
-<!-- select-->
-<!-- a.mn_code as mn_code,-->
-<!-- DATE(a.lst) as lst,-->
-<!-- ROUND(AVG(a.dust_value),3) as day_avg,-->
-<!-- CONCAT(ROUND(COUNT(*)/96*100, 2), '%') as day_online,-->
-<!-- CONCAT(ROUND(SUM(CASE WHEN a.dust_value >0 THEN 1 ELSE 0 END)/COUNT(*) *100, 2), '%') as day_valid,-->
-<!-- CONCAT(ROUND(SUM(CASE WHEN a.dust_value >= 1 THEN 1 ELSE 0 END)/96*100,2),'%') as day_exceeding-->
-<!-- from ja_t_dust_site_data_info as a join ja_t_dust_site_info as b on a.mn_code = b.mn_code-->
-<!-- <where>-->
-<!-- <if test="siteName !=null and siteName != ''">-->
-<!-- b.name = #{siteName}-->
-<!-- </if>-->
-
-<!-- <if test="beginTime != null and endTime != null">-->
-<!-- and a.lst between #{beginTime} and #{endTime}-->
-<!-- </if>-->
-<!-- </where>-->
-<!-- group by a.mn_code,DATE(a.lst)-->
-<!-- order by lst asc-->
-<!-- </select>-->
<!-- 鏍规嵁绔欑偣鍚嶅瓧鍜屾椂娈佃繘琛岀粺璁″垎鏋�-->
<select id="analysisByTime" resultType="com.job.zsc.pojo.AnalysisDustData">
select c.name,d.*
diff --git a/src/main/resources/com/job/zsc/mapper/SqlMapper.xml b/src/main/resources/com/job/zsc/mapper/SqlMapper.xml
index 9332f31..925d2cd 100644
--- a/src/main/resources/com/job/zsc/mapper/SqlMapper.xml
+++ b/src/main/resources/com/job/zsc/mapper/SqlMapper.xml
@@ -10,9 +10,7 @@
<if test="shopname != null and shopname != '' ">
shopname like concat('%',#{shopname},'%')
</if>
- <!--<if test="gender != null">
- and gender = #{gender}
- </if>-->
+
<if test="begin != null and end != null">
and reportingTime between #{begin} and #{end}
</if>
diff --git a/src/main/resources/com/job/zsc/mapper/Vue3FumeMapper.xml b/src/main/resources/com/job/zsc/mapper/Vue3FumeMapper.xml
index c3abbaf..d4cabc2 100644
--- a/src/main/resources/com/job/zsc/mapper/Vue3FumeMapper.xml
+++ b/src/main/resources/com/job/zsc/mapper/Vue3FumeMapper.xml
@@ -7,12 +7,8 @@
<!-- 瓒呮爣娌圭儫鏌ヨ-->
<select id="conditionQuery" resultType="com.job.zsc.pojo.FumeHistoryData">
select a1.MV_ID,a1.MV_Stat_Code,b.DI_Name,a1.MV_Create_Time,a1.MV_Data_Time,a1.MV_Fume_Concentration2
- from exceeding_st_data as a1 , ea_t_device_info as b
+ from exceeding_st_data as a1 join ea_t_device_info as b on a1.MV_Stat_Code = b.DI_Code
<where>
- <!-- 鏉′欢涓虹┖鏃舵煡璇㈠叏閮�-->
- <if test="(devId ==null or devId == '') and (beginTime ==null and endTime == null)">
- a1.MV_Stat_Code = b.DI_Code
- </if>
<if test="devId != null and devId != '' ">
and a1.MV_Stat_Code = #{devId} and b.DI_Code = #{devId}
@@ -25,25 +21,6 @@
order by MV_Data_Time asc
</select>
-<!-- 鍘嗗彶娌圭儫鏌ヨ-->
-<!-- <select id="conditionQueryHistory" resultType="com.job.zsc.pojo.FumeHistoryData">-->
-<!-- select a1.MV_ID,a1.MV_Stat_Code,b.DI_Name,a1.MV_Data_Time,a1.MV_Fume_Concentration2,a1.MV_Fan_Electricity,a1.MV_Purifier_Electricity-->
-<!-- from fd_t_minutevalue as a1 , ea_t_device_info as b-->
-<!-- <where>-->
-<!-- <!– 鏉′欢涓虹┖鏃舵煡璇㈠叏閮�–>-->
-<!-- <if test="(devId ==null or devId == '') or (beginTime ==null and endTime == null)">-->
-<!-- a1.MV_Stat_Code = b.DI_Code-->
-<!-- </if>-->
-<!-- <if test="devId != null and devId != '' ">-->
-<!-- and a1.MV_Stat_Code = #{devId} and b.DI_Code = #{devId}-->
-<!-- </if>-->
-
-<!-- <if test="beginTime != null and endTime != null">-->
-<!-- and a1.MV_Data_Time between #{beginTime} and #{endTime}-->
-<!-- </if>-->
-<!-- </where>-->
-<!-- order by MV_Data_Time asc-->
-<!-- </select>-->
<!-- 鍘嗗彶娌圭儫鏌ヨ-->
<select id="conditionQueryHistory" resultType="com.job.zsc.pojo.FumeHistoryData">
SELECT d.DI_Name, c.*
@@ -52,20 +29,10 @@
FROM fd_t_minutevalue as a
LEFT JOIN fd_t_minutevalue as b ON a.MV_Data_Time = b.MV_Data_Time AND a.MV_Isduplication < b.MV_Isduplication
WHERE b.MV_ID IS NULL AND a.MV_Stat_Code = #{devId})
- as c,ea_t_device_info as d
-
-<!-- CREATE TEMPORARY TABLE IF NOT EXISTS temp_table AS-->
-<!-- SELECT a.*-->
-<!-- FROM fd_t_minutevalue AS a-->
-<!-- LEFT JOIN fd_t_minutevalue AS b ON a.MV_Data_Time = b.MV_Data_Time AND a.MV_Isduplication < b.MV_Isduplication WHERE b.MV_ID IS NULL AND a.MV_Stat_Code = 'GLHB00000000016004'-->
-
+ as c join ea_t_device_info as d on c.MV_Stat_Code = d.DI_Code
<where>
- <!-- 鏉′欢涓虹┖鏃舵煡璇㈠叏閮�-->
- <if test="(devId ==null or devId == '') or (beginTime ==null and endTime == null)">
- c.MV_Stat_Code = d.DI_Code
- </if>
<if test="devId != null and devId != '' ">
- and c.MV_Stat_Code = #{devId} and d.DI_Code = #{devId}
+ and c.MV_Stat_Code = #{devId}
</if>
<if test="beginTime != null and endTime != null">
@@ -78,11 +45,8 @@
<!-- 鏉′欢鏌ヨ寮傚父琛�-->
<select id="conditonQueryAbnormalData" resultType="com.job.zsc.pojo.FumeAbnormalData">
select a1.dev_id,b.DI_Name,a1.exception_type,a1.region,a1.begin_time,a1.end_time
- from abnormal_data as a1,ea_t_device_info as b
+ from abnormal_data as a1 join ea_t_device_info as b on a1.dev_id = b.DI_Code
<where>
- <if test="(devId ==null or devId == '') or (beginTime ==null and endTime == null)">
- a1.dev_id = b.DI_Code
- </if>
<if test="devId != null and devId != '' ">
and a1.dev_id = #{devId} and b.DI_Code = #{devId}
@@ -99,11 +63,8 @@
<!-- 鏉′欢鏌ヨ寮傚父琛� 澧炲姞浜嗗紓甯哥被鍨嬮�夐」-->
<select id="conditonQueryAbnormalData1" resultType="com.job.zsc.pojo.FumeAbnormalData">
select a1.dev_id,b.DI_Name,a1.exception,a1.exception_type,a1.region,a1.begin_time,a1.end_time
- from abnormal_data as a1,ea_t_device_info as b
+ from abnormal_data as a1 join ea_t_device_info as b on a1.dev_id = b.DI_Code
<where>
- <if test="(devId ==null or devId == '') or (beginTime ==null and endTime == null)">
- a1.dev_id = b.DI_Code
- </if>
<if test="devId != null and devId != '' ">
and a1.dev_id = #{devId} and b.DI_Code = #{devId}
@@ -160,11 +121,8 @@
<!-- 鏉′欢缁勫悎鏌ヨ寮傚父琛� 寮傚父绫诲瀷閫夐」鏀逛负澶氶��-->
<select id="conditonQueryAbnormalData2" resultType="com.job.zsc.pojo.FumeAbnormalData">
select a1.dev_id,b.DI_Name,a1.exception,a1.exception_type,a1.region,a1.begin_time,a1.end_time
- from abnormal_data as a1,ea_t_device_info as b
+ from abnormal_data as a1 join ea_t_device_info as b on a1.dev_id = b.DI_Code
<where>
- <if test="(devId ==null or devId == '') or (beginTime ==null and endTime == null)">
- a1.dev_id = b.DI_Code
- </if>
<if test="devId != null and devId != '' ">
and a1.dev_id = #{devId} and b.DI_Code = #{devId}
@@ -187,12 +145,8 @@
<!-- 鏉′欢缁勫悎鏌ヨ寮傚父琛� 寮傚父绫诲瀷閫夐」鏀逛负澶氶��-->
<select id="conditonQueryAbnormalData3" resultType="com.job.zsc.pojo.FumeAbnormalData">
select a1.dev_id,b.DI_Name,a1.exception,a1.exception_type,a1.region,a1.begin_time,a1.end_time
- from abnormal_data as a1,ea_t_device_info as b
+ from abnormal_data as a1 join ea_t_device_info as b on a1.dev_id = b.DI_Code
<where>
- <if test="(devId ==null or devId == '') or (beginTime ==null and endTime == null)">
- a1.dev_id = b.DI_Code
- </if>
-
<if test="devId != null and devId != '' ">
and a1.dev_id = #{devId} and b.DI_Code = #{devId}
</if>
--
Gitblit v1.9.3