优化查询以在 MS SQL Server 中创建排名

2021-09-10 00:00:00 sql tsql sql-server

我正在创建一个应用程序，用户可以在其中进行锻炼.他们通过应用程序传递结果，这些结果存储在 SQL Server 数据库中.结果以这种方式保存在 SQL Server 表中:

I'm creating an application where users do workouts. They pass on their results via an app, and these results are stored in an SQL Server database. Results are saved in this way in a SQL Server table:

我想编写一个查询，根据每个用户的最佳分数创建一个排名.这是我目前所拥有的:

I want to write a query to create a ranking based on the best score of each user. This is what I have so far:

SELECT id, 
       workout_id, 
       level_id, 
       a.user_id, 
       total_time, 
       score, 
       datetime_added
FROM nodefit_rankings_fitness as a INNER JOIN
    (
     SELECT user_id, 
            MAX(score) AS MAXSCORE 
     FROM nodefit_rankings_fitness 
     GROUP BY user_id
    ) AS lookup
ON  lookup.user_id = a.user_id
    AND 
    lookup.MAXSCORE  =  a.score
ORDER BY score DESC, 
         datetime_added DESC

这会产生这个排名:

问题是，如果用户多次达到相同的最高分，他将多次出现在排名中.必须调整查询，以便当用户多次获得相同的最高分数时，排名中仅显示最后一次尝试的结果(基于 datetime_ added 列).

The problem is that if a user has achieved the same maximum score a number of times, he will appear multiple times in the ranking. The query must be adjusted so that when a user has the same maximum score a few times, only the result of the last attempt (based on the datetime_added column) is displayed in the rankings.

不幸的是，我自己找不到解决方案.我们当然感谢您的帮助.

Unfortunately, I cannot find a solution myself. Help is certainly appreciated.

推荐答案

如果你关心性能，你也应该尝试关联子查询:

If you care about performance, you should also try a correlated subquery:

SELECT id, workout_id, level_id, a.user_id, total_time, score, datetime_added
FROM nodefit_rankings_fitness nrf
WHERE nrf.id = (SELECT TOP (1) nrf2.id
                FROM nodefit_rankings_fitness nrf2
                WHERE nrf2.user_id = nrf.user_id
                ORDER BY nrf2.score DESC
               )
ORDER BY score DESC, datetime_added DESC;

特别是，这可以利用 nodefit_rankings_fitness(user_id, score desc, id) 上的索引.

In particular, this can take advantage of an index on nodefit_rankings_fitness(user_id, score desc, id).

相关文章