From 6f28997aa8d9f62e0c54838490a473041f709742 Mon Sep 17 00:00:00 2001
From: zmc <zmc_li@foxmail.com>
Date: 星期四, 02 十一月 2023 11:42:32 +0800
Subject: [PATCH] 修改了数据排名清单sql查询语句
---
src/main/java/com/flightfeather/monitor/mapper/FugitiveDustMapper.java | 2
src/main/java/com/flightfeather/monitor/service/impl/FugitiveDustServiceImpl.java | 4 +-
src/main/java/com/flightfeather/monitor/controller/FugitiveDustController.java | 4 +-
src/main/java/com/flightfeather/monitor/domain/ds1/repository/DustStatisticsValueRep.kt | 2 +
src/main/java/com/flightfeather/monitor/service/FugitiveDustService.java | 2
src/main/resources/mapper/FugitiveDustMapper.xml | 55 ++++++++++++++++-----------
src/main/resources/application.yml | 12 +++---
7 files changed, 47 insertions(+), 34 deletions(-)
diff --git a/src/main/java/com/flightfeather/monitor/controller/FugitiveDustController.java b/src/main/java/com/flightfeather/monitor/controller/FugitiveDustController.java
index c2d7cd6..4772e55 100644
--- a/src/main/java/com/flightfeather/monitor/controller/FugitiveDustController.java
+++ b/src/main/java/com/flightfeather/monitor/controller/FugitiveDustController.java
@@ -50,8 +50,8 @@
// 鏍规嵁绔欑偣鍚嶅瓧鍜屾椂闂存鏌ヨ鍧囧�煎拰
@GetMapping("/analysisdata")
- public Result analysisdata( String siteName,String beginTime, String endTime){
- List<AnalysisDustData> list = fugitiveDustService.analysisdata(siteName,beginTime,endTime);
+ public Result analysisdata( String siteName,String beginTime, String endTime,String type){
+ List<AnalysisDustData> list = fugitiveDustService.analysisdata(siteName,beginTime,endTime,type);
return Result.success(list);
}
diff --git a/src/main/java/com/flightfeather/monitor/domain/ds1/repository/DustStatisticsValueRep.kt b/src/main/java/com/flightfeather/monitor/domain/ds1/repository/DustStatisticsValueRep.kt
index 6af586e..fbd89ea 100644
--- a/src/main/java/com/flightfeather/monitor/domain/ds1/repository/DustStatisticsValueRep.kt
+++ b/src/main/java/com/flightfeather/monitor/domain/ds1/repository/DustStatisticsValueRep.kt
@@ -51,4 +51,6 @@
val dataCount = d * 96
dustStatisticsValueMapper.monthlyStatics(s, e, dataCount.toInt())
}
+
+
}
\ No newline at end of file
diff --git a/src/main/java/com/flightfeather/monitor/mapper/FugitiveDustMapper.java b/src/main/java/com/flightfeather/monitor/mapper/FugitiveDustMapper.java
index 096af75..397ac58 100644
--- a/src/main/java/com/flightfeather/monitor/mapper/FugitiveDustMapper.java
+++ b/src/main/java/com/flightfeather/monitor/mapper/FugitiveDustMapper.java
@@ -19,7 +19,7 @@
@Select("select DISTINCT name from ja_t_dust_site_info ")
List<DustSiteData> allSiteName();
- List<AnalysisDustData> analysisdata(String siteName, String beginTime, String endTime);
+ List<AnalysisDustData> analysisdata(String siteName, String beginTime, String endTime,String type);
List<AnalysisDustData> analysisByTime(String siteName, String beginTime, String endTime);
diff --git a/src/main/java/com/flightfeather/monitor/service/FugitiveDustService.java b/src/main/java/com/flightfeather/monitor/service/FugitiveDustService.java
index 552c299..7a878f4 100644
--- a/src/main/java/com/flightfeather/monitor/service/FugitiveDustService.java
+++ b/src/main/java/com/flightfeather/monitor/service/FugitiveDustService.java
@@ -12,7 +12,7 @@
List<DustSiteData> allSiteName();
- List<AnalysisDustData> analysisdata( String siteName,String beginTime, String endTime);
+ List<AnalysisDustData> analysisdata( String siteName,String beginTime, String endTime,String type);
List<AnalysisDustData> analysisByTime(String siteName, String beginTime, String endTime);
diff --git a/src/main/java/com/flightfeather/monitor/service/impl/FugitiveDustServiceImpl.java b/src/main/java/com/flightfeather/monitor/service/impl/FugitiveDustServiceImpl.java
index f1a6131..36c1e97 100644
--- a/src/main/java/com/flightfeather/monitor/service/impl/FugitiveDustServiceImpl.java
+++ b/src/main/java/com/flightfeather/monitor/service/impl/FugitiveDustServiceImpl.java
@@ -46,8 +46,8 @@
}
@Override
- public List<AnalysisDustData> analysisdata( String siteName,String beginTime, String endTime) {
- List<AnalysisDustData> list = fugitiveDustMapper.analysisdata(siteName,beginTime,endTime);
+ public List<AnalysisDustData> analysisdata( String siteName,String beginTime, String endTime,String type) {
+ List<AnalysisDustData> list = fugitiveDustMapper.analysisdata(siteName,beginTime,endTime,type);
return list;
}
diff --git a/src/main/resources/application.yml b/src/main/resources/application.yml
index 050fda5..0889e17 100644
--- a/src/main/resources/application.yml
+++ b/src/main/resources/application.yml
@@ -2,9 +2,9 @@
datasource:
ds1:
driver-class-name: com.mysql.cj.jdbc.Driver
- url: jdbc:mysql://localhost:3306/fume
- username: fume
- password: fume_feiyu2023
+# url: jdbc:mysql://localhost:3306/fume
+# username: fume
+# password: fume_feiyu2023
# url: jdbc:mysql://localhost:3306/qianduan_sql
# username: root
@@ -14,9 +14,9 @@
# username: root
# password: 123456
-# url: jdbc:mysql://114.215.109.124:3306/fume
-# username: fumeRemote
-# password: feiyu2023
+ url: jdbc:mysql://114.215.109.124:3306/fume
+ username: fumeRemote
+ password: feiyu2023
mybatis:
configuration:
diff --git a/src/main/resources/mapper/FugitiveDustMapper.xml b/src/main/resources/mapper/FugitiveDustMapper.xml
index de696eb..f387e49 100644
--- a/src/main/resources/mapper/FugitiveDustMapper.xml
+++ b/src/main/resources/mapper/FugitiveDustMapper.xml
@@ -63,41 +63,52 @@
</if>
<if test="beginTime != null and endTime != null">
- and a.Lst between #{beginTime} and #{endTime}
+ and a.Lst between #{beginTime} and #{endTime} and a.type = #{type}
</if>
</where>
</select>
+
+<!-- <!– 鏍规嵁绔欑偣鍚嶅瓧鍜屾椂娈佃繘琛岀粺璁″垎鏋�–>-->
+<!-- <select id="analysisByTime" resultType="com.flightfeather.monitor.pojo.AnalysisDustData">-->
+<!-- select c.name,d.*-->
+<!-- from ja_t_dust_site_info as c join-->
+<!-- (-->
+<!-- select-->
+<!-- mn_code as mn_code,-->
+<!-- DATE(lst) as lst,-->
+<!-- ROUND(AVG(dust_value),3) as day_avg,-->
+<!-- min(dust_value) as min,-->
+<!-- max(dust_value) as max,-->
+<!-- CONCAT(ROUND(COUNT(*)/96*100, 2), '%') as day_online,-->
+<!-- CONCAT(ROUND(SUM(CASE WHEN dust_value >0 THEN 1 ELSE 0 END)/96 *100, 2), '%') as day_valid,-->
+<!-- CONCAT(ROUND(SUM(CASE WHEN dust_value >= 1 THEN 1 ELSE 0 END)/96*100,2),'%') as day_exceeding-->
+<!-- from ja_t_dust_site_data_info-->
+<!-- <where>-->
+<!--<!– <if test="siteName !=null and siteName != ''">–>-->
+<!--<!– b.name = #{siteName}–>-->
+<!--<!– </if>–>-->
+
+<!-- <if test="beginTime != null and endTime != null">-->
+<!-- lst between #{beginTime} and #{endTime}-->
+<!-- </if>-->
+
+<!-- </where>-->
+<!-- GROUP BY mn_code,DATE(lst)-->
+<!-- order by lst asc ) as d on c.mn_code = d.mn_code-->
+<!-- </select>-->
<!-- 鏍规嵁绔欑偣鍚嶅瓧鍜屾椂娈佃繘琛岀粺璁″垎鏋�-->
<select id="analysisByTime" resultType="com.flightfeather.monitor.pojo.AnalysisDustData">
select c.name,d.*
- from ja_t_dust_site_info as c join
- (
- select
- mn_code as mn_code,
- DATE(lst) as lst,
- ROUND(AVG(dust_value),3) as day_avg,
- min(dust_value) as min,
- max(dust_value) as max,
- CONCAT(ROUND(COUNT(*)/96*100, 2), '%') as day_online,
- CONCAT(ROUND(SUM(CASE WHEN dust_value >0 THEN 1 ELSE 0 END)/96 *100, 2), '%') as day_valid,
- CONCAT(ROUND(SUM(CASE WHEN dust_value >= 1 THEN 1 ELSE 0 END)/96*100,2),'%') as day_exceeding
- from ja_t_dust_site_data_info
+ from ja_t_dust_site_info as c join dust_statistics_value as d on c.mn_code = d.mn_code
<where>
-<!-- <if test="siteName !=null and siteName != ''">-->
-<!-- b.name = #{siteName}-->
-<!-- </if>-->
-
<if test="beginTime != null and endTime != null">
- lst between #{beginTime} and #{endTime}
+ d.lst between #{beginTime} and #{endTime} and d.type = 'day'
</if>
-
</where>
- GROUP BY mn_code,DATE(lst)
- order by lst asc ) as d on c.mn_code = d.mn_code
+ order by c.name asc
</select>
-
<!--鏉′欢鏌ヨ寮傚父鏁版嵁 鍔犱笂浜嗗璁′俊鎭�-->
--
Gitblit v1.9.3