Recently, when a group sorting query is encountered in mysql, it is suddenly found that there is no group sorting such as row_number() over(partition by colname) in MySQL.
And since there is no ranking function similar to row u number(), rank(), deny u rank() in SQL Server in MySQL, all of the following implementation methods are found. Here is a simple record.

 

First create a table and insert the test data.

create table demo.Student (
   ID int(11) NOT NULL AUTO_INCREMENT,
   StuNo varchar(32) NOT NULL,
   StuName varchar(10) NOT NULL,
   StuAge int(11) DEFAULT NULL,
   PRIMARY KEY (ID)
 )
 engine=innodb auto_increment=1 default charset=utf8 collate=utf8_general_ci;

insert into demo.Student(StuNo,StuName,StuAge) values('A001','Xiao Ming',22);
insert into demo.Student(StuNo,StuName,StuAge) values('A005','petty thief',23);
insert into demo.Student(StuNo,StuName,StuAge) values('A007','Xiaohong',24);
insert into demo.Student(StuNo,StuName,StuAge) values('A003','Xiao Ming',22);
insert into demo.Student(StuNo,StuName,StuAge) values('A002','petty thief',23);
insert into demo.Student(StuNo,StuName,StuAge) values('A004','Xiaohong',24);
insert into demo.Student(StuNo,StuName,StuAge) values('A006','Xiao Wang',25);

select * from demo.Student;

The test data are as follows:

 

Implement row u number() ranking function, and sort by student number (StuNo).

-- @row_number:=0，set variable@row_number The initial value of is 0.
-- @row_number:=@row_number+1，accumulation@row_number The value.
select ID,StuNo,StuName,StuAge,@row_number:=@row_number+1 as row_number 
from demo.Student a,
(
    select @row_number:=0
) b
order by StuNo asc;

The results are as follows:

 

Implement rank() ranking function, and sort by student age.

-- @StuAge:=null，set variable@StuAge The initial value of is null
-- @rank:=0，set variable@rank The initial value of is 0
-- @inRank:=1，set variable@inRank The initial value of is 1
-- if(@StuAge=StuAge,@rank,@rank:=@inRank)，Specifies that when the value of the rank sequence does not change,@rank The value of does not change; when the value of the specified rank changes,@rank The value of jumps to@inRank Value of internal count
-- @inRank:=@inRank+1，Each row is incremented by 1 to realize internal counting
select t.ID,t.StuNo,t.StuName,t.StuAge,t.row_rank 
from 
(
    select ID,StuNo,StuName,StuAge,if(@StuAge=StuAge,@rank,@rank:=@inRank) as row_rank,@inRank:=@inRank+1,@StuAge:=StuAge 
    from demo.Student a,
    (
        select @StuAge:=null,@rank:=0,@inRank:=1 
    ) b 
    order by StuAge asc 
) t;

The results are as follows:

 

Implement the rank function of deny_rank(), and sort by student age (StuAge).

-- @StuAge:=null，set variable@StuAge The initial value of is null
-- @rank:=0，set variable@rank The initial value of is 0
-- if(@StuAge=StuAge,@rank,@rank:=@rank+1)，Specifies that when the value of the rank sequence does not change,@rank The value of does not change; when the value of the specified rank changes,@rank Value of 1
select t.ID,t.StuNo,t.StuName,t.StuAge,t.row_rank 
from 
(
    select ID,StuNo,StuName,StuAge,if(@StuAge=StuAge,@rank,@rank:=@rank+1) as row_rank,@StuAge:=StuAge
    from demo.Student a,
    (
        select @StuAge:=null,@rank:=0 
    ) b 
    order by StuAge asc 
) t;

The results are as follows:

 

Implement row_number() over(partition by colname order by colname) group ranking function, and sort by stuage group.

-- @StuAge:=null，set variable@StuAge The initial value of is null
-- @row_number:=0，set variable@row_number The initial value of is 0
-- if(@StuAge=StuAge,@row_number:=@row_number+1,@row_number:=1)，Specifies that when the value of the rank sequence does not change,@row_number When the value of the specified row sequence changes,@row_number The value of is equal to 1
select t.ID,t.StuNo,t.StuName,t.StuAge,t.row_number 
from 
(
    select ID,StuNo,StuName,StuAge,if(@StuAge=StuAge,@row_number:=@row_number+1,@row_number:=1) as row_number,@StuAge:=StuAge 
    from demo.Student a,
    (
        select @StuAge:=null,@row_number:=0 
    ) b 
    order by StuAge asc 
) t;

The results are as follows:

 

Implement grouping aggregation string, that is, string the value of the specified column.
In SQL Server, intermediate variables are used for implementation, and now it is relatively simple in MySQL.
MySQL provides a group concat() function, which can spell the value of a specified column into a string, and can spell the characters according to the specified sorting method, separated by commas. Here is an example:

select group_concat(StuNo order by StuNo asc) as column1,group_concat(StuNo order by ID asc) as column2 
from demo.Student 

The results are as follows:

 

select StuName,group_concat(StuNo order by StuNo asc) as column1,concat('"',group_concat(StuNo order by StuNo asc),'"') as column2 
from demo.Student 
group by StuName 
order by StuAge 

The results are as follows: