问题描述:

I want to get an Highscore list with unique users.

Having issues with the unique part. By removing GROUP BY the script doesn't contain errors

DATABASE:

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

QUERY:

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

ERROR:

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.

WANTED RESULT:

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

网友答案:

Ok, if I understand correctly, you want to get each user's highest score and return a query in descending order by score.

In that case I would use a CTE with a windows function like this:

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

If necessary, what you want CAN be done without the CTE and ROW_NUMBER function, but it would be a much more convoluted query. This is a cleaner solution that is easier to read, IMO.

网友答案:

You just need to add a rank to the query. You might want to use dense_rank or row_number depending on how you want to treat rows with the same score.

Here is a mock-up query:

SELECT 
  rank() over (partition by g1.Id order by g1.Score Desc) as Rank,
  g1.Id,
  g1.GameId,
  g1.TicketId,
  g1.Score,
  g1.Email,
  g1.Extra,
  g1.CreatedTime,
  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

The rank query provided is based on the example data but most likely you will have to calculate the rank over each game, something like this :

rank() over (partition by g1.GameId, g1.Id order by g1.Score Desc) as Rank,
网友答案:

use Inner join for more info about SQL join example

SELECT * FROM GameResult as a 
    INNER JOIN Ticket as b
    ON a.TicketId=b.TicketId;
    INNER JOIN User as c
    ON b.UserId = c.Id 
相关阅读:
Top