统计SQL中某一列的特定值并根据唯一标识符按行显示 [英] Count specific values of a column in SQL and display in row according unique identifier

问题描述

我必须使用下表命名的结果:

I have to following table named results:

Position | Country
-------------------
1        | US
2        | Italy
3        | France
1        | US
2        | France
3        | Italy

我想创建以下内容

Country | 1 | 2 | 3 |
---------------------
US      | 2 | 0 | 0 |
Italy   | 0 | 1 | 1 |
France  | 0 | 1 | 1 |

我相信前进的最佳方式是使用数据透视表，有人可以就如何进行操作提供建议吗?

I believe the best way to move forward is using pivot tables, can someone give me advice on how to proceed?

推荐答案

可以使用条件聚合:

select country,
       sum(case when position = 1 then 1 else 0 end) as pos_1,
       sum(case when position = 2 then 1 else 0 end) as pos_2,
       sum(case when position = 3 then 1 else 0 end) as pos_3
from t
group by country
order by sum(pos) asc;

这篇关于统计SQL中某一列的特定值并根据唯一标识符按行显示的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！