Mysql本身没有内置row_number()函数,不过我们可以采用表变量的方式来达到类似SQLServer中的row_number()函数的效果。
首先有一个员工表,结构如下
插入数据:
INSERT INTO `test`.`employee` (`id`, `name`, `salary`, `departmentId`) VALUES ('1', 'Joe', '85000', '1');
INSERT INTO `test`.`employee` (`id`, `name`, `salary`, `departmentId`) VALUES ('2', 'Henry', '80000', '2');
INSERT INTO `test`.`employee` (`id`, `name`, `salary`, `departmentId`) VALUES ('3', 'Sam', '60000', '2');
INSERT INTO `test`.`employee` (`id`, `name`, `salary`, `departmentId`) VALUES ('4', 'Max', '90000', '1');
INSERT INTO `test`.`employee` (`id`, `name`, `salary`, `departmentId`) VALUES ('5', 'Janet', '69000', '1');
INSERT INTO `test`.`employee` (`id`, `name`, `salary`, `departmentId`) VALUES ('6', 'Randy ', '82000', '1');
INSERT INTO `test`.`employee` (`id`, `name`, `salary`, `departmentId`) VALUES ('7', 'Will ', '70000', '1');
需求如下:
将员工表按部门departmentId分组,工资salary由高到低进行排序,并赋予排序序号。
SELECT
@sort := CASE WHEN @type = a.departmentId THEN @sort + 1 ELSE 1 END AS sort,
@type := departmentId AS type,
a.*
FROM
employee a
ORDER BY
departmentId ,salary desc;
其中@type是一个临时变量,每次查询都会被赋值为departmentId。@sort的赋值在@type的前面,目的是为了判断当前行的departmentId与上一行的结果是否相同,相同则加1,不同则从1开始重新排序编号。从而实现了分组排序编号的功能,效果与SQLSERVER中的row_number()函数相同。