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

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

数据库：

GameResult
Id, GameId, TicketId,   Score,  Email,          Extra,  CreatedTime
1   1       1           100     [email protected]   info1   2012-12-01T12:12:12
2   1       2           200     [email protected]  info2   2014-01-01T01:01:01
3   1       3           300     [email protected]  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     [email protected]  info3   2013-05-01T05:05:05     2       abc3            345             666
2       2               1       2           200     [email protected]  info2   2014-01-01T01:01:01     1       abc2            234             555

最佳答案

好的，如果我理解正确，您希望获得每个用户的最高分数，并按分数降序返回查询。

在这种情况下，我将使用带有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，这是一种更易于阅读的更清洁的解决方案。

关于c# - 具有排名和唯一用户的高分，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/34901668/