oracle的排序函数以及mysql使用变量实现排序

oracle的排序函数

• rank()函数：跳跃排序，如果两个第一，则后边是第3
• dense_rank()函数：连续排序，，再如两个第一，则后边是第2
• row_number()函数：连续排序，没有并列的情况

create table ccx_test(
	course varchar(10),
	score int 
);
insert into ccx_test values(1,70);
insert into ccx_test values(1,100);
insert into ccx_test values(1,80);
insert into ccx_test values(1,90);
insert into ccx_test values(1,60);
insert into ccx_test values(2,70);
insert into ccx_test values(2,80);
insert into ccx_test values(2,60);
insert into ccx_test values(2,70);
insert into ccx_test values(2,50);

--row_number()顺序排序
select 
    course
		,score
    ,row_number() over(partition by course order by score desc) as rank
from ccx_test;

--rank() 跳跃排序，如果两个第一，则接下来是第三
select 
    course
		,score
    ,rank() over(partition by course order by score desc) rank
from ccx_test;

--dense_rank() 连续排序，如果两个第一，则接下来是第二
select 
    course
		,score
    ,dense_rank() over(partition by course order by score desc) rank
from ccx_test;

mysql使用变量实现oracle的rank排序

CREATE TABLE ccx_test_weather(
  id int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  record_date date NULL DEFAULT NULL COMMENT '日期',
  temperature int(10) NULL DEFAULT NULL COMMENT '温度',
  PRIMARY KEY (id) USING BTREE
);

INSERT INTO ccx_test_weather VALUES (1, '2023-03-01', 10);
INSERT INTO ccx_test_weather VALUES (2, '2023-03-02', 25);
INSERT INTO ccx_test_weather VALUES (3, '2023-03-03', 20);
INSERT INTO ccx_test_weather VALUES (4, '2023-03-04', 30);
INSERT INTO ccx_test_weather VALUES (5, '2023-03-05', 35);
INSERT INTO ccx_test_weather VALUES (6, '2023-03-05', 35);
INSERT INTO ccx_test_weather VALUES (7, '2023-03-06', 20);
INSERT INTO ccx_test_weather VALUES (8, '2023-03-07', 40);
select * from ccx_test_weather

普通排序（直接挨个加1）

select c.*, 
	 @rn := @rn + 1 rn
from ccx_test_weather c, (select @rn := 0) init
order by temperature

并列排序（数据相同则排名相同,顺序排名）

select c.*, 
	@rn := if(@tmp = temperature, @rn, @rn + 1) rn
	,@tmp := temperature
from ccx_test_weather c, (select @rn := 0, @tmp := NULL, @incrn := 1) init
order by temperature

并列排序（数据相同则排名相同，跳跃排名）

select c.*, 
	@rn := if(@tmp = temperature, @rn, @incrn) rn
 	,@tmp := temperature
 	,@incrn := @incrn + 1
from ccx_test_weather c, (select @rn := 0, @tmp := NULL, @incrn := 1) init
order by temperature

如果想要实现组内排序把要分组的字段作为第一个排序字段即可