MySQL implementation SQL Server ranking function

When I recently encountered a group sort query in MySQL, I suddenly found that there was no sort of row_number () over (partition by colname) in MySQL.
And since there is no ranking function in MySQL similar to row_number (), rank (), Dense_rank () in SQL Server, all of the following implementations are found here for a simple record.

First create a table and insert the test data.

Create TableDemo. Student (IDint( One) not NULLauto_increment, Stunovarchar( +) not NULL, Stunamevarchar(Ten) not NULL, Stuageint( One)DEFAULT NULL,   PRIMARY KEY(ID)) engine=InnoDB auto_increment=1 defaultCharSet=UTF8 Collate=utf8_general_ci;Insert  intoDemo. Student (Stuno,stuname,stuage)Values('A001','Xiao Ming', A);Insert  intoDemo. Student (Stuno,stuname,stuage)Values('A005','Xiao Li', at);Insert  intoDemo. Student (Stuno,stuname,stuage)Values('A007','Little Red', -);Insert  intoDemo. Student (Stuno,stuname,stuage)Values('A003','Xiao Ming', A);Insert  intoDemo. Student (Stuno,stuname,stuage)Values('A002','Xiao Li', at);Insert  intoDemo. Student (Stuno,stuname,stuage)Values('A004','Little Red', -);Insert  intoDemo. Student (Stuno,stuname,stuage)Values('A006','Xiao Wang', -);Select *  fromDemo. Student;

The test data is as follows:

Implement the Row_number () ranking function, sorted by number (Stuno).

--@row_number: = 0, the initial value of the set variable @row_number is 0. --@row_number: [email protected]_number+1, accumulate @row_number value. SelectId,stuno,stuname,stuage,@row_number:=@row_number+1  asRow_number fromDemo. Student A, (Select @row_number:=0) bOrder  byStunoASC;

The results are as follows:

The rank () ranking function is implemented, sorted by student age (Stuage).

--@StuAge: =null, set the initial value of the variable @stuage to null--@rank: = 0, the initial value of the set variable @rank is 0--@inRank: = 1, the initial value of the set variable @inrank is 1--if (@StuAge =stuage, @rank, @rank: [email protected]), the value of the @rank does not change when the value of the row sequence is constant, the value of the @rank jumps into the value of the @inrank internal count when the value of the row sequence is changed --@inRank: [email protected]+1, each line increased by 1 for internal countingSelectT.id,t.stuno,t.stuname,t.stuage,t.row_rank from (    SelectId,stuno,stuname,stuage,if(@StuAge=Stuage,@rank,@rank:=@inRank) asRow_rank,@inRank:=@inRank+1,@StuAge:=Stuage fromDemo. Student A, (Select @StuAge:=NULL,@rank:=0,@inRank:=1) bOrder  byStuageASC) T;

The results are as follows:

Implement the Dense_rank () ranking function, sorted by Student age (Stuage).

--@StuAge: =null, set the initial value of the variable @stuage to null--@rank: = 0, the initial value of the set variable @rank is 0--if (@StuAge =stuage, @rank, @rank: [email protected]+1], the value of the @rank does not change when the value of the row sequence is constant, the value of the @rank is increased by 1 when the value of the row sequence is changed .SelectT.id,t.stuno,t.stuname,t.stuage,t.row_rank from (    SelectId,stuno,stuname,stuage,if(@StuAge=Stuage,@rank,@rank:=@rank+1) asRow_rank,@StuAge:=Stuage fromDemo. Student A, (Select @StuAge:=NULL,@rank:=0) bOrder  byStuageASC) T;

The results are as follows:

Implements the Row_number () over (partition by colname ORDER by colname) grouping ranking function, sorted by Student age (Stuage).

--@StuAge: =null, set the initial value of the variable @stuage to null--@row_number: = 0, the initial value of the set variable @row_number is 0--if (@StuAge =stuage, @row_number: [Email protected]_number+1, @row_number: =1), the value of @row_number is increased by 1 when the value of the row sequence is not changed Specifies that the value of the @row_number is equal to 1 when the value of the row sequence is changedSelectT.id,t.stuno,t.stuname,t.stuage,t.row_number from (    SelectId,stuno,stuname,stuage,if(@StuAge=Stuage,@row_number:=@row_number+1,@row_number:=1) asRow_number,@StuAge:=Stuage fromDemo. Student A, (Select @StuAge:=NULL,@row_number:=0) bOrder  byStuageASC) T;

The results are as follows:

Implements a grouped aggregate string, that is, the value of the specified column is spelled into a string.
The use of intermediate variable implementations in SQL Server is now relatively straightforward in MySQL.
MySQL provides a group_concat () function that can be used to spell the values of a specified column into a string, and can be spelled into characters in a specified sort order, separated by commas. The following example:

Select Order  by ASC  as Order  by ASC  as   from

The results are as follows:

SelectStuname,group_concat (StunoOrder  byStunoASC) asColumn1,concat ('"', Group_concat (StunoOrder  byStunoASC),'"') asColumn2 fromDemo. StudentGroup  byStunameOrder  byStuage

The results are as follows:

MySQL implementation SQL Server ranking function