gpt4 book ai didi

mySQL 排名(和抽奖)

转载 作者:行者123 更新时间:2023-11-29 04:28:59 27 4
gpt4 key购买 nike

下周末我们将进行一场包含 3 项资格赛(半决赛和决赛)的比赛。只有最好的 15 名参赛者才能参加半决赛。只有最好的 6 人才能参加总决赛。

在资格考试中,每项资格考试的分数从 0 到 100 不等

我正在寻找一种方法来选择半决赛的参赛者。这应该基于(资格等级1)*(资格等级2)*(资格等级3)

所以我需要这样的东西:

select id, name, ((.... as RANK_OF_SCORE_1) * (.. as RANK_OF_SCORE_2) * (... as     RANK_OF_SCORE_3)) as qualification_score from participants order by qualification_score desc limit 15

但这当然不是有效的 mySQL。

除此问题外,如果参赛者得分相同,即使超过 15 分的上限,他们也应该进入半决赛。

对于决赛,我们想选出半决赛成绩最好的 6 场比赛。如果 2 个分数相同,我们希望选择资格..

最佳答案

选项 1:使用支持窗口函数(即 RANK() 和 DENSE_RANK())的 postgres

SELECT user_id, score, rank() over (order by score desc) from scores;
Time : 0.0014 s

选项 2:使用自连接:得分为 X 的用户的排名是(1 + 得分小于 X 的用户的数量(*));这可能会很慢

CREATE TABLE scores( user_id INT PRIMARY KEY, score INT, KEY(score) );
INSERT INTO scores SELECT id, rand()*100 FROM serie LIMIT 1000;

SELECT a.user_id, a.score, 1+count(b.user_id) AS rank
FROM scores a
LEFT JOIN scores b ON (b.score>a.score)
GROUP BY user_id ORDER BY rank;

+---------+-------+------+
| user_id | score | rank |
+---------+-------+------+
| 381 | 100 | 1 |
| 777 | 100 | 1 |
| 586 | 100 | 1 |
| 907 | 100 | 1 |
| 790 | 100 | 1 |
| 253 | 99 | 6 |
| 393 | 99 | 6 |
| 429 | 99 | 6 |
| 376 | 99 | 6 |
| 857 | 99 | 6 |
| 293 | 99 | 6 |
| 156 | 99 | 6 |
| 167 | 98 | 13 |
| 594 | 98 | 13 |
| 690 | 98 | 13 |
| 510 | 98 | 13 |
| 436 | 98 | 13 |
| 671 | 98 | 13 |

time 0.7s

选项 3:

SET @rownum = 0;
SELECT a.user_id, a.score, b.r FROM
scores a
JOIN (
SELECT score, min(r) AS r FROM (
SELECT user_id, score, @rownum:=@rownum+1 AS r
FROM scores ORDER BY score DESC
) foo GROUP BY score
) b USING (score)
ORDER BY r;

time : 0.0014 s

编辑

SET @rownum1 = 0;
SET @rownum2 = 0;
SET @rownum3 = 0;

SELECT s.*, s1.r, s2.r, s3.r FROM
scores s
JOIN
(
SELECT score_1, min(r) AS r FROM (
SELECT score_1, @rownum1:=@rownum1+1 AS r
FROM scores ORDER BY score_1 DESC
) foo GROUP BY score_1
) s1 USING (score_1) JOIN (
SELECT score_2, min(r) AS r FROM (
SELECT score_2, @rownum2:=@rownum2+1 AS r
FROM scores ORDER BY score_2 DESC
) foo GROUP BY score_2
) s2 USING (score_2) JOIN (
SELECT score_3, min(r) AS r FROM (
SELECT score_3, @rownum3:=@rownum3+1 AS r
FROM scores ORDER BY score_3 DESC
) foo GROUP BY score_3
) s3 USING (score_3)
ORDER BY s1.r * s2.r * s3.r;

关于mySQL 排名(和抽奖),我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/6003767/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com