主页 / 问题 / 694501
Accepted
cruim
Asked:2020-07-20 16:52:27 +0800 CST2020-07-20 16:52:27 +0800 CST

正确分组

  • 772

有一个查询显示每小时的小时数和平均订单处理时间。

SELECT
    createdhour,
    SEC_TO_TIME(
        sum(
            TIMESTAMPDIFF(
                SECOND,
                createdtime_spec,
                first_status_change
            )
        ) / count(*)
    ) AS 'РФ'
WHERE
    createdtime BETWEEN '2017-07-01'
AND '2017-07-20'
AND department IN ('РФ')
GROUP BY
    createdhour

问题是，如何在下一列中不同地显示每小时的平均订单处理时间department？这样做了

SELECT department,
    createdhour,
    case when department = 'РФ' then
SEC_TO_TIME(
        sum(
            TIMESTAMPDIFF(
                SECOND,
                createdtime_spec,
                first_status_change
            )
        ) / count(*)
    )end AS 'РФ',
    case when department = 'ЕС' then
SEC_TO_TIME(
        sum(
            TIMESTAMPDIFF(
                SECOND,
                createdtime_spec,
                first_status_change
            )
        ) / count(*)
    )end AS 'ЕС'
from report_designer
WHERE
    createdtime BETWEEN '2017-07-01'
AND '2017-07-20'
AND department IN ('РФ','ЕС')
GROUP BY
    createdhour,department

像这样发出：

结果

数据是正确的，但是如何去掉空值呢？

mysql

1 个回答

  1. Best Answer
    SELECT createdhour,
       max(if(department='РФ',avg_time,NULL)) as `РФ`,
       max(if(department='ЕС',avg_time,NULL)) as `ЕС`
  FROM (
    SELECT createdhour, department,
           SEC_TO_TIME(
              avg(
                  TIMESTAMPDIFF(
                      SECOND,
                      createdtime_spec,
                      first_status_change
                  )
              )
           ) AS avg_time
     from report_designer
    WHERE createdtime BETWEEN '2017-07-01' AND '2017-07-20'
      AND department IN ('РФ','ЕС')
    GROUP BY createdhour, department
   ) X
 GROUP BY createdhour
    • 2

相关问题