如何在SQLite中使用此SQL查询创建正确的枢轴表

Question

我已经想解决了这张硬式SQL枢轴表。我找不到解决方案。

with cte as (
  select nationality, class as n
  from Fifaklasse
  group by nationality
)
select nationality,
  coalesce(sum(case when n = 'Wereldklasse' then n end), 0) as 'Wereldklasse',
  coalesce(sum(case when n = 'Topklasse'  then n end), 0) as 'Topklasse',
  coalesce(sum(case when n = 'Subtop'  then n end), 0) as 'Subtop'
from cte
group by nationality

player	nationality	Class
Messi	n1	World_class
Aguero	n1	World_class
Vidal	n2	Top_class
Pinto	n3	Subtop_class
Ronaldo	n3	World_class
Suarez	n4	World_class
Falcao	n4	Top_class

RESULT

Nationality	World_class	Top_class	Subtop_class
n1	2	0	0
n2	0	1	0
n3	1	0	1
n4	1	1	0

Answer 1

您应该将值1求和以获取计数。除此之外，我将使用此版本和布尔表达式：

SELECT nationality,
       SUM(class = 'Wereldklasse') AS Wereldklasse,
       SUM(class = 'Topklasse') AS Topklasse,
       SUM(class = 'Subtop') AS Subtop
FROM Fifaklasse
GROUP BY nationality;

Answer 2

蒂姆是正确的。但是，为了使其更易于理解并在大多数其他数据库引擎中工作，应将其重写为

SELECT nationality,
   SUM(iif(klass = 'World', 1, 0)) AS Wereldklasse,
   SUM(iif (klass = 'Top', 1, 0)) AS Topklasse,
   SUM(iif (klass = 'Subtop', 1, 0)) AS Subtop
FROM Fifaklass
GROUP BY nationality