本文介绍了得分最高的前三个组应有特定要点的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我的桌子
+------+-------+---------+-------+--------+ | 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: www.sqlfiddle/#!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我的最终输出寻找一个Section.
+---------------------------------------------+ | | 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 | | +---------------------------------------------+请帮助我.
解决方案我找到了一种列出带有分配点的组的解决方案，但是我很难将结果存储回myTable，即执行UPDATE在桌子上.我终于做到了(请参阅文章底部！).
但是首先，这是小组得分表生成器(小组概述):
SELECT mg,ms,mm, CASE WHEN @s=ms THEN CASE WHEN @m=mm THEN @i WHEN @i>2 THEN @i:=@i-2 ELSE null END ELSE @i:=5 END pt, @g:=mg gr,@s:=ms,@m:=mm FROM ( SELECT group1 mg,section ms,max(marks) mm FROM mytable WHERE group1>'' GROUP BY group1,section ) m ORDER BY ms,mm desc,mgsqlfiddle/#!2/bea2a2/1
它给了我这个清单:
| MG | MS | MM | PT | GR | @S:=MS | @M:=MM | ------------------------------------------------------ | G1 | class1 | 55 | 5 | G1 | class1 | 55 | | G10 | class1 | 55 | 5 | G10 | class1 | 55 | | G5 | class1 | 32 | 3 | G5 | class1 | 32 | | G8 | class1 | 22 | 1 | G8 | class1 | 22 | | G9 | class1 | 20 | (null) | G9 | class1 | 20 | | G66 | class2 | 66 | 5 | G66 | class2 | 66 | | G88 | class2 | 65 | 3 | G88 | class2 | 65 | | G4 | class2 | 60 | 1 | G4 | class2 | 60 | | G55 | class2 | 33 | (null) | G55 | class2 | 33 |我上午返回(2013年8月26日，在自己寻求帮助后，请参见此处)并且现在可以提供完整答案:
SET @s:=@m:=@i:='a'; -- variables *MUST* be "declared" in some -- way, otherwise UPDATE will not work! UPDATE mytable INNER JOIN (SELECT mg,ms,mm, CASE WHEN @s=ms THEN CASE WHEN @m=mm THEN @i WHEN @i>2 THEN @i:=@i-2 ELSE null END ELSE @i:=5 END pt, @s:=ms,@m:=mm FROM ( SELECT group1 mg,section ms,max(marks) mm FROM mytable WHERE group1>'' GROUP BY group1,section ) m ORDER BY ms,mm desc,mg ) t ON mg=group1 AND ms=section AND mm=marks SET Points=pt在此处查看 sqlfiddle/#!2/bb7f2
最后-题外话:
亲爱的用户@ user2594154，您为什么要用相同问题 8次轰炸此板???
- 在最高分上添加特定点查找总分和分组
- 得分最高的前三个小组应有具体要点(此帖子)
- 按名称和分数将结果分组按分数对每个节进行分组，并且
- CompetitionName降序查找两个学生的最高点
- 在显示3列的最大值的部分中，包括重复项
- 具有条件的值前三个得分最高的组应该
- 具有5、3、1个特定点[重复]
如果您将问题保留在一个帖子中，对确切的解释以及您自己尝试过的内容，这对每个人都将大有帮助. (!!).然后，在回答过程中，可以对其进行编辑，使其更加精确.如果新问题的主题实际上是不同，则仅应发布新问题，请参见此处.
没有难过的感觉-在解决您的这个问题的过程中，我学到了很多东西. ;-)
my 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 : www.sqlfiddle/#!2/5ce6c/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 in the 1st highest groups, 3 points for 2nd highest and 1 points for 3rd highest group. .Duplicate Marks may occur for group.
I am using following code, this code works fine for individual students, dont know how to give points to the Group.
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 DescMy final output looks for a Section.
+---------------------------------------------+ | | 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 | | +---------------------------------------------+Please help me.
解决方案I have found a solution to list the groups with their allocated points but I was having a hard time storing the result back into myTable, i.e. doing an UPDATE on the table. I managed at last (see bottom of post!!).
But first, here is the group score-table generator (group overview):
SELECT mg,ms,mm, CASE WHEN @s=ms THEN CASE WHEN @m=mm THEN @i WHEN @i>2 THEN @i:=@i-2 ELSE null END ELSE @i:=5 END pt, @g:=mg gr,@s:=ms,@m:=mm FROM ( SELECT group1 mg,section ms,max(marks) mm FROM mytable WHERE group1>'' GROUP BY group1,section ) m ORDER BY ms,mm desc,mgsqlfiddle/#!2/bea2a2/1
It gives me this list:
| MG | MS | MM | PT | GR | @S:=MS | @M:=MM | ------------------------------------------------------ | G1 | class1 | 55 | 5 | G1 | class1 | 55 | | G10 | class1 | 55 | 5 | G10 | class1 | 55 | | G5 | class1 | 32 | 3 | G5 | class1 | 32 | | G8 | class1 | 22 | 1 | G8 | class1 | 22 | | G9 | class1 | 20 | (null) | G9 | class1 | 20 | | G66 | class2 | 66 | 5 | G66 | class2 | 66 | | G88 | class2 | 65 | 3 | G88 | class2 | 65 | | G4 | class2 | 60 | 1 | G4 | class2 | 60 | | G55 | class2 | 33 | (null) | G55 | class2 | 33 |I am back (26.08.2013, after having asked for help myself, see here) and can now provide the full answer:
SET @s:=@m:=@i:='a'; -- variables *MUST* be "declared" in some -- way, otherwise UPDATE will not work! UPDATE mytable INNER JOIN (SELECT mg,ms,mm, CASE WHEN @s=ms THEN CASE WHEN @m=mm THEN @i WHEN @i>2 THEN @i:=@i-2 ELSE null END ELSE @i:=5 END pt, @s:=ms,@m:=mm FROM ( SELECT group1 mg,section ms,max(marks) mm FROM mytable WHERE group1>'' GROUP BY group1,section ) m ORDER BY ms,mm desc,mg ) t ON mg=group1 AND ms=section AND mm=marks SET Points=ptsee here sqlfiddle/#!2/bb7f2
And finally - off-topic:
Dear user @user2594154, why have you bombarded this board with the same question 8 times?!?
- Add specific points on highest marks Find sum of points and grouping
- First three Groups with Highest Marks should have specific points (this post)
- Grouping results by name and points Group each Sections by Points and
- CompetitionName descending order Find highest points of two students
- in a section Display 3 Maximum values of a Column, include duplicate
- values with condition First three Groups with Highest Marks should
- have specific points 5,3, 1 [duplicate]
It would be much more helpful for everybody if you had kept your question in one post, explaining exactly what you want and also what you have tried yourself (!!). Then, in the course of the answering process, it is possible to edit it, making it more precise. New questions should only be posted, if their subject is actually different, see here.
No hard feelings - I learnt a lot in the process of solving this problem of yours. ;-)
更多推荐
得分最高的前三个组应有特定要点
发布评论