php - 需要帮助查询竞赛获胜者的数据库

Question

我正在创建一个网络应用程序，用户将能够参加比赛，并根据他们的排名，用户将获得奖励。

( table 名竞赛)

id    |   contest_name   |   status
------------------------------------
1     |   Test Contest   |   active

(表参与者)

id    |    user_id   | contest_id   | score   | time_taken  
----------------------------------------------------------
1     |    123       |     1        |  10     |   2332  --> in milliseconds

我的比赛 table 奖品分配( table 名price_distribution)

id   |  contest_id  |  rank_start  | rank_end  | price
-------------------------------------------------------
1    |    1         |     1         |   10     |   50
-------------------------------------------------------
2    |    1         |     11        |   20     |   25
-------------------------------------------------------

这意味着如果所有排名在 1 到 10 之间的用户将获得 50 分，排名在 11-20 之间的用户将获得 20 分，以此类推。

我使用此查询来获取竞赛中所有用户的列表及其排名。

SELECT participants.score,
       participants.time_taken, 
       contest.name as contest_name, 
       user.name as username,
       user.image as userimage, 
       FIND_IN_SET( participants.score, (SELECT GROUP_CONCAT( participants.score ORDER BY participants.score DESC, participants.time_taken ASC ) 
                                         FROM participants )) AS rank
FROM participants 
LEFT JOIN contest 
ON participants.contest_id = contest.id 
LEFT JOIN user ON user.id = participants.user_id 
WHERE participants.contest_id = '1' 
ORDER BY participants.score DESC, participants.time_taken ASC 
LIMIT 50

上面的查询结果是这样的

score   |  time_taken  | contest_name  | username  | userimage  | rank  
-----------------------------------------------------------------------
10      |   2356       |  test_contest |  abc      |  image     | 1
-----------------------------------------------------------------------

上面的查询仅根据排名列出用户，不执行任何其他操作。我想根据排名奖励用户。如何实现这个查询。

我想知道执行时会根据用户的排名奖励用户的查询，并从奖励分配表中获取值。

任何帮助将不胜感激。

Answer 1

您可以根据用户的分数加入表price_distribution:

SELECT participants.score,
       participants.time_taken, 
       contest.name as contest_name, 
       user.name as username,
       user.image as userimage, 
       FIND_IN_SET( participants.score, (SELECT GROUP_CONCAT( participants.score ORDER BY participants.score DESC, participants.time_taken ASC ) 
                                         FROM participants )) AS rank,
       pd.price
FROM participants 
LEFT JOIN contest ON participants.contest_id = contest.id 
LEFT JOIN user ON user.id = participants.user_id 
LEFT JOIN price_distribution pd 
ON participants.contest_id = pd.contest_id and participants.score BETWEEN pd.rank_start AND pd.rank_end
WHERE participants.contest_id = '1' 
ORDER BY participants.score DESC, participants.time_taken ASC 
LIMIT 50