one , Data sheet design
two , Query scenario
Count the examination times of each course , Time of the latest exam , The teacher who entered the score of the latest exam
1, Count the examination times of no courses
# Statistics of examination times select project ' subject ',count(project) ' Examination times ' from score a group by project
Query results ：
2, Time of the latest exam
# Statistics of examination times Time of the latest exam select project ' subject ',count(project) ' Examination times ' ,max(create_time)
from score a group by project
Query results ：
3, Group statistics of teachers who have entered the latest grades
When we grouped to query the latest teachers who entered the grades or the latest grades of various subjects, we found that the data was not the latest .
SELECT a.id, a.edit_teacher, a.project, a.create_time, a.score, count(project)
' Examination times ', max(create_time) ' Latest data time ' FROM score a GROUP BY a.project
Query results ：
But obviously we need to query the data id Should be 4,8,12
It can be seen that when grouping and aggregating, the first data after grouping is queried by default , Then we need to sort our data if we want to query the latest data
SELECT *, count( project ) ' Examination times ', max(create_time) ' Latest data time ' FROM ( SELECT
a.id, a.edit_teacher, a.project, a.create_time, a.score FROM score a ORDER BY
a.id DESC ) b GROUP BY b.project
Query results ：
We found that the data is not the result we want , The sorting in the sub query is invalid
Find all kinds of information on the Internet
Temporary table generated by subquery （ Derived table derived table） Used in order by And make it effective , Three conditions must be met ：
1, External queries prohibit grouping or aggregation
2, External query not specified having,HAVING, order by
3, External queries use derived tables or views as from The only specified source in the sentence
Obviously we are not satisfied , So how to solve it order by Failure ?
Our external table uses group by, Then the temporary table will not be executed filesort operation （ Namely order by Will be ignored ）, So we can add （
distinct(a.id)）.
SELECT *, count( project ) ' Examination times ' , max(create_time) ' Latest data time ' FROM ( SELECT
DISTINCT a.id, a.edit_teacher, a.project, a.create_time, a.score FROM score a
ORDER BY a.id DESC ) b GROUP BY b.project
results of enforcement ：
The result is correct .
Technology