SQL三方并列排名逻辑

Question

我正在使用PostgreSQL编写程序，以显示循环赛中5个小组的视频游戏锦标赛排名。因此，在某些地方可能存在3方并列的情况。我找到了确定头对头获胜者的逻辑：这里，这非常有帮助，但无法解决三方并列的情况。我稍微修改了模式，所以这是我正在使用的内容：

[poolteam]
-team 
-wins
 [versus]
 -team1
 -team2
 -win 

对阵表只告诉我们两支队伍之间的比赛结果。然后，我将其传入新表格中以获取团队的排名。

select team, wins, RANK() OVER (ORDER BY wins desc) AS rank 
    INTO rankTable 
from poolteam;

然后，我可以使用链接中的答案来获取排名和头对头的平局分胜负。我编写了这个程序来查找是否有超过2个队伍具有相同的排名

select rank 
from finalStandings 
group BY rank having count(*) >2

这个逻辑首先比较各队胜场数，然后再看两队之间的交锋情况，如果有超过2支平局队伍，则会考虑所有平局队伍之间的交锋情况。以下是一个示例：

Team A 3-1
Team B 2-2
Team C 2-2
Team D 2-2
Team E 1-3

团队A击败了B、C和E，团队B击败了C和D，团队C击败了E和D，团队D击败了E和A，团队E击败了B。

在这种情况下，三支并列的队伍（B、C、D）都赢了2场比赛，但因为B击败了所有并列的队伍，他们将取得第二名，C将获得第三名，因为他们赢得了与D的比赛。

如果三支队伍相互击败，则平局解决者会查看与并列队伍赢得的比赛，这很容易计算，只有这种情况我需要帮助处理。

- RedMenace

---

我认为这个问题在 SQL 中不容易解决，因为图形中可能会有循环。每个团队可以打两场比赛，赢得与“下一个”团队的比赛。要确定这些是平局，需要沿着图形进行遍历以打破平局。使用递归 CTEs 绝对可以做到，但并不简单。 - Gordon Linoff

是的，我在考虑使用存储过程，并将并列的队伍放入一个数组中，然后运行循环来查看谁打败了谁，然后分配一个积分系统。如果一支队伍击败了另外两支并列的队伍，则给予他们2分，而只击败一支队伍的队伍则得到1分。然后，如果仍然存在并列，则可以勾选这些积分。这似乎很长，所以我想知道是否有什么明显的遗漏。 - RedMenace

这是一个有趣的挑战。我创建了一个 SQLFiddle，如果其他人想接手并完成它的话可以访问该链接：http://sqlfiddle.com/#!17/585c8d/2 - JGFMK

Answer 1

如果三个队伍排名相同并打成平局，假设为A、B和C，则：

1. 一个队伍（假设为A）击败了B和C两支队伍，另一个队伍（假设为B）只击败了一支队伍C，还有一支队伍（假设为C）没有击败任何一支队伍；或者
2. 所有队伍都只击败了其中一支队伍，并且存在一个循环（例如A击败B，B击败C，C击败A）。

你可以看到，如果三支队伍排名相同并且彼此之间各打了一场比赛，那么总共会打出三场比赛，必须分配三次胜利，但没有一支队伍打了超过两场比赛，因此可能的情况要么是1, 1, 1，要么是2, 1, 0。这意味着通过一张胜利表和一张排名表，你可以通过简单地计算内部排名胜利并按照这些胜利排序来获得所需的结果。

假设你已经有了这个模式：具有名称、胜利和失败列的名为teamrank的表格和一个具有winner和loser列的表格matches，则可以使用以下方法：

模式：

CREATE TABLE matches (winner CHAR(1), loser CHAR(1));
insert into matches (winner, loser) values('A','B');
insert into matches (winner, loser) values('A','C');
insert into matches (winner, loser) values('D','A');
insert into matches (winner, loser) values('A','E');
insert into matches (winner, loser) values('B','C');
insert into matches (winner, loser) values('B','D');
insert into matches (winner, loser) values('E','B');
insert into matches (winner, loser) values('C','D');
insert into matches (winner, loser) values('C','E');
insert into matches (winner, loser) values('D','E');
create table team (name CHAR(1));
insert into team (name)  VALUEs('A');
insert into team (name)  VALUEs('B');
insert into team (name)  VALUEs('C');
insert into team (name)  VALUEs('D');
insert into team (name)  VALUEs('E');
CREATE TABLE TEAMSTAT (NAME CHAR(1), WINS SMALLINT, LOSSES SMALLINT);
insert into teamstat SELECT T.NAME,
       coalesce(m1.wins,0) as wins,
       coalesce(m2.losses,0) as losses
from team t
LEFT JOIN(select winner, count(winner) as wins 
          from matches
          group by winner
         ) m1 ON m1.winner = t.name 
LEFT JOIN(select loser, count(loser) as losses
          from matches
          group by loser
         ) m2 ON m2.loser = t.name

查询：

select t.name, t.wins, t.losses
, sum(case when t.name = m.winner and t.wins = t2.wins then 1 else 0 end) as wins_in_rank
, sum(case when t.name = m.winner and t.wins <= t2.wins then 1 else 0 end) as wins_in_or_above_rank
from teamstat t
 left join teamstat t2 on t2.name <> t.name and t.wins <= t2.wins
 left join matches m on m.winner in (t2.name, t.name) and m.loser in(t2.name, t.name)
group by t.name, t.wins, t.losses
order by t.wins desc, "wins_in_rank" desc, "wins_in_or_above_rank" desc

更新的代码片段：http://sqlfiddle.com/#!17/585c8d/13/0

- George S