Shulou(Shulou.com)05/31 Report--

This article mainly introduces the example analysis of getting topN by GROUP BY grouping sorting in MySQL. The article is very detailed and has a certain reference value. Interested friends must read it!

MySQL VERSION: 5.5.45

ENGINE: InnoDB

Problem description:

Get the record of each student's top n in the transcript.

Table structure:

Table data:

Method 1 (user variable):

①, first of all, I get all the results of each group's ranking.

Click (here) to collapse or open

Select @ gp_row:=if (@ name=name,@gp_row+1,1) as gp_row,@name:=name,id,name,grade from td, (select @ gp_row:=0,@name:='') as temp order by name,grade desc

The results are as follows:

② is using the result as an intermediate table to query the data of top N.

Click (here) to collapse or open

Select id,name,grade from (select @ gp_row:=if (@ name=name,@gp_row+1,1) as gp_row,@name:=name,id,name,grade from td, (select @ gp_row:=0,@name:='') as temp order by name,grade desc) as tb_gp_rank where gp_rowa.grade)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.