从结果中删除空值

问题描述：

我有下面这个查询。如何从查询结果中删除空值？所以只有一个行保留查询从结果中删除空值

SELECT 
    CASE 
WHEN department = 'РФ' THEN 
    avg_time 
END AS `РФ`, 
CASE 
WHEN department = 'ЕС' THEN 
    avg_time 
END AS `ЕС`, 
CASE 
WHEN department = 'Аутсорс' THEN 
    avg_time 
END AS `Аутсорс` 
FROM 
    (
     SELECT 
      department, 
      (sum(apruv)/count(*) * 100) AS avg_time 
     FROM 
      report_designer 
     WHERE TIMESTAMPDIFF(
      MINUTE, 
      createdtime_spec, 
      first_status_change 
     ) <= 15 
     GROUP BY 
      department 
    ) x

结果是

如何从查询结果中删除空值？所以只有一个行保留

这将返回不一致的值。例如。如果一个部门有'空'PO但不是'EC'空，那么应用上述变换将返回不为空的'PO'值，这将是错误的。 –

答

一种方法是使用：

SELECT 
    (SELECT (sum(apruv)/count(*) * 100) AS avg_time 
    FROM report_designer 
    WHERE TIMESTAMPDIFF(MINUTE,createdtime_spec,first_status_change) <= 15 
     AND department = 'РФ') AS `РФ`, 
    (SELECT (sum(apruv)/count(*) * 100) AS avg_time 
    FROM report_designer 
    WHERE TIMESTAMPDIFF(MINUTE,createdtime_spec,first_status_change) <= 15 
     AND department = 'EC') AS `EC`, 
    (SELECT (sum(apruv)/count(*) * 100) AS avg_time 
    FROM report_designer 
    WHERE TIMESTAMPDIFF(MINUTE,createdtime_spec,first_status_change) <= 15 
     AND department = 'Аутсорс') AS `Аутсорс`;

从结果中删除空值

相关推荐