在我的表"game_info"中,我有
ID | player_1 | player_2 | score
--------------------------------
1 | John | Rick | 100
2 | Joe | John | 80
3 | Bob | Rick | 210
我想对每个球员的总分求和,无论他们是player_1还是player_2,并按分数排序。
我的期望是:
Name | Score
------------
Rick | 310
Bob | 210
John | 180
Joe | 80
我试图加入和联盟,但我做不好。
有什么办法可以在 sql 中做到这一点,或者我必须重新设计我的表?
谢谢!
您可以使用
union all
和group by
执行此操作:
select player, sum(score)
from ((select player1 as player, score from game_info
) union all
(select player2 as player, score from game_info
)
) p
group by player
order by sum(score) desc;
SELECT player, SUM(score)
FROM
(SELECT player_1 AS player, score
FROM t
UNION ALL
SELECT player_2 as player, score
FROM t) sub
GROUP BY player
您必须将玩家 1 结果与玩家 2 结果联合起来,这样您就只有一个只有玩家和分数的结果。当然,在此视图中,分数将列出两次,每个玩家一个。在我的查询中,结果称为 sub。根据替补,您可以按球员分组并汇总他们的分数。