mysql - 从一个表中计算不同的行

Question

我有表t1:

id|id_title|action

表中数据:

1|1|like
2|1|like
3|1|unlike
4|2|share
5|2|share

所以我想从查询中获取下一个结果:

id|count like|count unlike|count share

1|2|1|0
2|0|0|2

我尝试使用下一个查询:

SELECT id_title, ( Select Count(id) From T1 WHERE action='like') As CountOfItems FROM T1 GROUP BY id_title

但它总是返回第一行的计数。我应该做什么？或者也许我必须更改表的结构？

Answer 1

只需使用条件聚合。

select
id_title
,sum(case when action='like' then 1 else 0 end) As Count_Like
,sum(case when action='unlike' then 1 else 0 end) As Count_Unlike
,sum(case when action='share' then 1 else 0 end) As Count_Share
FROM T1 
GROUP BY id_title