WHCSRL 技术网

Oracle中row_number() over partition by 转换mysql

Oracle中row_number() over partition by 转换mysql

row_number() over (partition by col1 order by col2)表示根据col1分组,在分组内部根据col2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的)

Oracle中的用法

-- 新建一个表
CREATE TABLE td_school
   (  
  name VARCHAR2(30),    -- 姓名
  age int,              -- 年龄
  class VARCHAR2(30),   -- 班级
  subject VARCHAR2(30), -- 学科
  achievement int       -- 成绩
   ) 
   
-- 插入数据
insert into td_school (name,age,class,subject,achievement) values ('小明',18,'001','语文',80);
insert into td_school (name,age,class,subject,achievement) values ('小明',18,'001','数学',60);
insert into td_school (name,age,class,subject,achievement) values ('小明',18,'001','英语',100);
insert into td_school (name,age,class,subject,achievement) values ('小红',20,'001','语文',90);
insert into td_school (name,age,class,subject,achievement) values ('小红',20,'001','数学',80);
insert into td_school (name,age,class,subject,achievement) values ('小红',20,'001','英语',70);
insert into td_school (name,age,class,subject,achievement) values ('小王',21,'002','语文',100);
insert into td_school (name,age,class,subject,achievement) values ('小王',21,'002','数学',90);
insert into td_school (name,age,class,subject,achievement) values ('小黄',18,'002','语文',30);
insert into td_school (name,age,class,subject,achievement) values ('小张',18,'002','语文',70);

-- 按照姓名分组并已成绩由低到高排序
SELECT t.*,row_number() over (partition by t.name order by t.achievement) rn from td_school t

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25

最终的查询结果

mysql中的用法

-- 新建一个表
create table `td_school`
   (  
  `name` varchar(30),      -- 姓名
  `age` int,               -- 年龄
  `class` varchar(30),     -- 班级
  `subject` varchar(30),   -- 学科
  `achievement` int        -- 成绩
   ) 

-- 插入数据
insert into td_school (name,age,class,subject,achievement) values ('小明',18,'001','语文',80);
insert into td_school (name,age,class,subject,achievement) values ('小明',18,'001','数学',60);
insert into td_school (name,age,class,subject,achievement) values ('小明',18,'001','英语',100);
insert into td_school (name,age,class,subject,achievement) values ('小红',20,'001','语文',90);
insert into td_school (name,age,class,subject,achievement) values ('小红',20,'001','数学',80);
insert into td_school (name,age,class,subject,achievement) values ('小红',20,'001','英语',70);
insert into td_school (name,age,class,subject,achievement) values ('小王',21,'002','语文',100);
insert into td_school (name,age,class,subject,achievement) values ('小王',21,'002','数学',90);
insert into td_school (name,age,class,subject,achievement) values ('小黄',18,'002','语文',30);
insert into td_school (name,age,class,subject,achievement) values ('小张',18,'002','语文',70);

-- 按照姓名分组并已成绩由低到高排序
select
	r.name,r.age,r.class,r.subject,r.achievement,rn
from
	(
		select
			t.*,@rownum := @rownum + 1,
		if (
			@pdept = t.name,                  -- 定义一个变量,给变量赋值需要分组的字段
			@rank := @rank + 1 ,@rank := 1    -- 每次循环到这个字段rank就加1
		) as rn,
		@pdept := t.name                      
	from
		(
			select * from td_school order by name,achievement
		) t,
		(
			select @rownum := 0, @grade := null ,@pdept := null ,@rank := 0
		) a
	) r;

-- 如果需要多项分组可使用concat连接
select
	r.name,r.age,r.class,r.subject,r.achievement,rn
from
	(
		select
			t.*,@rownum := @rownum + 1,
		if (
			@pdept = concat(t.name,t.class),
			@rank := @rank + 1 ,@rank := 1
		) as rn,
		@pdept := concat(t.name,t.class)
	from
		(
			select * from td_school order by name,class,achievement
		) t,
		(
			select @rownum := 0, @grade := null ,@pdept := null ,@rank := 0
		) a
	) r;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63

最终的查询结果
在这里插入图片描述

推荐阅读