Ranking in our daily life is not unfamiliar, such as the score of the game and so on ranking, all need to use SQL statement query database. However, how to achieve the ranking through SQL statements?

To illustrate, LET’s define a random table and some data to facilitate the following query:

create table `playertable` (
	`id` bigint not null,
	`nickname` varchar(32) not null,
	`age` int not null,
	`score` int not null,
	primary key (`id`)
) engine=InnoDB default charset=utf8mb4;
 
insert into `playertable`
(id, nickname, age, score)
values
(1, "test", 17, 192),
(2, "dev", 11, 12),
(3, "asa", 27, 34),
(4, "err", 34, 112),
(5, "admin", 11, 2233),
(6, "lkkka", 23, 432),
(7, "pop", 71, 675),
(8, "asddw", 33, 23),
(9, "root", 24, 908),
(10, "qwe", 12, 233),
(11, "zxc", 23, 452),
(12, "jkalso", 45, 123),
(13, "plomo", 23, 231),
(14, "llkknsadsa", 7, 659);
Copy the code

1. Basic knowledge

There are a few basic things that need to be covered in this tutorial.

As statement: Used to define aliases. Both tables and variables can define aliases. When a table defines an alias, as can be omitted. For example:

(select * from playertable) as t;
(select * from playertable) t;
Copy the code

The result of the query is defined as t.

Derived table: The result of each select operation can be used as a temporary derived table, and we can even perform further queries in this derived table to filter the data. For example, FROM the top ten players in the above table, I further filter out the nicknames, ages and scores of the top five players in the above table:

select nickname, age, score from (select * from `playertable` order by age limit 10) as temp order by score desc limit 5;
Copy the code

Results:

We can start by looking at the parentheses section, which means that we first select the top ten ages from the player table as a derived table and alias them temp. The derived table is then defined. Normally the parentheses are executed first and can be treated as a whole.

Further filtering is most of the statements outside the parentheses! Note that derived tables must have aliases!

The detailed decomposition can be seen in the picture:

@ symbol in MySQL: you can customize a temporary variable. Set a temporary variable with the following statement:

Set @ variable name := variable value; // or (select @variable name := variable value) as temp;Copy the code

2. Concrete implementation

Basic idea is that define a variable called row is used to represent the current ranking, to define the two temporary derived tables, a derived table is mainly used to row variable initialization, and the table is only a field, that is the row, and each record will add a row of values, the other a derived table is our query data and sort the results. Finally, why not combine the two derived tables?

For example, select the top five players by score and rank them:

select nickname, score, @row := @row + 1 as sequence from (select @row := 0) as ranktable, (select * from `playertable` order by score desc limit 5) as playerinfo;
Copy the code

Results:

The statement is broken up as follows:

Add a where statement to query the rank of a specific player:

select nickname, score, @row := @row + 1 as sequence from (select @row := 0) as ranktable, (select * from `playertable` order by score desc limit 5) as playerinfo where nickname="admin";
Copy the code

Results: