mysql查询第一至第五排名 mysql查询第二名

目录

• 简述

• 第N高薪水（连续排名，同薪同名）

• 单表查询
• 子查询方式1
• 子查询方式2
• 自连接
• 笛卡尔积
• 自定义变量
• 开窗函数

• 部门前n高薪水（连续排名，同薪同名）

• 子查询方式
• 连接查询
• 自定义变量
• 开窗函数

简述

最近在刷题和工作中总会遇到前n高，第n高的问题，汇总一下以便日后查看。
排名3种场景（以薪水为例）：

• 连续排名，同薪不同名。3000、2000、2000、1000的排名为1-2-3-4
• 不连续排名，同薪同名。3000、2000、2000、1000的排名为1-2-2-4
• 连续排名，同薪同名。3000、2000、2000、1000的排名为1-2-2-3
  下面的两个例子都以连续排名，同薪同名的情况举例

第N高薪水（连续排名，同薪同名）

如表中所示，如果存在第N高的薪水则返回Salary，如果不存在那么查询应该返回NULL。

单表查询

• 解题思路
  全局排名，不分组，所以我们可以用ORDER BY排序加LIMIT N,M限制（M表示在限制条数之后的offset记录，LIMIT M OFFSET N），排名第N高意思是LIMIT N-1,1，但是LIMIT后面只接受正整数或者单一变量，不能用表达式，所以在函数中需要先SET N = N - 1 同薪同名且连续排名，意味着需要去重，我们可以用GROUP BY 按薪水分组后再ORDER BY或者DISTINCT去重。

MySQL中的LIMIT用法详解

• 基本语法：
  SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
• LIMIT子句用于select中，对输出结果集的行数进行约束，LIMIT接受一个或两个数字参数。参数必须是一个整数常量。offset表示偏移量（指向数据记录的游标），rows表示查询限定返回的最大记录数。当offset参数省略时，默认为0，即LIMIT 3 等同于LIMIT 0,3。

• SELECT * FROM table LIMIT 3, 4;返回第4-7行
• SELECT * FROM table LIMIT 3;返回前3行

2. 代码片段

CREATE FUNCTION getNHighestSalary(N INT) RETURNS INT
BEGIN
    SET N := N-1;
    IF (N < 0) THEN
    	RETURN NULL;
    ELSE
	  RETURN (
    	  SELECT DISTINCT salary FROM employee
    	  -- GROUP BY salary
    	  ORDER BY salary DESC
    	  LIMIT N, 1
  		);
  	END IF;
END

子查询方式1

1. 解题思路
   先查出前n高的薪水，再从中查询最低的薪水（即第n高的薪水），并用COUNT(1)累加用来判断是否有第n高的薪水 。考虑会有相等的薪水所以第一重查询用DISTINCT去重。
2. 代码片段

CREATE FUNCTION getNHighestSalary(N INT) RETURNS INT
BEGIN
	RETURN(
	SELECT IF(count < N, NULL, min) AS Salary
	FROM
		(	
			SELECT MIN(Salary) AS min, COUNT(1) AS count
			FROM
			(
				SELECT  DISTINCT Salary
				FROM Employee ORDER BY Salary DESC LIMIT N
			) a
		) b
	);	
END

子查询方式2

1. 解题思路
   排名第N高意味着表中存在N-1个比其更高的薪水（去重前提下）。
   联表查询出比当前薪水高的有几个，如果这个数量等于N-1，那么返回该薪水。
2. 代码片段

CREATE FUNCTION getNHighestSalary(N INT) RETURNS INT
BEGIN
	RETURN(
	SELECT DISTINCT(e.salary)
	FROM Employee e
	WHERE  (
		SELECT
			COUNT(DISTINCT salary)
		FROM Employee e1 WHERE e1.salary > e.salary
		) = N - 1
	);	
END

自连接

1. 解题思路
   自连接条件为表1的Salary小于表2的Salary，以表1的Salary分组，统计表2的Salary的去重个数
   考虑到第一名的表2的Salary为空，所以采用LEFT JOIN ，当去重个数等于N-1时就是要输出的排名(也可以用JOIN，连接条件为<=，COUNT(DISTINCT e2.Salary) = N)
2. 代码片段

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      SELECT 
          e1.salary
      FROM 
          employee e1 LEFT JOIN employee e2 ON e1.salary < e2.salary
      GROUP BY 
          e1.salary
      HAVING 
          count(DISTINCT e2.salary) = N - 1
  );
END

笛卡尔积

1. 解题思路
   跟子查询方式2相似，不再赘述。
2. 代码片段

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      SELECT 
          e1.salary
      FROM 
          employee e1, employee e2 
      WHERE 
          e1.salary <= e2.salary
      GROUP BY 
          e1.salary
      HAVING 
          count(DISTINCT e2.salary) = N
  );
END

自定义变量

1. 解题思路
   自定义两个变量，@s存储工资，@r存储排名，先按工资排序，查询时更新变量值，当工资相等时排名不变，不相等则排名加一
