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 语句。

我晚上睡觉时是否认为这会起作用？

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

每次添加其他操作时，您仍然需要更改查询。

Answer 2

您想要做的事情称为交叉制表。通常，这在 Excel 和其他电子表格软件中作为称为数据透视表的功能提供。

我找到了一篇博客文章，它将帮助您使用 SQL 来完成此操作。查看 pivot-table-hack-in -sqlite3-和-mysql

Answer 3

我不太了解 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 

但是每次添加另一种操作类型时都需要重写。您可能应该添加一个索引来提高表的速度。但首先用“真实”数据检查查询计划。

Answer 4

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

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

这将给出所需的输出..