A Student score table, using SQL statements to find out the top three of each class

Yesterday to a company interview, was stumped by this problem, hey, and lost a good chance.

Come back and think about it.

The table structure and data are as follows:

The SQL statement implemented:

The first implementation is

SELECT * FROM student a where a.id in (SELECT b.id from student b where B.classid=a.classid ORDER by grade DESC LIMIT 0,3) ;
Looks fine, in fact a lot of, first for MySQL, in (can't use limit) there is a syntax error, the second top three not necessarily only 3 bit OH

Try to change: sentence understanding: That is, as long as the class has three students score more than the student, then this student is not the top three.

SELECT * FROM student b
where
Not EXISTS (SELECT * from student C where C.classid=b.classid and B.grade < C.grade GROUP by C.CLASSID have COUNT (*) &G T;3)

Results show

Number of query statistics

Not exists is actually a nested loop. If student has 1000 data, it will query 1+1000 times, if not understood, with the help of Java code

List<student> students =studentservice.queryall ();//Query out all

for (int i=0;i<students.length (); i++) {

Filter top three

Query is not the top three class

}

So a total of 1+n times, n indicates the total number of bars in the student table

Feel not reliable, if there is 10W data, on the query 10W times, it is unbearable, think again, have a good way friends come to onlookers, very grateful!

A Student score table, using SQL statements to find out the top three of each class