SQL查询需求：计算每月preventiva与corretiva的占比百分比

我来帮你调整SQL语句，实现你需要的每月预防性（preventiva）和纠正性（corretivas）工单占比的计算。你的原查询已经算出了两类工单的数量和总数，只需要在此基础上添加百分比计算逻辑即可，同时还要注意避免除以0的报错情况。

核心问题分析 #

你的原SQL已经正确统计了每月的preventiva、corretivas和total数值，但缺少百分比计算。直接用两类工单数量除以总数时，如果某个月没有符合条件的工单（total=0），会触发除以0的错误，所以需要用NULLIF函数处理这种情况。

修改后的SQL语句（推荐使用CTE，更易读） #

WITH MonthlyWorkOrders AS (
    SELECT 
        MONTH(workOrderDate) AS MES, 
        SUM(CASE WHEN WorkType = '02' AND workOrderDescription = 'preventiva' THEN 1.0 ELSE 0 END) AS preventiva, 
        SUM(CASE WHEN workOrderDescription = 'CORRETIVA' THEN 1.0 ELSE 0 END) AS corretivas, 
        SUM(
            CASE WHEN WorkType = '02' AND workOrderDescription = 'preventiva' THEN 1.0 ELSE 0 END + 
            CASE WHEN workOrderDescription = 'CORRETIVA' THEN 1.0 ELSE 0 END
        ) AS total 
    FROM WorkOrder 
    WHERE YEAR(workOrderDate) = 2018 AND lastUpdateData IS NOT NULL 
    GROUP BY MONTH(workOrderDate)
)
SELECT 
    MES,
    preventiva,
    corretivas,
    total,
    -- 计算preventiva占比，保留2位小数，total为0时返回NULL（可根据需求改成0）
    ROUND(preventiva / NULLIF(total, 0) * 100, 2) AS `preventivas%`,
    -- 计算corretivas占比
    ROUND(corretivas / NULLIF(total, 0) * 100, 2) AS `corretivas%`
FROM MonthlyWorkOrders;

关键细节说明 #

1. 避免除以0错误：使用NULLIF(total, 0)，当total为0时会返回NULL，这样除法运算结果也会是NULL，不会报错。如果你希望total为0时显示0%，可以改成CASE WHEN total = 0 THEN 0 ELSE ROUND(...) END。
2. 浮点型除法：所有计数用1.0而不是1，确保除法是浮点运算，避免整数除法导致的精度丢失（比如1/2会得到0而不是0.5）。
3. 保留小数位数：ROUND(..., 2)将百分比保留两位小数，你可以根据需求调整位数（比如改成0显示整数百分比）。
4. 大小写匹配：注意workOrderDescription的大小写（原SQL里preventiva是小写，CORRETIVA是大写），如果数据库中存在大小写不一致的数据，可以用LOWER(workOrderDescription) = 'preventiva'统一匹配规则。

替代方案（不使用CTE） #

如果你的数据库不支持CTE，也可以直接在原查询中重复计算总数：

SELECT 
    MONTH(workOrderDate) AS MES, 
    SUM(CASE WHEN WorkType = '02' AND workOrderDescription = 'preventiva' THEN 1.0 ELSE 0 END) AS preventiva, 
    SUM(CASE WHEN workOrderDescription = 'CORRETIVA' THEN 1.0 ELSE 0 END) AS corretivas, 
    SUM(
        CASE WHEN WorkType = '02' AND workOrderDescription = 'preventiva' THEN 1.0 ELSE 0 END + 
        CASE WHEN workOrderDescription = 'CORRETIVA' THEN 1.0 ELSE 0 END
    ) AS total,
    ROUND(
        SUM(CASE WHEN WorkType = '02' AND workOrderDescription = 'preventiva' THEN 1.0 ELSE 0 END) 
        / NULLIF(
            SUM(
                CASE WHEN WorkType = '02' AND workOrderDescription = 'preventiva' THEN 1.0 ELSE 0 END + 
                CASE WHEN workOrderDescription = 'CORRETIVA' THEN 1.0 ELSE 0 END
            ), 0
        ) * 100, 2
    ) AS `preventivas%`,
    ROUND(
        SUM(CASE WHEN workOrderDescription = 'CORRETIVA' THEN 1.0 ELSE 0 END) 
        / NULLIF(
            SUM(
                CASE WHEN WorkType = '02' AND workOrderDescription = 'preventiva' THEN 1.0 ELSE 0 END + 
                CASE WHEN workOrderDescription = 'CORRETIVA' THEN 1.0 ELSE 0 END
            ), 0
        ) * 100, 2
    ) AS `corretivas%`
FROM WorkOrder 
WHERE YEAR(workOrderDate) = 2018 AND lastUpdateData IS NOT NULL 
GROUP BY MONTH(workOrderDate);

内容的提问来源于stack exchange，提问作者Matheus Batista