SQL按一个列分组，计算另一个列的条目数。

Question

我正在使用sqlite3数据库，其中包含如下表格：

|name   |action     |
-------------------------
|john   |run        |
|jim    |run        |
|john   |run        |
|john   |jump       |
|jim    |jump       |
|jim    |jump       |
|jim    |dive       |

我希望能够得到如下输出结果。

|name   |run    |jump   |dive   |
---------------------------------
|john   |2  |1  |0  |
|jim    |1  |2  |1  |

最接近的是这个，但我想要像上面那样只有一行。

SELECT name, action, COUNT(name)
FROM table
GROUP BY name, action

|name   |action |COUNT(name)    |
|john   |run    |2      |
|john   |jump   |1      |
|jim    |run    |1      |
|jim    |jump   |2      |
|jim    |dive   |1      |

此外，在查询中我还需要一些WHERE语句。

我是否太天真以为这会起作用？

- valentine

---

你所需要的是数据透视表。我在msdn上找到了一个例子。 - Mridul Kashatria

是的，他需要对数据进行PIVOT操作，但在sqlite中没有像SQL Server中那样的PIVOT或UNPIVOT运算符，因此没有简单的方法来完成它。 - Waleed Al-Balooshi

Answer 1

您也可以通过使用总和聚合和类似以下的CASE条件来实现所需的内容：

SELECT name, 
       sum(CASE WHEN action = 'run' THEN 1 END) as run,
       sum(CASE WHEN action = 'jump' THEN 1 END) as jump,
       sum(CASE WHEN action = 'dive' THEN 1 END) as dive
FROM table
GROUP BY name

每次添加额外的操作，您仍然需要更改查询。

- Waleed Al-Balooshi

Answer 2

我对SQLLite不是很熟悉，但我想你可以使用子查询或临时表。在mssql中，你可以这样写：

select Name, 
     (select count(*) from table as t1 where t1.Name = table.Name and t1.Action = 'run') as Run, 
(select count(*) from table as t1 where t1.Name = table.Name and t1.Action = 'dive') as dive, 
(select count(*) from table as t1 where t1.Name = table.Name and t1.Action = 'jump') as run
 from table 

但是每次添加另一个操作类型时，这将需要重新编写。您应该在表上添加索引以加快速度。但是首先使用“真实”数据检查查询计划。

- Richard L

---

非常好用。我意识到模式完全有缺陷，但我正在使用 csv 并在内存中创建 sqlite 数据库，然后使用它来进行查询。因此，我在进行静态文本输入时受到了一定的限制。谢谢 Richard。 - valentine

Answer 3

在Oracle数据库中，您可以编写以下查询来显示所需的解决方案：

select * from table_name
pivot (count(*) for action in ('run','jump','drive'))

这将会得到所期望的输出结果。

- pratik garg

Answer 4

你想要做的是交叉制表。通常在Excel和其他电子表格软件中，这被称为数据透视表。

我找到了一篇博客文章，可以帮助你使用SQL完成这个功能。请查看在SQLite3和MySQL中使用数据透视表技巧

- Mridul Kashatria

---

嘿，mridkash，这正是我想要的，感谢你提供的链接和信息（教人钓鱼的道理）。测试表明，这种方法执行速度更快。 - valentine