SQL排序 并列 跳增

SQL排序 并列 跳增

SQL窗口函数排序,可以实现并列,跳增,按位排序.

1.rank函数：计算排序时，如果存在相同位次的记录，则会跳过之后的位次。

2.dense_rank函数：同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。

3.row_number函数：赋予唯一的连续位次。

MySQL从8.0开始支持窗口函数,那么,没有窗口函数,我们的排序实现可以用动态变量来实现。

 

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

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

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

DROP TABLE IF p_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 */

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：