MySQL使用row_number()及row_number over(partition by column)
在oracle中在处理某些数据的时候使用row_number() over(partition by a order by b desc)函数是相当方便的。遗憾的这么顺手的工具在MySQL中确没有。要想用只能自己想办法了。
首先我们来实现row_number()
先建个表
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
`record_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '记录id',
`employee_id` int(10) NULL DEFAULT NULL COMMENT '员工编号',
`employee_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '员工名称',
`group_no` int(10) NULL DEFAULT NULL COMMENT '员工所属组号',
`salary` decimal(10, 2) NULL DEFAULT NULL COMMENT '薪水',
PRIMARY KEY (`record_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 13 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of employee
-- ----------------------------
INSERT INTO `employee` VALUES (1, 1, '张三', 1, 1000.00);
INSERT INTO `employee` VALUES (3, 2, '李四', 1, 2000.00);
INSERT INTO `employee` VALUES (5, 3, '王五', 2, 2001.00);
INSERT INTO `employee` VALUES (7, 4, '赵五', 2, 1996.00);
INSERT INTO `employee` VALUES (9, 5, '马六', 3, 1998.00);
INSERT INTO `employee` VALUES (11, 6, '吴七', 4, 2005.00);
就围绕上面的表来写。我们查询出数据后希望增加一列排序。那么我们只需要向下面这样写就可以。
select x.*,(@r:=@r+1) as rank from (
select e.record_id,e.employee_id,e.employee_name,e.group_no,e.salary
from employee e
order by e.salary desc) x,(select @r:=0) r
rank就是我们查询结果的排序值了。
接下来:row_number(@partition by column)
注意这里只有分组,所以我们需要先将数据排好序 如果我们要将员工按照部门分组,薪资多到少递减。我们先将数据处理好后,来增加排序列,SQL查询出结果后以group_no分组,新增出来的排序列为rank,在外面套了一层,只取了需要的字段及rank列。直接上SQL。
select z.employee_id,z.employee_name,z.group_no,z.salary,z.rank from(
select x.*,@rownum:=@rownum+1,if(@part=x.group_no,@r:=@r+1,@r:=1) as rank,@part:=x.group_no from (
select e.record_id,e.employee_id,e.employee_name,e.group_no,e.salary
from employee e
order by e.group_no,e.salary desc) x,(select @rownum:=0,@part:=null,@r:=0) rt )z
不足之处还请各位明示。感谢。