MySQL實現SQL Server次序函數

標籤：group   學生   tun   concat   charset   int   記錄   比較   查詢   

最近在MySQL中遇到分組排序查詢時，突然發現MySQL中沒有row_number() over(partition by colname)這樣的分組排序。
並且由於MySQL中沒有類似於SQL Server中的row_number()、rank()、dense_rank()等次序函數，所有找到以下實現方法，在此簡單記錄一下。

 

首先建立一個表並插入測試資料。

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‘,‘小明‘,22);insert into demo.Student(StuNo,StuName,StuAge) values(‘A005‘,‘小李‘,23);insert into demo.Student(StuNo,StuName,StuAge) values(‘A007‘,‘小紅‘,24);insert into demo.Student(StuNo,StuName,StuAge) values(‘A003‘,‘小明‘,22);insert into demo.Student(StuNo,StuName,StuAge) values(‘A002‘,‘小李‘,23);insert into demo.Student(StuNo,StuName,StuAge) values(‘A004‘,‘小紅‘,24);insert into demo.Student(StuNo,StuName,StuAge) values(‘A006‘,‘小王‘,25);select * from demo.Student;

測試資料如下：

 

實現row_number()次序函數，按學號(StuNo)排序。

-- @row_number:=0，設定變數@row_number的初始值為0。-- @row_number:[email protected]_number+1，累加@row_number的值。select ID,StuNo,StuName,StuAge,@row_number:=@row_number+1 as row_number from demo.Student a,(    select @row_number:=0) border by StuNo asc;

結果如下：

 

實現rank()次序函數，按學生年齡(StuAge)排序。

-- @StuAge:=null，設定變數@StuAge的初始值為null-- @rank:=0，設定變數@rank的初始值為0-- @inRank:=1，設定變數@inRank的初始值為1-- if(@StuAge=StuAge,@rank,@rank:[email protected])，指定排序列的值不變時，@rank的值不變；指定排序列的值變化時，@rank的值跳變為@inRank內部計數的值-- @inRank:[email protected]+1，每一行自增1，用於實現內部計數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;

結果如下：

 

實現dense_rank()次序函數，按學生年齡(StuAge)排序。

-- @StuAge:=null，設定變數@StuAge的初始值為null-- @rank:=0，設定變數@rank的初始值為0-- if(@StuAge=StuAge,@rank,@rank:[email protected]+1)，指定排序列的值不變時，@rank的值不變；指定排序列的值變化時，@rank的值自增1select 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;

結果如下：

 

實現row_number() over(partition by colname order by colname)分組次序函數，按學生年齡(StuAge)分組排序。

-- @StuAge:=null，設定變數@StuAge的初始值為null-- @row_number:=0，設定變數@row_number的初始值為0-- if(@StuAge=StuAge,@row_number:[email protected]_number+1,@row_number:=1)，指定排序列的值不變時，@row_number的值自增1；指定排序列的值變化時，@row_number的值等於1select 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;

結果如下：

 

實現分組彙總字串，即把指定列的值拼成字串。
在SQL Server中時利用了中間變數實現，現在在MySQL中就比較簡單了。
MySQL提供了一個group_concat()函數，可以把指定列的值拼成一個字串，並可以按指定排序方式拼成字元，之間用逗號隔開。如下樣本：

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

結果如下：

 

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 

結果如下：

 

MySQL實現SQL Server次序函數