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

最终的查询结果

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;

最终的查询结果