2. 代码片段

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      SELECT 
          DISTINCT salary 
      FROM 
          (SELECT 
                salary, @r:=IF(@s=salary, @r, @r+1) AS rnk,  @s:= salary 
            FROM  
                employee, (SELECT @r:=0, @s:=NULL)init 
            ORDER BY 
                salary DESC) tmp
      WHERE rnk = N
  );
END

开窗函数

1. 解题思路
   mysql8.0以上版本可以用开窗函数，效率是最好的，常用的三种排名函数如下：

• ROW_NUMBER():连续排名，同薪不同名，3000、2000、2000、1000的排名为1-2-3-4
• RANK(): 不连续排名，同薪同名。3000、2000、2000、1000的排名为1-2-2-4
• DESENSE_RANK():连续排名，同薪同名。3000、2000、2000、1000的排名为1-2-2-3
  这三个函数要和OVER()一起使用，OVER()中的参数通常是PARTITION BY 和 ORDER BY 。例题情况是第三种，所以采用DENSE_RANK()。

2. 代码示例

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
        SELECT 
            DISTINCT salary
        FROM 
            (SELECT 
                salary, dense_rank() over(ORDER BY salary DESC) AS rnk
             FROM 
                employee) tmp
        WHERE rnk = N
  );
END

部门前n高薪水（连续排名，同薪同名）

如表中所示，如果存在部门前N高的薪水则返回DepartmentId + Salary，如果不存在那么查询应该返回NULL。因为只考虑部门和薪水，所以还是连续排名，同薪同名。

子查询方式

1. 解题思路（和第N高薪水的子查询方式2类似）
   工资前N高意味着：有不超过N-1个人的工资比查询结果的工资高。例如求前三高的工资，即有不超过2个人（查询子条件为<=2或<3）的工资比查询结果的工资高（有0个人比第一高工资高；有1个人比第二高工资高；有2个人比第三高工资高）
2. 代码示例

SELECT
  		  d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
		FROM Employee e1
        RIGHT JOIN Department d ON e1.DepartmentId = d.Id
		WHERE
    		3 > (SELECT COUNT(DISTINCT e2.Salary)
        			FROM Employee e2
        			WHERE  e2.Salary > e1.Salary
                		AND e1.DepartmentId = e2.DepartmentId
        ) 
		GROUP BY e1.Salary
		ORDER BY d.`Name`, e1.Salary DESC
;

连接查询

1. 解题思路
   能用子查询解决的问题一般都能用连接来解决
2. 代码示例

SELECT
	    d.name as department, e1.name as employee, e1.salary as salary
	FROM
		Department d LEFT JOIN Employee e1 on d.id = e1.departmentid
					 LEFT JOIN Employee e2 on e1.departmentid = e2.departmentid and 	e1.salary<=e2.salary
	GROUP BY 
		d.name, e1.Salary
	HAVING 
		count(distinct e2.salary)<4
	ORDER BY
    	d.name, e1.salary DESC

自定义变量

1. 解题思路
   自定义三个变量，@s存储工资，@r存储排名，@d存储部门ID，先按部门和工资排序，查询时更新变量值。
   （1）当前部门ID与@d相同(@d=DepartmentId)，则代表是在同一部门中进行的排名，当工资相等(@s=Salary)时排名不变(@r:=@r)，不相等则排名加一(@r:=@r+1)；
   （2）当前部门ID与@d不相同(@d!=DepartmentId)，则说明@d需重新赋值(@d=DepartmentId)，排名也要重新开始，即@r:=1。
2. 代码示例

SELECT
		d. NAME department,
		t. NAME employee,
		salary
	FROM
		(
			SELECT
				*, @r :=IF (DepartmentId = @d, IF (Salary = @s, @r, @r + 1), 1) AS rnk,
				@d := DepartmentId,
				@s := Salary
			FROM employee, (SELECT @s := NULL,@d := NULL, @r := 0 ) init
			ORDER BY DepartmentId, Salary DESC
		) t
	RIGHT JOIN department d ON t.DepartmentId = d.Id
	WHERE t.rnk <= N OR t.rnk IS NULL
	GROUP BY d.`Name`, salary
	ORDER BY DepartmentId, Salary DESC

开窗函数

1. 解题思路
   又到了快乐的开窗函数，因为是同薪同名，连续排名，所以还是用DENSE_RANK()，因为求的是部门前N高薪水，所以按部门分组再按薪水排序，那么开窗函数的使用就是：DENSE_RANK() OVER(PARTITION BY departmentid ORDER BY salary DESC) 。
2. 代码示例

SELECT
	 d.`Name`, tmp.`Name`, tmp.Salary 
	 FROM(
		SELECT 
			e1.DepartmentId, e1.`Name`, e1.Salary,
			DENSE_RANK() OVER(PARTITION BY e1.DepartmentId ORDER BY e1.Salary DESC) rnk
			FROM employee e1 ) tmp
	RIGHT JOIN department d
	ON d.Id = tmp.DepartmentId
	WHERE rnk <= N OR t.rnk IS NULL
	GROUP BY d.name, tmp.Salary
	;