mysql分组排名

项目需要支持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进行各组数据的条数控制

执行结果

在这里插入图片描述

扩展知识:

  1. sql语句中,使用@来定义一个变量。如:@p
  2. sql语句中,使用:=来给变量赋值,:@p:=123,则变量p的值为123
  3. sql语句中,if(A,B,C)表示,如果A条件成立,那么执行B,否则执行C,
    如: @p := if(20>19,100,200)的结果是,abc的值为100。
  4. case…when…then条件判断语句跟其他库用法一样
  • 2
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值