SQL高阶功能：mysql窗口函数，经典TOPN问题

专用窗口函数经典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 ;