项目需要支持mysql,sqlserver,oracle三个库。有个小盆友在oracle上开发了一段sql,把rank over()代码copy到mysql库中执行报错。mysql系统函数中没有rank over()这个排名函数。说下替代方案。
/**建表*/
CREATE TABLE T_PARTITION_RANK (
ID INT NOT NULL COMMENT '编号',
course varchar(50) NULL COMMENT '课程名称',
stuname varchar(50) NULL COMMENT '学生姓名',
score int NULL COMMENT '得分',
PRIMARY KEY (ID)
)ENGINE=InnoDB CHARSET=utf8 COMMENT='分组排序表';
/**初始化数据*/
insert into T_PARTITION_RANK(ID,course,stuname,score) VALUES (1,'语文','小王', 80);
insert into T_PARTITION_RANK(ID,course,stuname,score) VALUES (2,'语文','小明', 90);
insert into T_PARTITION_RANK(ID,course,stuname,score) VALUES (3,'语文','小张', 100);
insert into T_PARTITION_RANK(ID,course,stuname,score) VALUES (4,'数学','小王', 80);
insert into T_PARTITION_RANK(ID,course,stuname,score) VALUES (5,'数学','小明', 90);
insert into T_PARTITION_RANK(ID,course,stuname,score) VALUES (6,'数学','小张', 100);
insert into T_PARTITION_RANK(ID,course,stuname,score) VALUES (7,'外语','小王', 80);
insert into T_PARTITION_RANK(ID,course,stuname,score) VALUES (8,'外语','小明', 90);
insert into T_PARTITION_RANK(ID,course,stuname,score) VALUES (9,'外语','小张', 100);
insert into T_PARTITION_RANK(ID,course,stuname,score) VALUES (10,'语文','小红', 90);
insert into T_PARTITION_RANK(ID,course,stuname,score) VALUES (11,'数学','小红', 90);
insert into T_PARTITION_RANK(ID,course,stuname,score) VALUES (12,'外语','小红', 90);
/**查询*/
select * from T_PARTITION_RANK;
1.按学科分组,按分数高低排序
SELECT ID,course,stuname,score,rank
FROM
(
SELECT T_PARTITION_RANK.*,IF(@p=course,@r:=@r+1,@r:=1) AS rank,@p:=course
FROM T_PARTITION_RANK,(SELECT @p:=NULL,@r:=0)r
ORDER BY course,score
)s;
sql说明:
(1)(SELECT @p:=NULL,@r:=0) 定义两个变量并赋初始值
(2)用IF判断,这个相当于一个三元运算。如果course相同,排名加1,如果不同则从1开始排
执行结果:
2.按学科分组,组内相同分数排名相同
SELECT ID,course,stuname,score,rank
FROM
(
SELECT *,
IF(@p=course,
CASE
WHEN @s=score THEN @r
WHEN @s:=score THEN @r:=@r+1
END,
@r:=1 ) AS rank,
@p:=course,
@s:=score
FROM T_PARTITION_RANK,(SELECT @p:=NULL,@s:=NULL,@r:=0)r
ORDER BY course,score
)s;
sql说明:
(1)(SELECT @p:=NULL,@s:=NULL,@r:=0) 定义三个变量并赋初始值,
@p用于记录上一条数据的课程值
@s用于记录上一条数据的分数
@r用于记录排名值
(2)用IF判断排名值,IF中通过判断变量@p=course是否相同,如果相同,再用CASE WEHN判断分数排名:
如果score 相同,排名加1,如果不同则从1开始排
(3)排名值更新完成后,我们需要跟新课程和分数的值 @p:=course, @s:=score 即把当前的课程和分数赋值对应的变量
执行结果:
3.按学科分组,取每组分数第一名
SELECT ID,course,stuname,score,rank
FROM
(
SELECT *,
IF(@p=course,
CASE
WHEN @s=score THEN @r
WHEN @s:=score THEN @r:=@r+1
END,
@r:=1 ) AS rank,
@p:=course,
@s:=score
FROM T_PARTITION_RANK,(SELECT @p:=NULL,@s:=NULL,@r:=0)r
ORDER BY course,score DESC
)s
WHERE rank <2;
sql说明:
同2一致,只是用rank进行各组数据的条数控制
执行结果
扩展知识:
- sql语句中,使用@来定义一个变量。如:@p
- sql语句中,使用:=来给变量赋值,:@p:=123,则变量p的值为123
- sql语句中,if(A,B,C)表示,如果A条件成立,那么执行B,否则执行C,
如: @p := if(20>19,100,200)的结果是,abc的值为100。 - case…when…then条件判断语句跟其他库用法一样