Pierwsze trzy grupy z najwyższymi znakami powinny mieć określone punkty
mój stół
+------+-------+---------+-------+--------+
| 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
Staram się nadać konkretne punkty pierwszym 3 grupom z najwyższymi ocenami w każdej sekcji. Chciałbym dodać 5 punktów do każdego ucznia w 1 najwyższej grupie, 3 punkty za 2 najwyższą i 1 punkt za 3 najwyższą grupę. . Powielone znaki mogą pojawić się dla grupy.
Używam następującego kodu, ten kod działa dobrze dla poszczególnych studentów, nie wiem jak dawać punkty Grupie.
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
Moje ostateczne wyjście szuka sekcji.
+---------------------------------------------+
| | 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 | |
+---------------------------------------------+
Proszę pomóż mi.