sql - 如何在 MySQL 中按工作日对总和进行分组？

Question

我有一个这样的表:

id | timestamp  | type  
-----------------------
1    2010-11-20   A
2    2010-11-20   A
3    2010-11-20   B
4    2010-11-21   A
5    2010-11-21   C
6    2010-11-27   B

我需要计算每种类型的行数，按工作日分组；像这样:

weekday |  A  |  B  |  C 
--------------------------
5          2     2     0    -- the B column equals 2 because nov 20 and nov 27 are saturday
6          1     0     1

最简单的解决方案是什么？
我不介意使用 View 、变量、子查询等。

Answer 1

使用:

  SELECT WEEKDAY(t.timestamp) AS weekday,
         SUM(CASE WHEN t.type = 'A' THEN 1 ELSE 0 END) AS a,
         SUM(CASE WHEN t.type = 'B' THEN 1 ELSE 0 END) AS b,
         SUM(CASE WHEN t.type = 'C' THEN 1 ELSE 0 END) AS c
    FROM YOUR_TABLE t
GROUP BY WEEKDAY(t.timestamp)