优化后的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

优化点:

  1. 将不必要的括号去除,简化WHERE子句。
  2. 将多个条件合并为一行,提高可读性。
  3. 优化LEFT JOIN语句的顺序,将最小的表放在最前面。
  4. 删除多余的括号,简化GROUP BY子句。
SQL语句优化:提高性能和可读性

原文地址: https://www.cveoy.top/t/topic/o6bq 著作权归作者所有。请勿转载和采集!

免费AI点我,无需注册和登录