mysql - 最高分的前三组应该有特定的分数

Question

我的 table

+------+-------+---------+-------+--------+
| Name | Group1| Section | Marks | Points |
+------+-------+---------+-------+--------+
| S1   | G1    | class1  |    55 |        |
| S16  | G1    | class1  |    55 |        |
| S17  | G1    | class1  |    55 |        |
| S28  |       | class1  |    55 |        |
| S2   |       | class2  |    33 |        |
| S3   |       | class1  |    25 |        |
| S4   | G88   | class2  |    65 |        |
| S5   | G88   | class2  |    65 |        |
| S30  | G66   | class2  |    66 |        |
| S31  | G66   | class2  |    66 |        |
| S32  |       | class1  |    65 |        |
| S7   | G5    | class1  |    32 |        |
| S18  | G5    | class1  |    32 |        |
| S19  | G5    | class1  |    32 |        |
| S33  | G4    | class2  |    60 |        |
| S34  | G4    | class2  |    60 |        |
| S35  | G4    | class2  |    60 |        |
| S10  |       | class2  |    78 |        |
| S8   | G8    | class1  |    22 |        |
| S20  | G8    | class1  |    22 |        |
| S21  | G8    | class1  |    22 |        |
| S9   |       | class2  |    11 |        |
| S12  |       | class3  |    43 |        |
| S22  | G9    | class1  |    20 |        |
| S23  | G9    | class1  |    20 |        |
| S24  | G9    | class1  |    20 |        |
| S13  | G55   | class2  |    33 |        |
| S36  | G55   | class2  |    33 |        |
| S14  |       | class2  |    78 |        |
| S25  | G10   | class1  |    55 |        |
| S26  | G10   | class1  |    55 |        |
| S27  | G10   | class1  |    55 |        |
+------+-------+---------+-------+--------+

SQL FIDDLE:http://www.sqlfiddle.com/#!2/5ce6c/1

我正在尝试为每个部分中得分最高的前 3 组打分。我想给第一组的每个学生加 5 分，第二组加 3 分，第三组加 1 分。 .组可能出现重复标记。

我正在使用以下代码，此代码适用于个别学生，不知道如何给小组打分。

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

我的最终输出寻找一个部分。

 +---------------------------------------------+
 | | Name | Group1| Section | Marks | Points | |
 +---------------------------------------------+
 | | S1   | G1    | class1  |    55 |    5   | |
 | | S16  | G1    | class1  |    55 |    5   | |
 | | S17  | G1    | class1  |    55 |    5   | |
 | | S28  |       | class1  |    55 |        | |
 | | S2   |       | class2  |    33 |        | |
 | | S3   |       | class1  |    25 |        | |
 | | S4   | G88   | class2  |    65 |        | |
 | | S5   | G88   | class2  |    65 |        | |
 | | S30  | G66   | class2  |    66 |        | |
 | | S31  | G66   | class2  |    66 |        | |
 | | S32  |       | class1  |    65 |        | |
 | | S7   | G5    | class1  |    32 |    3   | |
 | | S18  | G5    | class1  |    32 |    3   | |
 | | S19  | G5    | class1  |    32 |    3   | |
 | | S33  | G4    | class2  |    60 |        | |
 | | S34  | G4    | class2  |    60 |        | |
 | | S35  | G4    | class2  |    60 |        | |
 | | S10  |       | class2  |    78 |        | |
 | | S8   | G8    | class1  |    22 |   1    | |
 | | S20  | G8    | class1  |    22 |   1    | |
 | | S21  | G8    | class1  |    22 |   1    | |
 | | S9   |       | class2  |    11 |        | |
 | | S12  |       | class3  |    43 |        | |
 | | S22  | G9    | class1  |    20 |        | |
 | | S23  | G9    | class1  |    20 |        | |
 | | S24  | G9    | class1  |    20 |        | |
 | | S13  | G55   | class2  |    33 |        | |
 | | S36  | G55   | class2  |    33 |        | |
 | | S14  |       | class2  |    78 |        | |
 | | S25  | G10   | class1  |    55 |   5    | |
 | | S26  | G10   | class1  |    55 |   5    | |
 | | S27  | G10   | class1  |    55 |   5    | |
 +---------------------------------------------+

请帮帮我。

Answer 1

这很有挑战性。

为了解决这个问题，我使用了几种方法:

1. A CASE statement将小组在前 3 名中的位置转换为积分。
2. Rows numbering有一个变量。
3. 内部 和左侧 JOIN将结果合并在一起。

以下查询已在您的 fiddle 上测试并有效:

SELECT t1.`id`,  t1.`name`,  t1.`group1`,
       t1.`section`, t1.`MARKS`, `t_group_points`.`points`

FROM   `students` t1

#--- Join  groups' points to the students
LEFT JOIN (
    (
        #---- Join all groups and give points to top 3 avg's groups ----
        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 3 avarages ----------
              (SELECT `top`.`avg`,
                      #-- Convert row number to points ---
                      CASE @curRow := @curRow + 1  
                           WHEN '1' THEN 5
                           WHEN '2' THEN 3
                           WHEN '3' THEN 1
                           ELSE NULL END 'points'

              FROM (SELECT DISTINCT `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`
                    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`)