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
如果想要实现组内排序把要分组的字段作为第一个排序字段即可