MYSQL 查询所需总计

Question

您好，我正在寻找一种解决方案，以解决我无法理解如何获得查询中列的总体总计的问题。

此查询获取工程师姓名以及他们所拥有的超出 SLA 的作业数量，即作业应该完成的数据已经过去，而作业仍需完成。

SELECT Engineer,Job_Status,COUNT(*) as 'Out Of SLA' 
FROM import 
WHERE (Job_Status = 'P' or Job_Status='P2' or Job_Status='P8') 
      and (isnull(Job_Completed_Date) 
           or Job_Completed_Date='0000-00-00') 
           and (Job_SLA_Due_Date < CURDATE()
          ) 
GROUP BY import.Engineer,Job_Status

上面的代码从导入表中生成以下结果。

+----------------+------------+------------+
|    Engineer    | Job_Status | Out of SLA |
+----------------+------------+------------+
| Andy Beeres    | P          |         15 |
| Andy Broad     | P          |          4 |
| Darren Goodwin | P          |          6 |
+----------------+------------+------------+

我希望能够显示“超出 SLA”列的总数以及表格数据的其余部分(如果这有意义的话，如下表所示)。

| Engineer      | Job_Status    | Out of SLA    |
|-------------  |------------   |------------   |
| Andy Beeres   |      P        |         14    |
|               |     P2        |          3    |
|               |     P8        |          1    |
| Total         |               |         18    |
| Andy Broad    | P             |         12    |
|               | P2            |          2    |
| Total         |               |         14    |
| Grand Total   |               |         32    |

问候

艾伦

Answer 1

使用with rollup和group by来获取total_sla

根据MySql Docs :

The GROUP BY clause permits a WITH ROLLUP modifier that causes summary output to include extra rows that represent higher-level (that is, super-aggregate) summary operations. ROLLUP thus enables you to answer questions at multiple levels of analysis with a single query.

SELECT Engineer,Job_Status,COUNT(*) as 'Out Of SLA' 
FROM import 
WHERE (Job_Status = 'P' or Job_Status='P2' or Job_Status='P8') 
  and (isnull(Job_Completed_Date) 
       or Job_Completed_Date='0000-00-00') 
       and (Job_SLA_Due_Date < CURDATE()
      ) 
GROUP BY import.Engineer,Job_Status WITH ROLLUP