某某茶叶有限公司欢迎您!
金沙棋牌在线 > 必赢棋牌官网 > 用MySQL实现SQL Server的sp_executesql

用MySQL实现SQL Server的sp_executesql

时间:2019-12-29 06:39

最近在MySQL中遇到分组排序查询时,突然发现MySQL中没有row_number() over(partition by colname)这样的分组排序。
并且由于MySQL中没有类似于SQL Server中的row_number()、rank()、dense_rank()等排名函数,所有找到以下实现方法,在此简单记录一下。

什么是排名函数?说实话我也不甚清楚,我知道 order by 是排序用的,那么什么又是排名函数呢?

从MySQL 5.0 开始,支持了一个全新的SQL句法:

 

接下来看几个示例就明白了。

5.0 开始,支持了一个全新的SQL句法:...

首先创建一个表并插入测试数据。

首先建立一个表,随便插入一些数据。

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;

图片 1

测试数据如下:

ROW_NUMBER 函数:直接排序,ROW_NUMBER函数是以上升进行直接排序,并且以连续的顺序给每一行数据一个唯一的序号。(即排名连续)

图片 2

1 -- 以下是根据 U_Pwd 这一列进行排名(升序)
2 select *,
3 '第'+convert(varchar,ROW_NUMBER() over(order by U_Pwd))+'名' RowNum
4 from UserInfo

 

图片 3

实现row_number()排名函数,按学号(StuNo)排序。

RANK 函数:并列排序,在 order by 子句中指定的列,如果返回一行数据与另一行具有相同的值,rank函数将给这些行赋予相同的排名数值。

-- @row_number:=0,设置变量@row_number的初始值为0。
-- @row_number:=@row_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
) b
order by StuNo asc;

在排名的过程中,保持一个内部计数值,当值有所改变时,排名序号将有一个跳跃。(即排名不连续)

结果如下:

1 -- 以下是根据 U_Pwd 这一列进行排名(升序)
2 select *,
3 '第 '+convert(varchar,rank() over(order by U_Pwd))+' 名' RowNum
4 from UserInfo

图片 4

图片 5

 

可以明确的看到有4行数据并列第2名,然后直接就是第6名,这是因为 order by 子句中指定的列 U_Pwd 的值相同。

实现rank()排名函数,按学生年龄(StuAge)排序。

DENSE_RANK 函数:并列排序,这一点与 RANK() 函数类似,order by 子句指定的列的值相同,排名数值相同,但是后面是连续的。(即排名连续)

-- @StuAge:=null,设置变量@StuAge的初始值为null
-- @rank:=0,设置变量@rank的初始值为0
-- @inRank:=1,设置变量@inRank的初始值为1
-- if(@StuAge=StuAge,@rank,@rank:=@inRank),指定排序列的值不变时,@rank的值不变;指定排序列的值变化时,@rank的值跳变为@inRank内部计数的值
-- @inRank:=@inRank+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;
1 -- 以下是根据 U_Pwd 这一列进行排名(升序)
2 select *,
3 '第 '+convert(varchar,DENSE_RANK() over(order by U_Pwd))+' 名' RowNum
4 from UserInfo

结果如下:

图片 6

图片 7

可以看到即使有4行数据并列第2名,但是接下来依然是第3名。

 

NTILE 函数:将查询的结果分发到指定数量的组中。 各个组有编号,编号从1开始。 对于每一行,NTILE 将返回此行所属的组的编号。

实现dense_rank()排名函数,按学生年龄(StuAge)排序。

组中的行数计算方式为 total_num_rows(结果集的总行数) / num_groups(指定的组数)。

-- @StuAge:=null,设置变量@StuAge的初始值为null
-- @rank:=0,设置变量@rank的初始值为0
-- if(@StuAge=StuAge,@rank,@rank:=@rank+1),指定排序列的值不变时,@rank的值不变;指定排序列的值变化时,@rank的值自增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;

如果有余数 n,则前面 n 个组获得一个附加行。因此,可能不会所有组都获得相等数量的行,但是组大小最大只可能相差一行。