SQL语句优化:提高性能和可读性
优化后的SQL语句如下:
SELECT SUM(COALESCE(s.ThisDischargeCapacity, 0.0)) AS ThisDischargeCapacity,
SUM(COALESCE(s.ThisChargingCapacity, 0.0)) AS ThisChargingCapacity,
EXTRACT(year FROM s.RecordTime) AS Year,
EXTRACT(month FROM s.RecordTime) AS Month,
EXTRACT(day FROM s.RecordTime) AS Day
FROM StoredEnergy_History AS s
LEFT JOIN Equipment_Info AS e ON s.EquipmentId = e.Id
LEFT JOIN Equipment_UseType AS e0 ON e.EquipmentUseTypeId = e0.Id
WHERE s.Deleted = FALSE
AND e.Deleted = FALSE
AND e.IsEnable
AND e0.Deleted = FALSE
AND e0.IsEnable
AND e.EnterpriseId = '32a6060f-0914-11ee-84b7-6c3c8c6a422e'
AND e0.UseTypeName = '总储能'
AND (
(s.RecordTime BETWEEN @__startTimeYear_1 AND @__endTimeYear_2) OR
(s.RecordTime BETWEEN @__startTimeMonth_1 AND @__endTimeMonth_2) OR
(s.RecordTime BETWEEN @__startTimeDay_1 AND @__endTimeDay_2)
)
GROUP BY Year, Month, Day
优化点:
- 将不必要的括号去除,简化WHERE子句。
- 将多个条件合并为一行,提高可读性。
- 优化LEFT JOIN语句的顺序,将最小的表放在最前面。
- 删除多余的括号,简化GROUP BY子句。
原文地址: https://www.cveoy.top/t/topic/o6bq 著作权归作者所有。请勿转载和采集!