我有3个类别(不同等级的以下等级):低于SLA,Near SLA,Over SLA,我尝试对数据进行计数,但结果未按其类别进行汇总

这是我的查询:

SELECT
    B.province AS 'PROVINCE',
    CASE
        WHEN TIMEDIFF(A.deli_time, A.create_time) < '20:00:00' THEN COUNT(TIMEDIFF(A.deli_time, A.create_time))
    END AS 'Below SLA',
    CASE
        WHEN (TIMEDIFF(A.deli_time, A.create_time) > '20:00:00') AND (TIMEDIFF(A.deli_time, A.create_time) < '24:00:00') THEN COUNT(TIMEDIFF(A.deli_time, A.create_time))
    END AS 'NEAR SLA',
    CASE
        WHEN TIMEDIFF(A.deli_time, A.create_time) > '24:00:00' THEN COUNT(TIMEDIFF(A.deli_time, A.create_time))
    END AS 'OVER SLA'
FROM
    deli_order A
INNER JOIN
    deli_order_delivery B on A.id = B.order_id
WHERE
    (DATE(A.plat_create_time) BETWEEN '2019-03-30' AND'2019-04-07') AND (TIMEDIFF(A.deli_time, A.create_time) IS NOT NULL)
GROUP BY B.province;


这就是我得到的结果:

Province       | Below SLA | Near SLA | Over SLA
------------------------------------------------
Bali                30          Null      Null


“巴厘岛”的所有记录总数为30,但实际上分为19个低于SLA,5个接近SLA和6个高于SLA。

我应该更改查询什么?

最佳答案

SELECT
    B.province AS 'PROVINCE',
    SUM(CASE
        WHEN TIMEDIFF(A.deli_time, A.create_time) < '20:00:00' THEN 1
    END) AS 'Below SLA',


在每种情况下都放一个汇总函数。我只在一种情况下做过,都是一样的。

关于mysql - 如何对计数数据进行分组,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/55780722/

10-12 18:13