There is a requirement for grouping and then sorting in a recent project. Made for a long time, after the min big (background brother) guidance, finally made out, share to everyone (demo).
One example,
Student Information Sheet
Requirements: Obtain the highest historical scores and relevant information for each student in the same subject (type).
For example, Bob’s highest score in Chinese was 123
SQL:
[size=1em]
[size=1em]
?
?
|
[size=1em]1
|
[size=1em][size=1em]select *from t_test group by name, type order by score desc; |
Results:
[size=1em]
[size=1em]
?
?
|
[size=1em]1
[size=1em]2
[size=1em]3
|
[size=1em][size=1em] [size=1em] [size=1em] [size=1em] [size=1em] [size=1em] [size=1em] [size=1em] [size=1em] [size=1em] [size=1em] [size=1em] |
4. Think differently and use Max to find the highest score
SQL:
[size=1em]
[size=1em]
?
?
|
[size=1em]1
|
[size=1em][size=1em]select *, max(score) as max_score from t_test group by name, type; |
Results:
5. Then join the main table with right JOIN to find the full information
sql:
[size=1em]
[size=1em]
?
?
|
[size=1em]1
[size=1em]2
[size=1em]3
|
[size=1em][size=1em]select a.* from t_test as a right join [size=1em](select name, type, max(score) as max_score from t_test group by name, type) as b on a.name = b.name and a.type = b.type and a.score = b.max_score [size=1em]order by b.name, b.type; |
Results:
Second, the summary
Through Max (), min() can realize the function of sorting before grouping; Full information can be obtained with right Join; Select (name, type) from group by; The same can be achieved: the lowest score of the student information and in addition to the highest score other information (difference set), welcome to exchange learning.