mysql - 将多行重新组合为一行

Question

假设我有一张使用查询得到的表

我正在研究一个可以提供类似这样的东西的查询

这是我到目前为止尝试过的:

Select [First Name], [Last Name], Club,
case when Competition='La Liga' then Wins end as [La Liga], 
case when Competition='Copa del Rey' then Wins end as [Copa del Rey],
case when Competition='Supercopa de Espana' then Wins end as [Supercopa de Espana],     
case when Competition='UEFA Champions League' then Wins end as [UEFA Champions League],  
case when Competition='UEFA Super Cup' then Wins end as [UEFA Super Cup],  
case when Competition='FIFA Club World Cup' then Wins end as [UEFA Super Cup]

From dbo.Table
Group by [First Name], [Last Name], Club

但我收到“SQL 执行错误”列“dbo.Wins”在选择列表中无效，因为它未包含在聚合函数或 GROUP BY 子句中。列“dbo.Competition”在选择列表中无效，因为它未包含在聚合函数或 GROUP BY 子句中。

将列“Wins”和“Competition”放在 GROUP BY 子句中不会给出我想要的结果。

还有什么建议吗？

提前致谢

Answer 1

您需要向其中添加SUM、MIN 或MAX，如下所示:

Select [First Name], [Last Name], Club,
SUM(case when Competition='La Liga' then Wins end) as [La Liga], 
SUM(case when Competition='Copa del Rey' then Wins end) as [Copa del Rey],
SUM(case when Competition='Supercopa de Espana' then Wins end) as [Supercopa de Espana],     
SUM(case when Competition='UEFA Champions League' then Wins end) as [UEFA Champions League],  
SUM(case when Competition='UEFA Super Cup' then Wins end) as [UEFA Super Cup],  
SUM(case when Competition='FIFA Club World Cup' then Wins end) as [UEFA Super Cup]

From dbo.Table
Group by [First Name], [Last Name], Club

您不能通过 SQL Server 中的查询将未聚合的项目留在一组中，即使您知道每个项目最多有一个非空条目。