MySQL如何实现row_number()及row_number over(partition by column)

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

不足之处还请各位明示。感谢。