What I want to share with you today is MySQL Data ranking and the acquisition of data ranking under specified conditions , Data ranking is quite common when we do user ranking or other ranking , Of course, we can also calculate the data ranking through other processing methods . Here we mainly share with you MySQl Medium SQL Sentence ranking processing
There is a structure of posting statistics table ：
CREATE TABLE `answers`.`posting_static` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(100) NULL DEFAULT '', `post` int(11) NULL DEFAULT 0, `created_at` timestamp NULL, `updated_at` datetime NULL, PRIMARY KEY (`id`));
Data example ：
We want to get the ranking of all users' posts , How to pinch ？
First of all, we need to sort the number of users' posts , Number the sorted results （ You can also query first, then sort and number ）, This number is the ranking we need , So how to make up this number ？
stay Mysql We can use @rownum To achieve ,
@rownum := @rownum + 1 in := It's the function of assignment , It means to execute first @rownum + 1, Then assign the value to @rownum.
(SELECT @rownum := 0) r The meaning of this sentence is set rownum The initial value of the field is 0, I.e. number from 1 Start .
All data implementation SQL：
SELECT a.*, @rownum := @rownum + 1 AS rank FROM (SELECT @rownum := 0) r, `posting_static` AS a ORDER BY a.post DESC;
Get the ranking of a single user by condition ：
SELECT b.* SELECT FROM(SELECT a.*, @rownum := @rownum + 1 AS rank FROM (SELECT @rownum := 0) r, `posting_static` AS a ORDER BY a.post DESC) as b where b.`username` = ' Wang Wu ';