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

Question

我必须将下表命名为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 |

我相信最好的前进方式是使用数据透视表，有人可以给我一些建议吗？

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;