mysql - 计算列中的值

Question

我想从中得到

+-----------+--------+---------+
| name      | color  | species |
+-----------+--------+---------+
| Tom       | grey   | cat     |
| Jerry     | brown  | mouse   |
| Sylvester | black  | cat     |
| Tweety    | yellow | bird    |
| Garfield  | orange | cat     |
| Odie      | yellow | dog     |
| Bugs      | grey   | rabbit  |
| Daffy     | black  | duck    |
+-----------+--------+---------+

到此

+---------+------+-------+-------+--------+--------+
| species | grey | brown | black | yellow | orange |
+---------+------+-------+-------+--------+--------+
| cat     |    1 |     0 |     1 |      0 |      1 |
| mouse   |    0 |     1 |     0 |      0 |      0 |
| bird    |    0 |     0 |     0 |      1 |      0 |
| dog     |    0 |     0 |     0 |      1 |      0 |
| rabbit  |    1 |     0 |     0 |      0 |      0 |
| duck    |    0 |     0 |     1 |      0 |      0 |
+---------+------+-------+-------+--------+--------+

在 MySQL 中。

如果我提前知道颜色，我就知道该怎么做。

SELECT 
species,
COUNT(DISTINCT if (color='grey', name, null)) AS 'grey',
COUNT(DISTINCT if (color='brown', name, null)) AS 'brown',
COUNT(DISTINCT if (color='black', name, null)) AS 'black',
[...]
FROM animals
GROUP BY species

是否有一种方法可以实现此目的，而无需为可能出现的每种颜色添加新的 COUNT block ？如果颜色的名称不能用作列名称，就会出现问题。

SQL 中最简单的方法是这样的

SELECT
species, color, COUNT(name)
FROM animals
GROUP BY species, color

但这需要更多代码来转换客户端的数据。

Answer 1

您正在寻找的东西称为“枢轴”。如果您曾经在 Excel 中使用过“数据透视表”，那么这是一个非常相似的概念。基本上，您将自动发现行和列名称，然后填写表格。

结账 http://www.sqlservertutorial.net/sql-server-basics/sql-server-pivot/和“动态数据透视表”部分。

我不建议在应用程序的 SQL 端进行这种转换。我个人的意见是让数据库中的所有查询和数据操作保持完全定义(而不是动态定义)。