【mysql、sqlserver 4种分组排序函数dense_rank / row_number / rank / ntile】

原创已于 2022-10-12 09:41:52 修改 · 2.3k 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库 #sqlserver #mysql

于 2022-10-11 18:17:26 首次发布

本文介绍了SQL中常用的排序函数，包括row_number(), dense_rank(), rank() 和 ntile() 的使用方法及特点，并通过具体示例展示了如何在数据集中应用这些函数。

mysql高版本版本以上才支持函数（5.0+不支持），sqlserver原则上和mysql函数一致，以下截图来自sqlserver（2019）

--row_number()【概要：不考虑重复值】
--score的排序规则自然递增（不区分score重复）
select *,row_number() over(order by score desc) as 'row_number' from testuser;

--以subject分组，组内score的排序规则自然顺序递增（不区分score相同情况）
select *,row_number() over(partition by subject order by score desc) as 'row_number' from testuser;

--dense_rank()【概要：考虑重复值，序号不间隔】
--score的排序规则递增（相同score序号一致，不同score需要自然递增，无序号间隔）
select *,dense_rank() over(order by score desc) as 'dense_rank' from testuser;

--以subject分组，组内score的排序规则自然顺序递增（相同score序号一致，不同score需要自然递增，无序号间隔）
select *,dense_rank() over(partition by subject order by score desc) as 'dense_rank' from testuser;

--rank()【概要：考虑重复值，序号间隔】
--score的排序规则递增（相同score序号一致，不同score需要占位递增，序号存在间隔）
select *,rank() over(order by score desc) as 'rank' from testuser;

--以subject分组，组内score的排序规则自然顺序递增（相同score序号一致，不同score需要自然递增，存在序号间隔）
select *,rank() over(partition by subject order by score desc) as 'rank' from testuser;

--ntile()【概要：分组使用，区别于上面三种】
--按照排序规则分组，“ntile(3)”括号必须传值，分组数依托此参数
select *,ntile(4) over(order by score desc) as 'ntile' from testuser;

--先按照subject分组，如果分组数小于括号传值，则按照最小分组数
select *,ntile(4) over(partition by subject order by score desc) as 'ntile' from testuser;

补充：

因为没找到自己满意的材料，自己整理发布，仅供参考，不喜勿喷

DDL语句：

DROP TABLE IF EXISTS `testuser`;
CREATE TABLE `testuser` (
`user_id` int NOT NULL AUTO_INCREMENT,
`user_name` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
`score` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
`subject` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = Dynamic;

DML语句：

INSERT INTO `sortFunction` (`user_id`, `user_name`, `score`, `subject`) VALUES (1, 'jame', '80', 'chinese');
INSERT INTO `sortFunction` (`user_id`, `user_name`, `score`, `subject`) VALUES (2, 'tom', '90', 'english');
INSERT INTO `sortFunction` (`user_id`, `user_name`, `score`, `subject`) VALUES (3, 'lucy', '80', 'english');
INSERT INTO `sortFunction` (`user_id`, `user_name`, `score`, `subject`) VALUES (4, 'kate', '70', 'psychology');
INSERT INTO `sortFunction` (`user_id`, `user_name`, `score`, `subject`) VALUES (5, 'jack', '70', 'psychology');
INSERT INTO `sortFunction` (`user_id`, `user_name`, `score`, `subject`) VALUES (6, 'tip', '60', 'psychology');