mysql - 需要计算列中的唯一出现次数，并按日期对结果进行分组

Question

我需要计算字段中的唯一实例，然后按日期对计数进行排序，并尝试使用 SQL 在一个查询中完成这一切。

我的表格如下所示:

Date...........Defect_Code..........
2016-06-12     Machine Issue
2016-06-12     Broken
2016-06-12     null
2016-06-13     Machine Issue
2016-06-13     Machine Issue
2016-06-14     Crack
2016-06-14     Crack

我希望在计算每个 Defect_Code 的实例时按日期排序，从而得到一个看起来类似于的表格

Date............Count(Machine Issue)...Count(Broken)...Count(Crack)...Count(null)....
2016-06-12......1......................1...............0..............1..............
2016-06-13......2......................0...............0..............0..............
2016-06-14......0......................0...............3..............0..............

预先感谢您的帮助!

Answer 1

您可以通过条件聚合来执行此操作，因为 Defect_Code 的值是一致的:

SELECT Date
     , COALESCE(SUM(CASE WHEN Defect_Code = 'Machine Issue' THEN 1 END),0) AS 'Count(Machine Issue)'
     , COALESCE(SUM(CASE WHEN Defect_Code = 'Broken' THEN 1 END),0) AS 'Count(Broken)'
     , COALESCE(SUM(CASE WHEN Defect_Code = 'Crack' THEN 1 END),0) AS 'Count(Crack)'
     , COALESCE(SUM(CASE WHEN Defect_Code IS NULL THEN 1 END),0) AS 'Count(Null)'
FROM YourTable
GROUP BY Date
ORDER BY Date