专用窗口函数经典TOPN问题、经典排名问题
rank()：考虑并列+占用下一个名次位置
dense_rank()：考虑并列+不占用下一个名次位置
row_number()：不考虑并列，正常排序
创建sql表
CREATE TABLE `score` (
`s_id` varchar(20) NOT NULL,
`c_id` varchar(20) NOT NULL,
`s_score` int DEFAULT NULL,
PRIMARY KEY (`s_id`,`c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
插入SQL数据
INSERT INTO score (s_id,c_id,s_score) VALUES
('0','01',81),
('1','01',81),
('2','01',82),
('3','01',80),
('4','01',80),
('5','01',80),
('6','01',85),
('7','01',80),
('8','01',87),
('9','01',80);
rank() dense_rank() row_number()区别
select *, rank() over (order by s_score desc) as ranking,
dense_rank() over (order by s_score desc) as dese_rank,
row_number() over (order by s_score desc) as row_num
from score ;
