ROW_NUMBER（）ORDER BY，不能在同一SELECT语句中使用列

debugcn 发表于 Dev

卡尔·布鲁克斯

我下面有一个调用视图以创建联赛表的过程：

CREATE PROCEDURE [dbo].[League_Table_Insert]
    @LeagueName VARCHAR(30)
AS
SET NOCOUNT ON
BEGIN

    DECLARE @LeagueID INT

    SELECT 
    @LeagueID = LeagueID FROM dbo.League
    WHERE LeagueName = @LeagueName

    SELECT [TeamName], [Played], [Wins], [Loss], [Draws], [Points], [Goals_Scored], [Goals_Against], [Goal_Difference]  
    FROM League_Table
    WHERE LeagueID = @LeagueID
    ORDER BY Points DESC, Goal_Difference DESC;

END

看法：

CREATE VIEW League_Table
    AS
    SELECT f.LeagueID, t.TeamName, 
    SUM(
        CASE WHEN f.HomeScore IS NOT NULL THEN 1 ELSE 0 END
    ) AS Played,
        SUM(
        CASE        
            WHEN t.TeamID = f.HomeTeamID THEN
                --Home Fixture
                CASE
                 WHEN f.HomeScore > f.AwayScore THEN 1 
                 ELSE 0
                 END
            WHEN t.TeamID = f.AwayTeamID THEN
                CASE
                 WHEN f.AwayScore > f.HomeScore THEN 1
                 ELSE 0 
                 END
        END
        ) AS Wins, 
            SUM(
        CASE        
            WHEN t.TeamID = f.HomeTeamID THEN
                --Home Fixture
                CASE
                 WHEN f.HomeScore < f.AwayScore THEN 1 
                 ELSE 0
                 END
            WHEN t.TeamID = f.AwayTeamID THEN
                CASE
                 WHEN f.AwayScore < f.HomeScore THEN 1 
                 ELSE 0
                 END
        END
        ) AS Loss, 
        SUM(CASE WHEN f.HomeScore = f.AwayScore THEN 1 ELSE 0 END) as Draws, 
        SUM(
        CASE 
            WHEN t.TeamID = f.AwayTeamID THEN
                --Away Fixture
                CASE
                    WHEN f.AwayScore > f.HomeScore THEN 3
                    WHEN f.AwayScore = f.HomeScore THEN 1
                    ELSE 0
                END
            WHEN t.TeamID = f.HomeTeamID THEN
                --Home Fixture
                CASE
                    WHEN f.HomeScore > f.AwayScore THEN 3 
                    WHEN f.HomeScore = f.AwayScore THEN 1
                    ELSE 0
                END
        END
    ) AS Points, 
        SUM(
        CASE
            WHEN t.TeamID = f.HomeTeamID THEN f.HomeScore
            -- Home Fixture
            WHEN t.TeamID = f.AwayTeamID THEN f.AwayScore
            -- Away Fixture
            END
            ) AS Goals_Scored,

        SUM(
        CASE
            WHEN t.TeamID = f.HomeTeamID THEN f.AwayScore
            -- Home Fixture
            WHEN t.TeamID = f.AwayTeamID THEN HomeScore
            -- Away Fixture
            END
            ) AS Goals_Against,
         SUM(
          CASE
          WHEN t.TeamID = f.HomeTeamID THEN       
              CASE
              WHEN f.HomeScore IS NOT NULL THEN f.HomeScore - f.AwayScore
          -- Home Fixture
              END
          WHEN t.TeamID = f.AwayTeamID THEN 
              CASE
              WHEN f.AwayScore IS NOT NULL THEN f.AwayScore - f.HomeScore
          -- Away Fixture
              END
          END
        ) AS Goal_Difference
    FROM dbo.Team t
    --Season TBC
    INNER JOIN dbo.Fixture f ON t.TeamID IN (f.HomeTeamID, f.AwayTeamID)
    GROUP BY f.LeagueID, t.TeamName

下面是它的输出：

我想在视图中包括另一个字段，其中将包含每个团队的联赛排名。我的问题是使用ROW_NUMBER时的ORDER BY，因为我不确定将其设置为什么，因为我无法在同一SELECT语句中使用它来将其设置为“ Points DESC”和“ Goal_Difference DESC”创造它。我的问题是在以下代码中将ORDER BY设置为什么才能输出正确的联赛排名？

ROW_NUMBER() OVER (PARTITION BY LeagueID ORDER BY ...)  AS Position,

托斯滕·凯特纳

最简单的方法可能是子查询（派生表）：

CREATE VIEW League_Table AS
select 
  data.*, 
  row_number() over (partition by leagueid 
                     order by points desc, goal_difference desc) as position,
from
(
  SELECT f.LeagueID, t.TeamName, 
  SUM(
      CASE WHEN f.HomeScore IS NOT NULL THEN 1 ELSE 0 END
     ) AS Played,
  SUM(
  ...
  GROUP BY f.LeagueID, t.TeamName
) data

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。