窗口函数
mysql8增加的一个极其强大的统计函数
窗口函数加排序
row_number()over() 1,2,3,4,5,6
dense_rank()over() 1,2,2,3,3,4
rank()over() 1,2,2,4,4,6
案例
构建数据表
drop table if exists passing_number;
CREATE TABLE `passing_number` (
`id` int(4) NOT NULL,
`number` int(4) NOT NULL,
PRIMARY KEY (`id`));
INSERT INTO passing_number VALUES
(1,4),
(2,3),
(3,3),
(4,2),
(6,4),
(5,5);测试sql
SELECT
pn.id,
pn.`number`,
RANK() OVER(ORDER BY pn.number DESC) `RANK`,
DENSE_RANK() OVER(ORDER BY pn.number DESC) `DENSE_RANK`,
ROW_NUMBER() OVER(ORDER BY pn.number DESC) `row_number`
FROM
passing_number pn