sql - 根据谁赢得了他们所玩的比赛，在池中排名相等的积分

Question

我正在编写一个应用程序，用于计算橄榄球比赛(2015 年橄榄球世界杯，但它可以应用于许多其他基于泳池的比赛)的团队排名。

在分组阶段结束时，根据胜/平/负/红利积分计算积分，并按积分对队伍进行排名。 如果两支或两支以上的球队积分相同，则以两支并列球队交手的胜者为准。一旦初始排名完成，其他标准(分差) , tries difference, points scored, tries scored) 用于完成排名过程。

我已经编写了一个查询来执行上述所有操作，除了第一个排名标准 - 两支球队的积分水平应根据两支球队参加比赛的获胜者进行排序。

这是我的数据库架构:

[TEAM]        [MATCH]
-TeamId       -MatchId
-TeamName     -HomeTeamId
-Pool         -AwayTeamId
              -HomeTeamScore
              -HomeTeamTries
              -AwayTeamScore
              -AwayTeamTries

这是我用来计算给定池的排名的 SQL Server 查询:

WITH PoolResults ([MatchId], [TeamId], [Team], [P], [W], [D], [L], [PF], [PA], [PD], [TF], [TA], [TD], [PTS], [BP]) 
AS (SELECT 
        M.[MatchId], 
        M.[HomeTeamId] AS [TeamId], 
        HT.[TeamName], 
        1 AS [P], 
        CASE WHEN M.[HomeTeamScore] > M.[AwayTeamScore] THEN 1 ELSE 0 END AS [W], 
        CASE WHEN M.[HomeTeamScore] = M.[AwayTeamScore] THEN 1 ELSE 0 END AS [D], 
        CASE WHEN M.[HomeTeamScore] < M.[AwayTeamScore] THEN 1 ELSE 0 END AS [L], 
        M.[HomeTeamScore] AS [PF], 
        M.[AwayTeamScore] AS [PA], 
        (M.[HomeTeamScore] - M.[AwayTeamScore]) AS [PD], 
        M.[HomeTeamTries] AS [TF], 
        M.[AwayTeamTries] AS [TA], 
        (M.[HomeTeamTries] - M.[AwayTeamTries]) AS [TD], 
        CASE 
            WHEN M.[HomeTeamScore] > M.[AwayTeamScore] THEN 4 
            WHEN M.[HomeTeamScore] = M.[AwayTeamScore] THEN 2 
            WHEN M.[HomeTeamScore] < M.[AwayTeamScore] THEN 0 
        END AS [PTS], 
        CASE 
            WHEN ((M.[AwayTeamScore] - M.[HomeTeamScore]) BETWEEN 1 AND 7) AND M.[HomeTeamTries] >= 4 THEN 2 
            WHEN ((M.[AwayTeamScore] - M.[HomeTeamScore]) BETWEEN 1 AND 7) THEN 1 
            WHEN M.[HomeTeamTries] >= 4 THEN 1 
            ELSE 0 
        END AS [BP] 
    FROM 
        Match AS M 
        INNER JOIN Team AS HT 
            ON M.[HomeTeamId] = HT.[TeamId] 
    WHERE 
        M.[HomeTeamScore] IS NOT NULL 
        AND HT.[Pool] = @Pool

    UNION 

    SELECT 
        M.[MatchId], 
        M.[AwayTeamId] AS [TeamId], 
        AT.[TeamName], 
        1 AS [P], 
        CASE WHEN M.[AwayTeamScore] > M.[HomeTeamScore] THEN 1 ELSE 0 END AS [W], 
        CASE WHEN M.[AwayTeamScore] = M.[HomeTeamScore] THEN 1 ELSE 0 END AS [D], 
        CASE WHEN M.[AwayTeamScore] < M.[HomeTeamScore] THEN 1 ELSE 0 END AS [L], 
        M.[AwayTeamScore] AS [PF], 
        M.[HomeTeamScore] AS [PA], 
        (M.[AwayTeamScore] - M.[HomeTeamScore]) AS [PD], 
        M.[AwayTeamTries] AS [TF], 
        M.[HomeTeamTries] AS [TA], 
        (M.[AwayTeamTries] - M.[HomeTeamTries]) AS [TD], 
        CASE 
            WHEN M.[AwayTeamScore] > M.[HomeTeamScore] THEN 4 
            WHEN M.[AwayTeamScore] = M.[HomeTeamScore] THEN 2 
            WHEN M.[AwayTeamScore] < M.[HomeTeamScore] THEN 0 
        END AS [PTS], 
        CASE 
            WHEN ((M.[HomeTeamScore] - M.[AwayTeamScore]) BETWEEN 1 AND 7) AND M.[AwayTeamTries] >= 4 THEN 2 
            WHEN ((M.[HomeTeamScore] - M.[AwayTeamScore]) BETWEEN 1 AND 7) THEN 1 
            WHEN M.[AwayTeamTries] >= 4 THEN 1 
            ELSE 0 
        END AS [BP] 
    FROM 
        Match AS M 
        INNER JOIN Team AS AT 
            ON M.[AwayTeamId] = AT.[TeamId] 
    WHERE 
        M.[AwayTeamScore] IS NOT NULL 
        AND AT.[Pool] = @Pool 
    )

