mysql - 多个列中具有不同值的多个计数

Question

假设我有一个这样的表:

+------+--------+------+
|Color |Shape   |Type  |
+------+--------+------+
|red   |square  |puzzle|
|red   |circle  |puzzle|
|green |star    |puzzle|
|green |circle  |puzzle|
|blue  |square  |puzzle|
|blue  |star    |puzzle|
|blue  |triangle|puzzle|
+------+--------+------+

我想得到如下所示的结果:

+--------+---------+-----------+
|redCount|blueCount|squareCount|
+--------+---------+-----------+
|2       |3        |2          |
+--------+---------+-----------+

我如何更改以下查询才能真正起作用，或者根本不能以这种方式完成？

SELECT COUNT(Color="blue") AS blueCount,
 COUNT(Color="red") AS redCount,
 COUNT(Shape="square") AS squareCount
 FROM toys
 WHERE Type = "puzzle";

Answer 1

您也可以尝试使用 IF

SELECT COUNT(IF(`Color` = 'blue', `Color`, null)) AS `blueCount`
     , COUNT(IF(`Color` = 'red', `Color`, null)) AS `redCount`
     , COUNT(IF(`Shape` = 'square', `Shape`, null)) AS `squareCount`
FROM `toys`
WHERE `Type` = 'puzzle';

或案例:

SELECT COUNT(CASE WHEN `Color` = 'blue' THEN `Color` END) AS `blueCount`
     , COUNT(CASE WHEN `Color` = 'red' THEN `Color` END) AS `redCount`
     , COUNT(CASE WHEN `Shape` = 'square' THEN `Shape` END) AS `squareCount`
FROM `toys`
WHERE `Type` = 'puzzle';