UPDATE td_olt_fault_order_info a LEFT JOIN ( SELECT a.eparchy_name, SUM(a.duration_minute > 15) count_times, alarm_month, monthly_order_count, a.area FROM ( SELECT CASE WHEN eparchy_name LIKE '%市%' THEN SUBSTRING(eparchy_name, 1, LOCATE('市', eparchy_name) - 1) ELSE eparchy_name END as eparchy_name, SUBSTRING(TRIM(SUBSTRING(fault_occurrence_area, LOCATE('市', fault_occurrence_area) + 1)), 1) fault_area, SUBSTRING(TRIM(SUBSTRING(fault_occurrence_area, LOCATE('市', fault_occurrence_area) + 1)), 1, 2) area, fault_occurrence_area, work_order_number, network_element_name, alarm_title, area_name, MONTH(alarm_creation_time) AS alarm_month, alarm_creation_time, alarm_clearance_time, CASE -- 如果创建时间和清除时间在同一天 WHEN SUBSTRING(alarm_creation_time, 1, 10) = SUBSTRING(alarm_clearance_time, 1, 10) THEN ROUND((TIMESTAMPDIFF(SECOND, CASE WHEN HOUR(STR_TO_DATE(alarm_creation_time, '%Y-%m-%d %H:%i:%s')) BETWEEN 0 AND 5 THEN CONCAT(SUBSTRING(alarm_creation_time, 1, 10), ' 06:00:00') ELSE alarm_creation_time END, CASE WHEN HOUR(STR_TO_DATE(alarm_clearance_time, '%Y-%m-%d %H:%i:%s')) BETWEEN 0 AND 5 THEN CONCAT(SUBSTRING(alarm_clearance_time, 1, 10), ' 00:00:00') ELSE alarm_clearance_time END ) / 60), 2) -- 如果创建时间和清除时间不在同一天 ELSE -- 计算创建当天的持续时间 (SELECT ROUND((TIMESTAMPDIFF(SECOND, CASE WHEN HOUR(STR_TO_DATE(alarm_creation_time, '%Y-%m-%d %H:%i:%s')) BETWEEN 0 AND 5 THEN CONCAT(SUBSTRING(alarm_creation_time, 1, 10), ' 06:00:00') ELSE alarm_creation_time END, CONCAT(SUBSTRING(alarm_creation_time, 1, 10), ' 23:59:59' {
"msg": "\r\n### Error updating database. Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/ 60), 2) AS duration_minute,\n COUNT(*) OVER (PARTITION BY eparchy_name, ' at line 60\r\n### The error may involve com.asiainfo.infinity.file.mapper.OltFaultOrderMapper.countOltExceedFifteenMin-Inline\r\n### The error occurred while setting parameters\r\n### SQL: UPDATE td_olt_fault_order_info a left JOIN ( select a.eparchy_name,sum(a.duration_minute > 15) count_times ,alarm_month ,monthly_order_count from ( SELECT CASE WHEN eparchy_name LIKE '%市%' THEN SUBSTRING(eparchy_name, 1, LOCATE('市', eparchy_name) - 1) ELSE eparchy_name END as eparchy_name, SUBSTRING(TRIM(SUBSTRING(fault_occurrence_area, LOCATE('市', fault_occurrence_area) + 1)),1) fault_area, SUBSTRING(TRIM(SUBSTRING(fault_occurrence_area, LOCATE('市', fault_occurrence_area) + 1)),1,2) area, fault_occurrence_area, work_order_number, network_element_name, alarm_title, area_name, MONTH(alarm_creation_time) AS alarm_month,-- 当前月份 alarm_creation_time, alarm_clearance_time, -- 调整创建时间:0-6点算6点 CASE -- 如果创建时间和清除时间在同一天 WHEN SUBSTRING(alarm_creation_time, 1, 10) = SUBSTRING(alarm_clearance_time, 1, 10) THEN ROUND((TIMESTAMPDIFF(SECOND, CASE WHEN HOUR(STR_TO_DATE(alarm_creation_time, '%Y-%m-%d %H:%i:%s')) BETWEEN 0 AND 5 THEN CONCAT(SUBSTRING(alarm_creation_time, 1, 10), ' 06:00:00') ELSE alarm_creation_time END, CASE WHEN HOUR(STR_TO_DATE(alarm_clearance_time, '%Y-%m-%d %H:%i:%s')) BETWEEN 0 AND 5 THEN CONCAT(SUBSTRING(alarm_clearance_time, 1, 10), ' 00:00:00') ELSE alarm_clearance_time END ) / 60), 2) -- 如果创建时间和清除时间不在同一天 ELSE -- 计算创建当天的持续时间 (SELECT ROUND((TIMESTAMPDIFF(SECOND, CASE WHEN HOUR(STR_TO_DATE(alarm_creation_time, '%Y-%m-%d %H:%i:%s')) BETWEEN 0 AND 5 THEN CONCAT(SUBSTRING(alarm_creation_time, 1, 10), ' 06:00:00') ELSE alarm_creation_time END, CONCAT(SUBSTRING(alarm_creation_time, 1, 10), ' 23:59:59') ) / 60), 2)) + -- 计算清除当天的持续时间(排除0-6点) (SELECT ROUND((TIMESTAMPDIFF(SECOND, CONCAT(SUBSTRING(alarm_clearance_time, 1, 10), ' 06:00:00'), CASE WHEN HOUR(STR_TO_DATE(alarm_clearance_time, '%Y-%m-%d %H:%i:%s')) BETWEEN 0 AND 5 THEN CONCAT(SUBSTRING(alarm_clearance_time, 1, 10), ' 00:00:00') ELSE alarm_clearance_time END ) / 60), 2)) + -- 计算中间整天的持续时间(每天18小时,排除0-6点) -- 添加GREATEST函数确保不会计算负数 (SELECT GREATEST(0, (DATEDIFF(SUBSTRING(alarm_clearance_time, 1, 10), SUBSTRING(alarm_creation_time, 1, 10)) - 1)) * 18 * 60) END ) / 60), 2) AS duration_minute, COUNT(*) OVER (PARTITION BY eparchy_name, MONTH(alarm_creation_time), work_order_number) AS monthly_order_count -- 窗口函数 统计 以 FROM td_olt_fault_order WHERE alarm_title IN ( SELECT PARAM_VALUE FROM td_param WHERE PARAM_TYPE = 'KPI_SWCZWG_GZYJDB_GJBT' AND PARAM_VALUE2 = 'OLT退服' ) AND NOT ( SUBSTRING(alarm_creation_time, 1, 10) = SUBSTRING(alarm_clearance_time, 1, 10) AND alarm_creation_time >= CONCAT(SUBSTRING(alarm_creation_time, 1, 10), ' 00:00:00') AND alarm_clearance_time < CONCAT(SUBSTRING(alarm_clearance_time, 1, 10), ' 06:00:00') ) AND NOT (alarm_creation_time IN ('', '0000-00-00 00:00:00') OR alarm_clearance_time IN ('', '0000-00-00 00:00:00')) AND stat_month = ? ) a WHERE a.eparchy_name != '太原' AND CASE -- 忻州的筛选条件 WHEN eparchy_name = '忻州' THEN (fault_area IN ('郊区', '忻府区')) OR (fault_area is NULL AND area_name IN ('忻州市分公司算网运营交付中心综合接入网格', '忻州市分公司云网运营交付中心综合运营网格1', '忻州市分公司云网运营交付中心综合运营网格2')) WHEN eparchy_name = '大同' THEN (fault_area IN ('平旺区', '城区', '南郊区')) OR (fault_area is null and ( -- 主送区县筛选条件 area_name LIKE '%接入%' OR area_name LIKE '%平旺%' OR area_name LIKE '%南郊%' OR area_name LIKE '%城区%') ) WHEN eparchy_name = '晋城' THEN (fault_area IN ('城区')) OR ( fault_area IS NULL AND area_name IN ('晋城市综合运营网格', '晋城市分公司网络保障中心综合监看责任单元')) WHEN eparchy_name = '晋中' THEN (fault_area IN ('榆次区')) OR (fault_area IS NULL AND area_name IN ('晋中市分公司云网运营交付中心综合运营交付网格1', '晋中市分公司云网运营交付中心云网承载网格')) WHEN eparchy_name = '临汾' THEN (fault_area IN ('尧都区', '开发区')) OR (fault_area IS NULL AND (area_name LIKE '%市区%' or area_name LIKE '%尧都区%' or area_name LIKE '%开发区%') ) WHEN eparchy_name = '朔州' THEN (fault_area IN ('朔城区')) OR (fault_area IS NULL AND area_name LIKE ('%工程局朔州维护中心%')) WHEN eparchy_name = '吕梁' THEN (fault_area IN ('离石区')) OR (fault_area IS NULL AND area_name LIKE ('%综合运营网格%')) WHEN eparchy_name = '阳泉' THEN (fault_area IN ('城区')) OR (fault_area IS NULL AND (area_name LIKE ('%阳煤代维%') or area_name LIKE ('阳泉市云网运营交付中心市区综合运营网格'))) WHEN eparchy_name = '长治' THEN (fault_area IN ('潞州区', '高新区')) OR (fault_area is null and (area_name LIKE ('%接入网维护组%') or area_name LIKE ('%城北维护组%') or area_name LIKE ('%城南维护组%') or area_name LIKE ('%郊区维护组%') or area_name LIKE ('%线路维护中心%') or area_name IN ('长治市分公司云网运营交付中心云网承载网格(数据)城南维护组', '长治市分公司云网运营交付中心故县综合运营网格', '长治市分公司云网运营交付中心马厂综合运营网格'))) WHEN eparchy_name = '运城' THEN (fault_area IN ('盐湖区')) OR (fault_area IS NULL AND (area_name in ('运城市分公司云网运营交付中心综合运营网格1', '运城市分公司云网运营交付中心综合运营网格2'))) WHEN eparchy_name = '太原' AND area_name LIKE ('%接入网%') THEN (fault_area IN ('小店区', '万柏林区', '迎泽区', '尖草坪区', '晋源区', '杏花岭区', '综改区')) OR (fault_area IS NULL AND (network_element_name LIKE ('%小店%') OR network_element_name LIKE ('%万柏林%') OR network_element_name LIKE ('%迎泽%') OR network_element_name LIKE ('尖草坪') OR network_element_name LIKE ('晋源') OR network_element_name LIKE ('杏花岭') OR network_element_name LIKE ('综改'))) ELSE FALSE END group by a.eparchy_name ,alarm_month,monthly_order_count ) b ON a.EPARCHY_NAME = b.eparchy_name SET ATTR1 = IF( COALESCE(b.count_times,0) > 0, IF( b.monthly_order_count > 1, IF(20 - (b.monthly_order_count) <= 0, 0, 20 - (b.monthly_order_count)), IF(20 - COALESCE(b.count_times,0) <= 0, 0, 20 - COALESCE(b.count_times,0)) ), 20 ) WHERE a.STAT_MONTH= ? AND a.eparchy_name != '太原'\r\n### Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/ 60), 2) AS duration_minute,\n COUNT(*) OVER (PARTITION BY eparchy_name, ' at line 60\n; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/ 60), 2) AS duration_minute,\n COUNT(*) OVER (PARTITION BY eparchy_name, ' at line 60",
"code": 500
}