SELECT ROW_NUMBER() 
    OVER (ORDER BY 
            SUM([BP] + [PTS]) DESC, 
            SUM([PD]) DESC, 
            SUM([TD]) DESC, 
            SUM([PF]) DESC, 
            SUM([TF]) DESC) AS [Position], 
    [TeamId], 
    [TeamName], 
    SUM([P]) AS [P], 
    SUM([W]) AS [W], 
    SUM([D]) AS [D], 
    SUM([L]) AS [L], 
    SUM([PF]) AS [PF], 
    SUM([PA]) AS [PA], 
    SUM([PD]) AS [PD], 
    SUM([TF]) AS [TF], 
    SUM([TA]) AS [TA], 
    SUM([BP]) AS [BP], 
    SUM([BP] + [PTS]) AS [PTS] 
FROM 
    PoolResults 
GROUP BY 
    [TeamId], 
    [TeamName];

如前所述，除了考虑两支球队在相同分数上的排名(基于谁赢得了他们之间的比赛)之外，这一切都会发生。 有人对如何进行初始排名有建议吗？

===== 更新原帖 =====

澄清 - 可以有超过 2 支队伍获得相同的分数，在这种情况下，必须对 2 支队伍的每个组合进行评估以确定排名。 SqlFiddle 上的以下示例说明了一个池中有 5 个团队的场景，其中 3 个团队具有相同的点数 - http://sqlfiddle.com/#!6/c0701/3

查询 #1 显示原始比赛数据(哪些球队参加比赛和比分)

查询 #2 显示未排序的池排名:

Australia (10)
England (10)
Fiji (5)
Uruguay (0)
Wales (10)

查询 #3 显示了按点排序的池排名:

Australia (10)
England (10)
Wales (10)
Fiji (5)
Uruguay (0)

然而，真正的顺序应该是:

Wales (10)
England (10)
Australia (10)
Fiji (5)
Uruguay (0)

威尔士排名高于英格兰，因为威尔士击败英格兰，英格兰排名高于澳大利亚，因为英格兰击败澳大利亚

Answer 1

这需要 sql 2012+ 使用 LEAD() 和 LAG() functions ，此时也只要求两支队伍的Rank相同。

架构

CREATE TABLE Table1
    ([team] varchar(1), [rank] int);

INSERT INTO Table1
    ([team], [rank])
VALUES
    ('A', 1),('B', 1),('C', 2);

CREATE TABLE Table2
    ([team1] varchar(1), [team2] varchar(1), [win] varchar(1));

INSERT INTO Table2
    ([team1], [team2], [win])
VALUES
    ('A', 'B', 'B'), ('C', 'A', 'A'),('C', 'B', 'B');

SQL Fiddle Demo

WITH breakTie AS ( 
    SELECT
        [team],
        [rank],
        LAG([team]) OVER (ORDER BY [rank]) PreviousTeam,
        LEAD([team]) OVER (ORDER BY [rank]) NextTeam,
        LAG([rank]) OVER (ORDER BY [rank]) PreviousRank,
        LEAD([rank]) OVER (ORDER BY [rank]) NextRank
    FROM Table1
)
SELECT *, CASE 
       WHEN B.[rank] = B.[NextRank] and B.[team] = T.[win] THEN 1
       WHEN B.[rank] = B.[PreviousRank] and B.[team] = T.[win] THEN 1
       ELSE 0
    END as breakT
FROM breakTie B
LEFT JOIN Table2 T
   ON ( B.team = T.team1 or B.team = T.team2)
  AND ( B.NextTeam = T.team1 or B.NextTeam = T.team2)
ORDER BY 
    [rank],
    CASE 
       WHEN B.[rank] = B.[NextRank] and B.[team] = T.[win] THEN 1
       WHEN B.[rank] = B.[PreviousRank] and B.[team] = T.[win] THEN 1
       ELSE 0
    END