mysql - 从单列计数并将其分成 2 列

Question

这是我的数据库中的“评级”表。

attraction      customer      rate
------------------------------------
attrac1         cust1         like
attrac2         cust1         dislike 
attrac1         cust2         like

我应该写什么 SQL 使输出变成这样

attraction      like       dislike
----------------------------------
attrac1           2           0
attrac2           0           1

我试过这个

SELECT a_id,
(SELECT COUNT(rate) FROM rating WHERE rate = 'like') as 'Like',
(SELECT COUNT(rate) FROM rating WHERE rate = 'dislike') as 'Dislike'
FROM rating

但我无法得到我想要的结果。

Answer 1

这是一种方法:

SELECT a_id,
    SUM(Case when rate='like' then 1 else 0 end) as 'Like',
    SUM(Case when rate='dislike' then 1 else 0 end) as 'Dislike'
FROM rating
Group BY A_id