MySQL 每天计数类型

Question

所以我有一张 table

| date | name | type |

我想转换数据以制作一个表格来计算每个日期出现的每种类型的名称的数量。

| date | count(type_a) | count(type_b) | ....

我有两种类型，所以我这样做了

SELECT t0.date, type_a, type_b 
FROM
(SELECT DISTINCT date from my_table) AS t0
LEFT JOIN (SELECT date, COUNT(name) AS type_a
           FROM my_table
           WHERE type='type_a'
           GROUP BY date) AS t1
    ON t0.date = t1.date
LEFT JOIN (SELECT date, COUNT(name) AS type_b
           FROM my_table
           WHERE type='type_b'
           GROUP BY date) AS t2 
    ON t0.date = t2.date

但我觉得这是一种矫枉过正，并且对于更多类型来说变得过于重复。

假设我有 4 种类型。我确定我还缺少更好的东西。

Answer 1

您可以使用 case 语句有条件地聚合:

select date,
       sum(case when type = 'type_a' then 1 else 0 end) as type_a,
       sum(case when type = 'type_b' then 1 else 0 end) as type_b
from   tbl
group by date

为其他类型添加额外的 case 语句。

上面显示了没有类型 a 或类型 b 值的日期为零，这与外部联接的逻辑相匹配。如果你根本不想要这样的日期，你可以添加这个 where 子句:

where  type in ('type_a','type_b')