Find the first N record statements of each group after grouping Java code test your SQL statement, such as the student id name age class 1 1 1 15 1 2 2 15 1 3 15 1 4 sheets 4 15 2 5 sheets 5 15 2 6 sheets 6 15 2 7 sheets 7 15 3 8 sheets 8 15 3 9 sheets 9 15 3 10 sheets 10 15 5 11 sheets 11 15 5
Now I want to get the first two students in each class. Based on the question, group by class and then take the first two students with id. I believe most people can think of the basic combination: www.2cto.com SELECT. * FROM student a order by. class,. id, and the value is Limit 0, 2. (Note: There is no top n statement in MYSQL, instead of LIMIT .) Limit 0 and n can only get the first n digits, but how can we get the first two digits of each class. Next I will analyze the solution to this question: 1. Obtain the first two digits of each class: SELECT. * FROM student a WHERE (www.2cto.com select count (*) FROM student WHERE class =. class AND id <. id) <2 # traverse all records, and then compare the record with all records in the same class. Only when no more than two persons (including two persons) on the shift are smaller than the record id, this record is regarded as the top 2 of the Record id and then displayed. 2. When all qualified data is obtained, sort BY class and id: Add www.2cto.com order by. class,. id; final result: SELECT. * FROM student a WHERE (select count (*) FROM student WHERE class =. class AND id <. id) <2 order by. class,. id LIMIT 0, 30; traverse all records, and then compare this record with all records in actionid. If the id is smaller than one, this record is identified as the maximum id of the record, and then displayed. Www.2cto.com Java code SELECT a. * FROM messagesend a WHERE (select count (*) FROM messagesend t WHERE t. actionid = a. actionid AND id <a. id) <1