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')
) / 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 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 = '202505'
) a
WHERE
a.eparchy_name = '太原'
AND (
CASE
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,
a.area
) b ON a.EPARCHY_NAME = b.eparchy_name
AND (b.area IS NULL OR a.DEPT_NAME REGEXP b.area)
SET
ATTR5 = 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 = '202505'
AND a.eparchy_name = '太原';
就这个sql执行报错
最新发布
本文详细介绍了一个复杂的SQL查询案例，该查询用于筛选出特定条件下的销售订单记录，涉及到DATEDIFF函数和SUBSTRING函数的综合运用。
扫一扫
5万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
