Sql:列中行值的总和

Question

我有一个有三列的表：

Guid | event | timestamp
1236| page-view | 1234
1236 | product-view | 1235
1236 | page-view | 1237
2025 | add-to-cart
2025 | purchase

我想写一个不使用case语句的查询来显示列中的事件及其出现次数的总和。我不想使用这个案例，因为事件不断增加，现在大约有200个。

我想要的输出是这样的：

GUid | page-view | product-view | add-to-cart | purchase 
1236 | 2 | 1 | 0 | 0 
2025 | 0 | 0 | 1 | 1 

Answer 1

下面是一个使用case when的解决方案。

select   Guid
,count(case event when 'page-view'    then 1 end) as 'page-view'
,count(case event when 'product-view' then 1 end) as 'product-view'
,count(case event when 'add-to-cart'  then 1 end) as 'add-to-cart'
,count(case event when 'purchase'     then 1 end) as 'purchase'
from     t
group by Guid 

产品视图添加到购物车0

Guid	页面视图	购买
1236	2	1	0
2025	0	0		1	1

SELECT  a.Guid ,
ifnull ((SELECT count (Guid ) from table_a where  event in('page-view') and Guid = a.Guid  GROUP by Guid  ),0) as [page-view]  ,
ifnull((SELECT count (Guid ) from table_a where  event in('product-view') and Guid = a.Guid  GROUP by Guid  ),0) as [product-view]  ,
ifnull((SELECT count (Guid ) from table_a where  event in('add-to-cart') and Guid = a.Guid  GROUP by Guid  ),0) as [add-to-cart]  ,         
ifnull((SELECT count (Guid ) from table_a where  event in('purchase') and Guid = a.Guid  GROUP by Guid  ),0) as [purchase ]  
FROM table_a a
GROUP by Guid