From 83c1bbca96beb5461fff56d861214444348e86d0 Mon Sep 17 00:00:00 2001
From: liurunyu <lry9898@163.com>
Date: 星期二, 29 七月 2025 16:51:28 +0800
Subject: [PATCH] 优化SQL语句，以提高查询速度
---
pipIrr-platform/pipIrr-global/src/main/resources/mapper/RmOpenCloseValveHistoryMapper.xml | 192 ++++++++++++++++++++++++++++++++++++++++++++++++
pipIrr-platform/pipIrr-web/pipIrr-web-statistics/src/main/java/com/dy/pipIrrStatistics/client/ClientCtrl.java | 12 +++
2 files changed, 204 insertions(+), 0 deletions(-)
diff --git a/pipIrr-platform/pipIrr-global/src/main/resources/mapper/RmOpenCloseValveHistoryMapper.xml b/pipIrr-platform/pipIrr-global/src/main/resources/mapper/RmOpenCloseValveHistoryMapper.xml
index 830a944..bf62b09 100644
--- a/pipIrr-platform/pipIrr-global/src/main/resources/mapper/RmOpenCloseValveHistoryMapper.xml
+++ b/pipIrr-platform/pipIrr-global/src/main/resources/mapper/RmOpenCloseValveHistoryMapper.xml
@@ -1079,6 +1079,24 @@
<!--鑾峰彇鎸囧畾鏃堕棿娈靛唴娑堣垂閲戦瓒呰繃鎸囧畾鍊肩殑鍐滄埛鏁伴噺-->
<select id="getLargeAmountSpentClientsCount" resultType="java.lang.Long">
+ SELECT COUNT(*)
+ FROM se_client cli
+ inner join (
+ SELECT client_id, SUM(cl_this_money) AS amountSpent
+ FROM rm_open_close_valve_history
+ <where>
+ <if test="timeStart != null and timeStart != '' and timeStop != null and timeStop != ''">
+ op_dt BETWEEN #{timeStart} AND #{timeStop}
+ </if>
+ </where>
+ group by client_id
+ <if test="amountSpent != null and amountSpent > 0">
+ having amountSpent > #{amountSpent}
+ </if>
+ ) as his on his.client_id = cli.id
+ </select>
+ <!-- 2025-07-29 涓嬮潰SQL鏌ヨ澶參锛屽簾寮� -->
+ <select id="_getLargeAmountSpentClientsCount" resultType="java.lang.Long">
SELECT COUNT(*) AS recordCount
FROM se_client cli
<where>
@@ -1092,6 +1110,36 @@
</select>
<!--鑾峰彇鎸囧畾鏃堕棿娈靛唴娑堣垂閲戦瓒呰繃鎸囧畾鍊肩殑鍐滄埛-->
<select id="getLargeAmountSpentClients" resultType="com.dy.pipIrrGlobal.voSt.VoClient">
+ SELECT cli.id AS clientId,
+ cli.name AS clientName,
+ cli.clientNum,
+ cli.address,
+ cli.phone,
+ cli.idCard,
+ his.amountSpent
+ FROM se_client cli
+ inner join (
+ SELECT client_id, SUM(cl_this_money) AS amountSpent
+ FROM rm_open_close_valve_history
+ <where>
+ <if test="timeStart != null and timeStart != '' and timeStop != null and timeStop != ''">
+ op_dt BETWEEN #{timeStart} AND #{timeStop}
+ </if>
+ </where>
+ group by client_id
+ <if test="amountSpent != null and amountSpent > 0">
+ having amountSpent > #{amountSpent}
+ </if>
+ ) as his on his.client_id = cli.id
+ ORDER BY cli.id
+ <trim prefix="limit ">
+ <if test="start != null and count != null">
+ #{start,javaType=Integer,jdbcType=INTEGER}, #{count,javaType=Integer,jdbcType=INTEGER}
+ </if>
+ </trim>
+ </select>
+ <!-- 2025-07-29 涓嬮潰SQL鏌ヨ澶參锛屽簾寮� -->
+ <select id="_getLargeAmountSpentClients" resultType="com.dy.pipIrrGlobal.voSt.VoClient">
SELECT cli.id AS clientId,
cli.name AS clientName,
cli.clientNum,
@@ -1126,6 +1174,24 @@
<!--鑾峰彇鎸囧畾鏃堕棿娈靛唴娑堣垂閲戦浣庝簬鎸囧畾鍊肩殑鍐滄埛鏁伴噺-->
<select id="getSmallAmountSpentClientsCount" resultType="java.lang.Long">
+ SELECT COUNT(*)
+ FROM se_client cli
+ inner join (
+ SELECT client_id, SUM(cl_this_money) AS amountSpent
+ FROM rm_open_close_valve_history
+ <where>
+ <if test="timeStart != null and timeStart != '' and timeStop != null and timeStop != ''">
+ op_dt BETWEEN #{timeStart} AND #{timeStop}
+ </if>
+ </where>
+ group by client_id
+ <if test="amountSpent != null and amountSpent > 0">
+ having amountSpent < #{amountSpent}
+ </if>
+ ) as his on his.client_id = cli.id
+ </select>
+ <!-- 2025-07-29 涓嬮潰SQL鏌ヨ澶參锛屽簾寮� -->
+ <select id="_getSmallAmountSpentClientsCount" resultType="java.lang.Long">
SELECT COUNT(*) AS recordCount
FROM se_client cli
<where>
@@ -1139,6 +1205,36 @@
</select>
<!--鑾峰彇鎸囧畾鏃堕棿娈靛唴娑堣垂閲戦浣庝簬鎸囧畾鍊肩殑鍐滄埛-->
<select id="getSmallAmountSpentClients" resultType="com.dy.pipIrrGlobal.voSt.VoClient">
+ SELECT cli.id AS clientId,
+ cli.name AS clientName,
+ cli.clientNum,
+ cli.address,
+ cli.phone,
+ cli.idCard,
+ his.amountSpent
+ FROM se_client cli
+ inner join (
+ SELECT client_id, SUM(cl_this_money) AS amountSpent
+ FROM rm_open_close_valve_history
+ <where>
+ <if test="timeStart != null and timeStart != '' and timeStop != null and timeStop != ''">
+ op_dt BETWEEN #{timeStart} AND #{timeStop}
+ </if>
+ </where>
+ group by client_id
+ <if test="amountSpent != null and amountSpent > 0">
+ having amountSpent < #{amountSpent}
+ </if>
+ ) as his on his.client_id = cli.id
+ ORDER BY cli.id
+ <trim prefix="limit ">
+ <if test="start != null and count != null">
+ #{start,javaType=Integer,jdbcType=INTEGER}, #{count,javaType=Integer,jdbcType=INTEGER}
+ </if>
+ </trim>
+ </select>
+ <!-- 2025-07-29 涓嬮潰SQL鏌ヨ澶參锛屽簾寮� -->
+ <select id="_getSmallAmountSpentClients" resultType="com.dy.pipIrrGlobal.voSt.VoClient">
SELECT cli.id AS clientId,
cli.name AS clientName,
cli.clientNum,
@@ -1173,6 +1269,24 @@
<!--鑾峰彇鎸囧畾鏃堕棿娈靛唴鐢ㄦ按鏃堕暱瓒呰繃鎸囧畾鍊肩殑鍐滄埛鏁伴噺-->
<select id="getLargeWaterDurationClientsCount" resultType="java.lang.Long">
+ SELECT COUNT(*)
+ FROM se_client cli
+ inner join (
+ SELECT client_id, SUM(cl_this_time) AS waterDuration
+ FROM rm_open_close_valve_history
+ <where>
+ <if test="timeStart != null and timeStart != '' and timeStop != null and timeStop != ''">
+ op_dt BETWEEN #{timeStart} AND #{timeStop}
+ </if>
+ </where>
+ group by client_id
+ <if test="waterDuration != null and waterDuration > 0">
+ having waterDuration > #{waterDuration}
+ </if>
+ ) as his on his.client_id = cli.id
+ </select>
+ <!-- 2025-07-29 涓嬮潰SQL鏌ヨ澶參锛屽簾寮� -->
+ <select id="_getLargeWaterDurationClientsCount" resultType="java.lang.Long">
SELECT COUNT(*) AS recordCount
FROM se_client cli
<where>
@@ -1187,6 +1301,36 @@
<!--鑾峰彇鎸囧畾鏃堕棿娈靛唴鐢ㄦ按鏃堕暱瓒呰繃鎸囧畾鍊肩殑鍐滄埛-->
<select id="getLargeWaterDurationClients" resultType="com.dy.pipIrrGlobal.voSt.VoClient">
+ SELECT cli.id AS clientId,
+ cli.name AS clientName,
+ cli.clientNum,
+ cli.address,
+ cli.phone,
+ cli.idCard,
+ his.waterDuration
+ FROM se_client cli
+ inner join (
+ SELECT client_id, SUM(cl_this_time) AS waterDuration
+ FROM rm_open_close_valve_history
+ <where>
+ <if test="timeStart != null and timeStart != '' and timeStop != null and timeStop != ''">
+ op_dt BETWEEN #{timeStart} AND #{timeStop}
+ </if>
+ </where>
+ group by client_id
+ <if test="waterDuration != null and waterDuration > 0">
+ having waterDuration > #{waterDuration}
+ </if>
+ ) as his on his.client_id = cli.id
+ ORDER BY cli.id
+ <trim prefix="limit ">
+ <if test="start != null and count != null">
+ #{start,javaType=Integer,jdbcType=INTEGER}, #{count,javaType=Integer,jdbcType=INTEGER}
+ </if>
+ </trim>
+ </select>
+ <!-- 2025-07-29 涓嬮潰SQL鏌ヨ澶參锛屽簾寮� -->
+ <select id="_getLargeWaterDurationClients" resultType="com.dy.pipIrrGlobal.voSt.VoClient">
SELECT cli.id AS clientId,
cli.name AS clientName,
cli.clientNum,
@@ -1221,6 +1365,24 @@
<!--鑾峰彇鎸囧畾鏃堕棿娈靛唴鐢ㄦ按鏃堕暱浣庝簬鎸囧畾鍊肩殑鍐滄埛鏁伴噺-->
<select id="getSmallWaterDurationClientsCount" resultType="java.lang.Long">
+ SELECT COUNT(*)
+ FROM se_client cli
+ inner join (
+ SELECT client_id, SUM(cl_this_time) AS waterDuration
+ FROM rm_open_close_valve_history
+ <where>
+ <if test="timeStart != null and timeStart != '' and timeStop != null and timeStop != ''">
+ op_dt BETWEEN #{timeStart} AND #{timeStop}
+ </if>
+ </where>
+ group by client_id
+ <if test="waterDuration != null and waterDuration > 0">
+ having waterDuration < #{waterDuration}
+ </if>
+ ) as his on his.client_id = cli.id
+ </select>
+ <!-- 2025-07-29 涓嬮潰SQL鏌ヨ澶參锛屽簾寮� -->
+ <select id="_getSmallWaterDurationClientsCount" resultType="java.lang.Long">
SELECT COUNT(*) AS recordCount
FROM se_client cli
<where>
@@ -1235,6 +1397,36 @@
<!--鑾峰彇鎸囧畾鏃堕棿娈靛唴鐢ㄦ按鏃堕暱浣庝簬鎸囧畾鍊肩殑鍐滄埛-->
<select id="getSmallWaterDurationClients" resultType="com.dy.pipIrrGlobal.voSt.VoClient">
+ SELECT cli.id AS clientId,
+ cli.name AS clientName,
+ cli.clientNum,
+ cli.address,
+ cli.phone,
+ cli.idCard,
+ his.waterDuration
+ FROM se_client cli
+ inner join (
+ SELECT client_id, SUM(cl_this_time) AS waterDuration
+ FROM rm_open_close_valve_history
+ <where>
+ <if test="timeStart != null and timeStart != '' and timeStop != null and timeStop != ''">
+ op_dt BETWEEN #{timeStart} AND #{timeStop}
+ </if>
+ </where>
+ group by client_id
+ <if test="waterDuration != null and waterDuration > 0">
+ having waterDuration < #{waterDuration}
+ </if>
+ ) as his on his.client_id = cli.id
+ ORDER BY cli.id
+ <trim prefix="limit ">
+ <if test="start != null and count != null">
+ #{start,javaType=Integer,jdbcType=INTEGER}, #{count,javaType=Integer,jdbcType=INTEGER}
+ </if>
+ </trim>
+ </select>
+ <!-- 2025-07-29 涓嬮潰SQL鏌ヨ澶參锛屽簾寮� -->
+ <select id="_getSmallWaterDurationClients" resultType="com.dy.pipIrrGlobal.voSt.VoClient">
SELECT cli.id AS clientId,
cli.name AS clientName,
cli.clientNum,
diff --git a/pipIrr-platform/pipIrr-web/pipIrr-web-statistics/src/main/java/com/dy/pipIrrStatistics/client/ClientCtrl.java b/pipIrr-platform/pipIrr-web/pipIrr-web-statistics/src/main/java/com/dy/pipIrrStatistics/client/ClientCtrl.java
index 489e3e6..b68af91 100644
--- a/pipIrr-platform/pipIrr-web/pipIrr-web-statistics/src/main/java/com/dy/pipIrrStatistics/client/ClientCtrl.java
+++ b/pipIrr-platform/pipIrr-web/pipIrr-web-statistics/src/main/java/com/dy/pipIrrStatistics/client/ClientCtrl.java
@@ -141,6 +141,9 @@
return BaseResponseUtils.buildFail(Objects.requireNonNull(bindingResult.getFieldError()).getDefaultMessage());
}
try {
+ if(qo.getAmountSpent() == null){
+ qo.setAmountSpent(0.0D);
+ }
return BaseResponseUtils.buildSuccess(clientSv.getLargeAmountSpentClients(qo));
} catch (Exception e) {
log.error("鑾峰彇寮�鍗¤褰曞紓甯�", e);
@@ -161,6 +164,9 @@
return BaseResponseUtils.buildFail(Objects.requireNonNull(bindingResult.getFieldError()).getDefaultMessage());
}
try {
+ if(qo.getAmountSpent() == null){
+ qo.setAmountSpent(0.0D);
+ }
return BaseResponseUtils.buildSuccess(clientSv.getSmallAmountSpentClients(qo));
} catch (Exception e) {
log.error("鑾峰彇璁板綍寮傚父", e);
@@ -181,6 +187,9 @@
return BaseResponseUtils.buildFail(Objects.requireNonNull(bindingResult.getFieldError()).getDefaultMessage());
}
try {
+ if(qo.getWaterDuration() == null){
+ qo.setWaterDuration(0);
+ }
return BaseResponseUtils.buildSuccess(clientSv.getLargeWaterDurationClients(qo));
} catch (Exception e) {
log.error("鑾峰彇寮�鍗¤褰曞紓甯�", e);
@@ -201,6 +210,9 @@
return BaseResponseUtils.buildFail(Objects.requireNonNull(bindingResult.getFieldError()).getDefaultMessage());
}
try {
+ if(qo.getWaterDuration() == null){
+ qo.setWaterDuration(0);
+ }
return BaseResponseUtils.buildSuccess(clientSv.getSmallWaterDurationClients(qo));
} catch (Exception e) {
log.error("鑾峰彇寮�鍗¤褰曞紓甯�", e);
--
Gitblit v1.8.0