得分最高的前三组应有特定的分数 5,3,1 [英] First three Groups with Highest Marks should have specific points 5,3, 1
问题描述
我的桌子
+------+--------+---------+-------+--------+
| NAME | GROUP1 | SECTION | MARKS | POINTS |
+------+--------+---------+-------+--------+
| S1 | G1 | class1 | 55 | (null) |
| S16 | G1 | class1 | 55 | (null) |
| S17 | G1 | class1 | 55 | (null) |
| S2 | (null) | class2 | 33 | (null) |
| S25 | G10 | class1 | 55 | (null) |
| S26 | G10 | class1 | 55 | (null) |
| S4 | G88 | class2 | 65 | (null) |
| S5 | G88 | class2 | 65 | (null) |
| S32 | (null) | class1 | 65 | (null) |
| S7 | G5 | class1 | 32 | (null) |
| S18 | G5 | class1 | 32 | (null) |
| S10 | (null) | class2 | 78 | (null) |
| S8 | G8 | class1 | 22 | (null) |
| S20 | G8 | class1 | 22 | (null) |
| S22 | G9 | class1 | 20 | (null) |
| S23 | G9 | class1 | 20 | (null) |
| S13 | G55 | class2 | 33 | (null) |
| S36 | G55 | class2 | 33 | (null) |
+------+--------+---------+-------+--------+
SQL 小提琴:http://www.sqlfiddle.com/#!2/f4b54/1/0
我之前也提过这个问题，根据评论我试着把问题改进得更清楚.
I have raised this question before, according to the comments I tried to improve the question more clear.
我正在尝试为每个部分中得分最高的前 3 组给出具体分数.我想给每个在一个部分中获得最高分的学生加 5 分，第 2 高分 3 分，第 3 高分 1 分.组内可能出现重复标记.
I am trying to give specific points to first 3 groups with highest marks in each Section. I would like to add 5 points to each student who got highest Mark in a Section, 3 points for 2nd highest Marks and 1 points for 3rd highest group. Duplicate Marks may occur for group.
例如:在 class1 中 - 组 'G1' 和 'G10' 获得了 '55' 并且他们在 'class1' 中获得了最高分.所以我想给'G1'和'G10'组的所有学生'5'分.
eg: in class1 - Group 'G1' and 'G10' got '55' and they got highest mark in 'class1'. So i would like to give '5' points to all students in Group 'G1' and' G10'.
G5 组获得第二高分，所以该组的所有学生都应该给 3 分.同样的方式 G8 - 1 分.
Group G5 got second highest marks, so all student in that group should give 3 points. Same way G8 - 1 point.
请注意:任何没有团体 ID 的学生参与单项，不考虑该记录.
plz note : any student who doesnt have Group ID that student is participating single item, do not consider that records.
我正在使用以下代码，此代码适用于个别学生，不知道如何给小组成员加分.
I am using following code, this code works fine for individual students, dont know how to give points to the Group members.
select t1.Name, t1.Section, t1.Marks from myTable t1 join
(select Section, substring_index(group_concat (distinct Marks order by Marks desc),
',', 3) as Marks3 from myTable where Section = 'class1' group by Section ) tsum
on t1.Section = tsum.Section and find_in_set(t1.Marks, tsum.Marks3) > 0
ORDER BY Section, Marks DESC, ID Desc
我的最终输出寻找一个部分.请只给出一个部分(class1)
My final output looks for a Section. Plz give only one Section (class1)
+------+--------+---------+-------+--------+
| NAME | GROUP1 | SECTION | MARKS | POINTS |
+------+--------+---------+-------+--------+
| S1 | G1 | class1 | 55 | 5 |
| S16 | G1 | class1 | 55 | 5 |
| S17 | G1 | class1 | 55 | 5 |
| S2 | (null) | class2 | 33 | (null) |
| S25 | G10 | class1 | 55 | 5 |
| S26 | G10 | class1 | 55 | 5 |
| S4 | G88 | class2 | 65 | (null) |
| S5 | G88 | class2 | 65 | (null) |
| S32 | (null) | class1 | 65 | (null) |
| S7 | G5 | class1 | 32 | 3 |
| S18 | G5 | class1 | 32 | 3 |
| S10 | (null) | class2 | 78 | (null) |
| S8 | G8 | class1 | 22 | 1 |
| S20 | G8 | class1 | 22 | 1 |
| S22 | G9 | class1 | 20 | (null) |
| S23 | G9 | class1 | 20 | (null) |
| S13 | G55 | class2 | 33 | (null) |
| S36 | G55 | class2 | 33 | (null) |
+------+--------+---------+-------+--------+
请帮助我..
推荐答案
这个答案由Itay
SELECT t1.`id`, t1.`name`, t1.`group1`,
t1.`section`, t1.`MARKS`, `t_group_points`.`points`
FROM `students` t1
LEFT JOIN (
(
SELECT `t4`.`group1`, `t_points`.`points`
FROM (SELECT `t3`.`group1`, AVG(`t3`.`marks`) AS `avg`
FROM `students` `t3`
WHERE (`t3`.`section` = 'class1') AND
(`t3`.`group1` IS NOT NULL)
GROUP BY `t3`.`group1`) `t4`
INNER JOIN (
(SELECT `top`.`avg`,
CASE @curRow := @curRow + 1
WHEN '1' THEN 5
WHEN '2' THEN 3
WHEN '3' THEN 1
ELSE NULL END 'points'
FROM (SELECT `t_avg`.`avg`
FROM (SELECT `t2`.`group1`, AVG(`t2`.`marks`) AS `avg`
FROM `students` `t2`
WHERE (`t2`.`section` = 'class1') AND
(`t2`.`group1` IS NOT NULL)
GROUP BY `group1`) `t_avg`
GROUP BY `t_avg`.`avg`
ORDER BY `avg` DESC
LIMIT 0, 3) `top`, (SELECT @curRow:=0) r
) AS `t_points`)
ON (`t_points`.`avg` = `t4`.`avg`)
) AS `t_group_points`)
ON (`t_group_points`.`group1` = `t1`.`group1`)
这篇关于得分最高的前三组应有特定的分数 5,3,1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
