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');