统计SQL中某列的特定值，并根据唯一标识符在行中显示

Question

我必须以下表命名的结果：

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 |

我相信最好的前进方式是使用数据透视表，有人能给我如何前进的建议吗？

Answer 1

您可以使用条件聚合：

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;