mysql根据值显示列

I have a question about querying data. When I want to get the specific colors(red and blue) of the selling items and its count from the following data

我有一个关于查询数据的问题。当我想从以下数据中获取销售商品的特定颜色（红色和蓝色）及其数量时

id  item    color
1   card    red
2   card    red
3   card    blue
4   card    blue
5   light   red
6   light   blue
7   light   yellow
8   cup red
9   cup red
10  cup blue

into this format

进入这种格式

item    red blue
card    2   2
light   1   1
cup     2   0

I started from this.

我从这开始。

select item ,color, count(*) from shops where color in ('red','blue') group by item , color

But when I tried to separate "red","blue" into 2 columns. I have no idea how to do it. I would appreciate if someone could give some keywords or direction for this problem.

但是当我试图将“红色”，“蓝色”分成2列时。我不知道该怎么做。如果有人能为这个问题提供一些关键词或方向，我将不胜感激。

2 个解决方案

#1

You could use this:

你可以用这个：

SELECT item, 
    SUM(CASE WHEN color = 'red' THEN 1 ELSE 0 END) AS red,
    SUM(CASE WHEN color = 'blue' THEN 1 ELSE 0 END) AS blue
FROM table_name
GROUP BY item;

#2

Try with two counts, with a case inside them:

尝试两个计数，其中包含一个案例：

select  item,
        count(case when color = 'red' then item end) as red,
        count(case when color = 'blue' then item end) as blue
from    shops
group by item

#1