問題描述

我正在創建一個應用程序，用戶可以在其中進行鍛煉.他們通過應用程序傳遞結果，這些結果存儲在 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).

這篇關于優化查詢以在 MS SQL Server 中創建排名的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！