目前我正在开发一个系统来支持比赛。在这场比赛中,有11位评委给参赛者打分。这个分数存储在每个裁判的一个表中。使用下面的Select语句,我从MySQL database
(以及从其他表中的一些inner joins
到select
的其他信息)检索总分。
现在我想在这个系统中有排名,其中也考虑了分数。我已经尝试了这个论坛上的几个mysql
解决方案。但不知何故,这种说法总是不正确的。希望有人能帮助我。
SELECT optreden.id, optreden.wedstrijd, optreden.jaartal,
liedje.titel, club.volledige_clubnaam,
(SELECT SUM(score.score_lied) FROM score WHERE score.optreden=optreden.id) AS score
FROM optreden
INNER JOIN liedje ON optreden.liedje=liedje.id
INNER JOIN club ON liedje.uitvoerende_club=club.id
WHERE optreden.wedstrijd="voorselectie" AND optreden.jaartal=2014
GROUP BY optreden.id
ORDER BY score_lied DESC
使用这个查询,我得到以下结果:
+----+--------------+------+----------+--------------+------+
| id | Competition | Year | Title | Artist (club)| Score|
+----+--------------+------+----------+--------------+------+
| 12 | voorselectie | 2014 | Song 1 | Club 1 | 792 |
| 16 | voorselectie | 2014 | Song 2 | Club 2 | 600 |
| 10 | voorselectie | 2014 | Song 3 | Club 3 | 600 |
| 11 | voorselectie | 2014 | Song 3 | Club 3 | 500 |
| 12 | voorselectie | 2014 | Song 3 | Club 3 | 400 |
查询从以下表中获取信息:
- 评分:本表中11位评委给出的评分保存为行项目(结果如下);
- Optreden:本表中俱乐部和歌曲的信息是链接的;
- 俱乐部:来自俱乐部的信息;如名;
- Liedje:关于歌曲的信息,由俱乐部。所有的表都是通过id链接的。在结果中,应该有一个列,上面有俱乐部的排名。
表得分的结果如下:
+----+----+--------+----+
| ID | Pid| Userid | 85 |
+----+----+--------+----+
| 1 | 12 | 444 | 85 |
| 12 | 12 | 454 | 92 |
| 13 | 12 | 445 | 87 |
| 14 | 12 | 446 | 56 |
| 15 | 12 | 447 | 81 |
| 16 | 12 | 448 | 78 |
| 17 | 12 | 449 | 55 |
| 18 | 12 | 450 | 69 |
| 19 | 12 | 451 | 88 |
| 20 | 12 | 452 | 69 |
| 21 | 12 | 453 | 32 |
+----+----+--------+----+
其中各列表示如下内容:- ID:表中得分的id
;- Pid:演出的id
(与此演出俱乐部和歌曲相关联);-用户名:11名评委中的userid
;-评分:每位裁判给出的score
。
最终结果应该如下所示:
+----+--------------+------+----------+--------------+------+------+
| id | Competition | Year | Title | Artist (club)| Score| Rank |
+----+--------------+------+----------+--------------+------+------+
| 12 | voorselectie | 2014 | Song 1 | Club 1 | 792 | 1 |
| 16 | voorselectie | 2014 | Song 2 | Club 2 | 600 | 2 |
| 10 | voorselectie | 2014 | Song 3 | Club 3 | 600 | 2 |
| 11 | voorselectie | 2014 | Song 3 | Club 3 | 500 | 4 |
| 12 | voorselectie | 2014 | Song 3 | Club 3 | 400 | 5 |
我以前试过内维尔的解决方案,现在又试了一次。我重写了SQL statement
如下:
SELECT optreden.id, optreden.wedstrijd, optreden.jaartal,
liedje.titel, club.volledige_clubnaam,
(SELECT SUM(score.score_lied) FROM score WHERE score.optreden=optreden.id) AS score, CASE
WHEN @prev_value = score THEN @rank_count
WHEN @prev_value := score THEN @rank_count := @rank_count + 1
END AS rank,
FROM optreden
INNER JOIN liedje ON optreden.liedje=liedje.id
INNER JOIN club ON liedje.uitvoerende_club=club.id
WHERE optreden.wedstrijd="voorselectie" AND optreden.jaartal=2014
GROUP BY optreden.id
ORDER BY score_lied DESC
这里我得到错误:Unknown column 'score' in 'field list'.
.
似乎你得到这个错误只是因为没有字段得分…我已经将内部SELECT提取到JOIN中,以便可以直接使用实际字段。(虽然没有测试,抱歉)
SELECT optreden.id, optreden.wedstrijd, optreden.jaartal,
liedje.titel, club.volledige_clubnaam,
SUM(score.score_lied) AS score, CASE
WHEN @prev_value = SUM(score.score_lied) THEN @rank_count
WHEN @prev_value := SUM(score.score_lied) THEN @rank_count := @rank_count + 1
END AS rank,
FROM optreden
INNER JOIN liedje ON optreden.liedje=liedje.id
INNER JOIN club ON liedje.uitvoerende_club=club.id
LEFT JOIN score ON score.optreden=optreden.id
WHERE optreden.wedstrijd="voorselectie" AND optreden.jaartal=2014
GROUP BY optreden.id
ORDER BY score_lied DESC
请注意,这种排名可能会面临另一个小问题:通常,当两个实体最终处于相同的排名时,下一个排名不会给下一个人。下面是我举的一个例子,两个人排在第3位:
1, 2, 3, 3, 5
但是在你的代码中,它会给出:
1, 2, 3, 3, 4
我已经尝试了几种方法。然而,我忘了告诉你我将要使用的一个重要特性。俱乐部的排名应该通过大屏幕展示。因此MySQL代码做了一些改动。
我已经添加了一个支持表到我的数据库。在这个支持表中,分数的总和是用INSERT INTO…SELECT语句。
插入完成后。启动一个更新脚本,在表中进行排名,并对ex - equo进行正确的计算。
下面是update语句:
SET @rank=0, @last_score = null, @tie_build_up = 0;
UPDATE ranking_voorselectie_lied
SET rank= @rank:= if(@last_score = totaal_score_lied, @rank, @rank+@tie_build_up+1),
tie_build_up= @tie_build_up:= if(@last_score = totaal_score_lied, @tie_build_up+1, 0),
last_score= @last_score:= totaal_score_lied
WHERE wedstrijd="voorselectie" AND jaar=2014
ORDER BY totaal_score_lied DESC;
结果如下:
+--------------+------+----------+-------------------+------+-----+------------+
| Wedstrijd | Jaar | Optreden | totaal_score_lied | Rank | Tie | last_score |
+--------------+------+----------+-------------------+------+-----+------------+
| voorselectie | 2014 | 12 | 792 | 1 | 0 | 792 |
| voorselectie | 2014 | 16 | 82 | 2 | 0 | 82 |
| voorselectie | 2014 | 10 | 73 | 3 | 0 | 73 |
| voorselectie | 2014 | 15 | 51 | 4 | 0 | 51 |
| voorselectie | 2014 | 3 | 50 | 5 | 0 | 50 |
| voorselectie | 2014 | 11 | 42 | 6 | 0 | 42 |
| voorselectie | 2014 | 13 | 38 | 7 | 0 | 38 |
| voorselectie | 2014 | 8 | 38 | 7 | 1 | 38 |
| voorselectie | 2014 | 14 | 37 | 9 | 0 | 37 |
| voorselectie | 2014 | 5 | 35 | 10 | 0 | 35 |
| voorselectie | 2014 | 4 | 33 | 11 | 0 | 33 |
| voorselectie | 2014 | 7 | 25 | 12 | 0 | 25 |
| voorselectie | 2014 | 9 | 23 | 13 | 0 | 23 |
+--------------+------+----------+-------------------+------+-----+------------+