SQLite -每场比赛的赢家(等级高于分区)

Question

我正在为卡卡松创建一个分数追踪应用程序。我想得到每场比赛的冠军。

我已经在MS中实现了这个功能，现在我正在尝试将数据库转换为SQLite。

模式

http://sqlfiddle.com/#!7/e8340

MS-SQL

CREATE VIEW vTopScorePerGame AS
WITH ranked AS (
    SELECT      RANK() OVER (PARTITION BY [GameID] ORDER BY TotalScore DESC) AS Position,
                   PlayerID,
                PlayerName, 
                TotalScore,
                [GameID]
    FROM        vScore
)
SELECT      [GameID],
            PlayerID,
            PlayerName AS Winner,
            TotalScore
FROM        ranked      
WHERE       ranked.Position = 1

当我尝试使用SQLFiddle或SQLiteBrowser时，我会得到以下错误：

near "(": syntax error:

有人能给我指明正确的方向吗？

函数

使用

with.html

聚结

corefunc.html

无支撑

http://www2.sqlite.org/cvstrac/wiki?p=UnsupportedSqlAnalyticalFunctions

Answer 1

如果有SQLite 3.7.11或更高版本，只需使用MAX()从组中值最大的行获取值：

SELECT GameID,
       PlayerID,
       PlayerName AS Winner,
       MAX(TotalScore) AS TotalScore
FROM vScore
GROUP BY GameID;

在早期版本中，您可以在一个单独的子查询中获取具有larget得分的球员，然后使用原始表加入他们：

SELECT GameID,
       PlayerID,
       PlayerName AS Winner,
       TotalScore
FROM vScore
JOIN (SELECT GameID,
             (SELECT PlayerID
              FROM vScore AS s2
              WHERE s2.GameID = GameIDs.GameID
              ORDER BY TotalScore DESC
              LIMIT 1
             ) AS PlayerID
      FROM (SELECT DISTINCT GameID
            FROM vScore) AS GameIDs)
     USING (GameID, PlayerID);