c# - 具有排名和唯一用户的高分

Question

我想获得包含唯一用户的 Highscore 列表。独特部分有问题。通过删除 GROUP BY 脚本不包含错误

数据库:

GameResult
Id, GameId, TicketId,   Score,  Email,          Extra,  CreatedTime
1   1       1           100     test@test.com   info1   2012-12-01T12:12:12
2   1       2           200     test2@test.com  info2   2014-01-01T01:01:01
3   1       3           300     test3@test.com  info3   2013-05-01T05:05:05

Ticket
Id, UserId, TicketNumber,   ControlCode
1   1       abc1            123
2   1       abc2            234
3   2       abc3            345

User
Id, UniqueUserId
1   555
2   666

查询:

SELECT 
  g1.*, t1.UserId, t1.ControlCode, t1.TicketNumber, u1.UniqueUserId
FROM [dbo].[GameResult] as g1
INNER JOIN [dbo].[Ticket] as t1
      ON (g1.TicketId = t1.Id)
INNER JOIN [dbo].[User] as u1
    ON (t1.UserId = u1.Id)
GROUP BY u1.UniqueUserId 
ORDER BY g1.Score Desc, g1.CreatedTime Asc

错误:

Column 'dbo.GameResult.Id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

想要的结果:

Rank    GameResult.Id,  GameId, TicketId,   Score,  Email,          Extra,  CreatedTime,            UserId, TicketNumber,   ControlCode,    UniqueUserId
1       3               1       3           300     test3@test.com  info3   2013-05-01T05:05:05     2       abc3            345             666
2       2               1       2           200     test2@test.com  info2   2014-01-01T01:01:01     1       abc2            234             555

Answer 1

好的，如果我没理解错的话，你想得到每个用户的最高分，并按分数降序返回一个query。

在那种情况下，我会使用带有如下 Windows 函数的 CTE:

WITH cte AS (
 SELECT UserId, Score, gr.Id,
  ROW_NUMBER() OVER (Partition By UserId ORDER BY Score DESC) rn
 FROM [dbo].[GameResult] as g
 INNER JOIN [dbo].[Ticket] as t
      ON (g.TicketId = t.Id)
)
SELECT 
  g1.*, t1.UserId, t1.ControlCode, t1.TicketNumber, u1.UniqueUserId
FROM [dbo].[GameResult] as g1
INNER JOIN [dbo].[Ticket] as t1
    ON (g1.TicketId = t1.Id)
INNER JOIN [dbo].[User] as u1
    ON (t1.UserId = u1.Id)
INNER JOIN cte 
    ON g1.Id=cte.Id
WHERE cte.rn = 1
ORDER BY g1.Score Desc, g1.CreatedTime Asc

如有必要，您可以在没有 CTE 和 ROW_NUMBER 函数的情况下完成您想要的操作，但这将是一个更加复杂的查询。这是一个更清晰的解决方案，更易于阅读，IMO。