SQL根据多个字段排序，实现并列排序，排序跳跃。如1,1,3,4,5,5,7排列。

例如职工薪资表，包括用户ID,部门ID,工作职能，薪资，以相同部门ID，工作职能，按薪资进行降序排列。薪资相同排序号相同，后面的排序跳增。

/*创建测试表，插入数据*/

DROP TABLE IF EXISTS test.emp_salary;

CREATE TABLE `emp_salary` (

  `empid` VARCHAR(10) DEFAULT NULL,

  `deptid` VARCHAR(10) DEFAULT NULL,

  `job` VARCHAR(30) DEFAULT NULL,

  `salary` DECIMAL(10,2) DEFAULT NULL

) ENGINE=INNODB DEFAULT CHARSET=utf8;

TRUNCATE TABLE emp_salary;

INSERT INTO `emp_salary` VALUES

('0001', 'D040','自然语言处理工程师','25000.00')

,('0002', 'D040','数据采集专员', NULL)

,('0003', 'D040','爬虫开发工程师', '25000.00')

,('0004', 'D040','数据仓库工程师', '30000.00')

,('0005', 'D040','数据仓库工程师', '18000.00')

,('0006', 'D040','数据仓库工程师', '18000.00')

,('0007', 'D040','数据仓库工程师', '16000.00')

,('0008', 'D050','Java开发工程师', '16000.00')

,('0009', 'D050','Java开发工程师', '22000.00')

,('0010', 'D050','Java开发工程师', '22000.00')

,('0011', 'D050','Java开发工程师', '18000.00')

,('0012', 'D050','Java开发工程师', '18000.00');

/*实现按照部门,按工作分组，按salary降序排序项*/

/*  SQL1-1 */

SELECT empid,deptid,job,salary,(CASE WHEN salary IS NULL THEN NULL ELSE rank END ) AS rank

FROM  

( SELECT tmp.empid,tmp.deptid,tmp.job,tmp.salary

,@rownum := @rownum + 1

,IF(@dept = tmp.deptid AND @job = tmp.job AND @salary >= tmp.salary,@rank := @rank + 1,@rank := 1) AS rank

,@dept := tmp.deptid

,@job := tmp.job

,@salary := tmp.salary

FROM

(SELECT empid,deptid,job,salary FROM emp_salary ORDER BY deptid,job,salary DESC) tmp,

(SELECT @rownum := 0,@dept := NULL,@rank := 0) a

) result ;

/* 或者*/

/*  SQL1-2 */

SET @row := 0 ;

SET @curDep := '' ;

SET @curJob := '' ;

SET @curSal := 0 ;

SELECT empid,deptid,job,salary

,CASE WHEN salary IS NULL THEN NULL

WHEN @curDep = deptid AND @curJob = job THEN @row := @row + 1

WHEN @curDep <> deptid OR @curJob <> job THEN @row := 1

END rank

,@curDep := deptid

,@curJob := job

,@curSal := salary

FROM emp_salary

ORDER BY deptid,job,salary DESC;

得到的排序如图1-1

/*  SQL2-1 */

SET @row := 0 ;

SET @curDep := '' ;

SET @curJob := '' ;

SET @curSal := 0 ;

SELECT empid,deptid,job,salary

,CASE WHEN salary IS NULL THEN NULL

WHEN @curDep = deptid AND @curJob = job AND @curSal > salary THEN @row := @row + 1

WHEN @curDep = deptid AND @curJob = job AND @curSal = salary THEN @row := @row  

WHEN @curDep <> deptid OR @curJob <> job THEN @row := 1

END rank

,@curDep := deptid

,@curJob := job

,@curSal := salary

FROM emp_salary

ORDER BY deptid,job,salary DESC;

得到排序如图2-1：

/*  SQL3-1 */

SELECT empid,deptid,job,salary,(CASE WHEN salary IS NULL THEN NULL ELSE rank END) rank

FROM

( SELECT empid,deptid,job,salary

,(SELECT COUNT(1)+1 FROM emp_salary t WHERE t.deptid=s.deptid AND t.job=s.job AND t.salary>s.salary) AS rank

FROM emp_salary s

ORDER BY deptid,job,salary DESC

) result;

得到排序如图3-1：