Lou Chen

LeetCode中的进阶查询(持续更新)

Lou.Chen2022年11月3日
大约 7 分钟

第N高的薪水

如何查找第2高的数据?

Create table If Not Exists Employee (Id int, Salary int)
Truncate table Employee
insert into Employee (Id, Salary) values ('1', '100')
insert into Employee (Id, Salary) values ('2', '200')
insert into Employee (Id, Salary) values ('3', '300')
  • 同薪同名且总排名连续，例如薪水3000、2000、2000、1000同样的薪水排名结果为1-2-2-3 （去除所有重复的再排名）

方法一：使用子查询和 LIMIT 子句

SELECT
    (SELECT DISTINCT
            Salary
        FROM
            Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1) AS SecondHighestSalary

**方法二：**使用 IFNULL 和 LIMIT 子句

SELECT
    IFNULL(
      (SELECT DISTINCT Salary
       FROM Employee
       ORDER BY Salary DESC
        LIMIT 1 OFFSET 1),
    NULL) AS SecondHighestSalary
  • 连续排名，例如薪水3000、2000、2000、1000排名结果为1-2-3-4，体现同薪不同名，排名类似于编号

方法一：

select max(Salary) SecondHighestSalary 
from Employee
where Salary < (select max(Salary) from Employee)

第N高的薪水

同上一题，去除重复的再排名

方法一：使用 order by 加 limit

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    SET N := N-1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT 
            distinct salary
      FROM 
            employee
      ORDER BY 
            salary DESC
      LIMIT N, 1
  );
END

方法二：使用变量

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
      SELECT 
          DISTINCT salary 
      FROM 
          (SELECT 
                salary, @r:=IF(@p=salary, @r, @r+1) AS rnk,  @p:= salary 
            FROM  
                employee, (SELECT @r:=0, @p:=NULL)init 
            ORDER BY 
                salary DESC) tmp
      WHERE rnk = N
  );
END

详细声明：

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    # i 定义变量接收返回值
    DECLARE ans INT DEFAULT NULL;  
    # ii 执行查询语句，并赋值给相应变量
    SELECT 
        DISTINCT salary INTO ans
    FROM 
        (SELECT 
            salary, @r:=IF(@p=salary, @r, @r+1) AS rnk,  @p:= salary 
        FROM  
            employee, (SELECT @r:=0, @p:=NULL)init 
        ORDER BY 
            salary DESC) tmp
    WHERE rnk = N;
    # iii 返回查询结果，注意函数名中是 returns，而函数体中是 return
    RETURN ans;
END

分数排名-连续排名(分数相同，排名相同)

Create table If Not Exists Scores (Id int, Score DECIMAL(3,2))

Truncate table Scores

insert into Scores (Id, Score) values ('1', '3.5')

insert into Scores (Id, Score) values ('2', '3.65')

insert into Scores (Id, Score) values ('3', '4.0')

insert into Scores (Id, Score) values ('4', '3.85')

insert into Scores (Id, Score) values ('5', '4.0')

insert into Scores (Id, Score) values ('6', '3.65')

如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：

+-------+------+
| Score | Rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+

方法一：

最后的结果包含两个部分，第一部分是降序排列的分数，第二部分是每个分数对应的排名。

思路：假设现在给你一个分数X，如何算出它的排名Rank呢？ 我们可以先提取出大于等于X的所有分数集合H，将H去重后的元素个数就是X的排名。比如你考了99分，但最高的就只有99分，那么去重之后集合H里就只有99一个元素，个数为1，因此你的Rank为1。

SELECT s1.score,( SELECT count( DISTINCT s2.score ) FROM scores s2 WHERE s2.Score >= s1.Score ) AS rank 
FROM
	scores s1 
ORDER BY
	s1.score DESC

方法二：使用变量

select t.score Score,t.rank Rank from scores join
(
    SELECT p.score score , @rank:=@rank+1 rank FROM 
        (SELECT distinct(score) score FROM Scores ORDER BY score DESC) p , (SELECT @rank:=0 ) q
) t 
on scores.score = t.score
order by score desc

若出现执行报错，则应该是 rank为关键字的原因，将rank加单引号即可 'rank'

连续出现N次数字

Create table If Not Exists Logs (Id int, Num int)
Truncate table Logs
insert into Logs (Id, Num) values ('1', '1')
insert into Logs (Id, Num) values ('2', '1')
insert into Logs (Id, Num) values ('3', '1')
insert into Logs (Id, Num) values ('4', '2')
insert into Logs (Id, Num) values ('5', '1')
insert into Logs (Id, Num) values ('6', '2')
insert into Logs (Id, Num) values ('7', '2')

期望结果：

Logs 表：
+----+-----+
| Id | Num |
+----+-----+
| 1  | 1   |
| 2  | 1   |
| 3  | 1   |
| 4  | 2   |
| 5  | 1   |
| 6  | 2   |
| 7  | 2   |
+----+-----+

Result 表：
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+
1 是唯一连续出现至少三次的数字。

方法一：使用变量

SELECT DISTINCT
	t.num ConsecutiveNums 
FROM
	(select num,@r:=if(@n=num,@r:=@r+1,@r:=1) as total,
	@n:=num 
     from Logs,(select @r:=1,@n:=null) init) t 
WHERE
	t.total >=3

或者

select distinct Num as ConsecutiveNums
from (
  select Num, 
    case 
      when @prev = Num then @count := @count + 1
      when (@prev := Num) is not null then @count := 1
    end as CNT
  from Logs, (select @prev := null,@count := null) as t
) as temp
where temp.CNT >= 3

各种排名（内部变量写法）

https://www.cnblogs.com/niniya/p/9046449.htmlopen in new window

CREATE TABLE `employee` (
  `Id` int(11) DEFAULT NULL,
  `Name` varchar(255) DEFAULT NULL,
  `Salary` int(11) DEFAULT NULL,
  `DepartmentId` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO employee (Id, Name, Salary, DepartmentId) VALUES ('9', 'Geney', '100000', '3');
INSERT INTO employee (Id, Name, Salary, DepartmentId) VALUES ('2', 'Henry', '80000', '2');
INSERT INTO employee (Id, Name, Salary, DepartmentId) VALUES ('3', 'Sam', '60000', '2');
INSERT INTO employee (Id, Name, Salary, DepartmentId) VALUES ('10', 'Sary', '60000', '2');
INSERT INTO employee (Id, Name, Salary, DepartmentId) VALUES ('1', 'Joe', '85000', '1');
INSERT INTO employee (Id, Name, Salary, DepartmentId) VALUES ('4', 'Max', '90000', '1');
INSERT INTO employee (Id, Name, Salary, DepartmentId) VALUES ('5', 'Janet', '69000', '1');
INSERT INTO employee (Id, Name, Salary, DepartmentId) VALUES ('6', 'Randy', '85000', '1');
INSERT INTO employee (Id, Name, Salary, DepartmentId) VALUES ('7', 'Will', '80000', '1');
INSERT INTO employee (Id, Name, Salary, DepartmentId) VALUES ('8', 'Tom', '80000', '1');
INSERT INTO employee (Id, Name, Salary, DepartmentId) VALUES ('11', 'Hele', '50000', '1');

按顺序排名(值相同，排名不相同，排名连续)

SELECT
	e.*,@r :=@r + 1 AS rank
FROM
	employee e,
	(SELECT @r := 0) init
ORDER BY
	Salary DESC

按顺序排名(值相同，排名相同，排名连续)

SELECT id,name,salary,departmentid,rank from(
SELECT
	e.*,IF(@s=Salary,@r,@r:=@r+1) AS rank, @s:=Salary
FROM
	employee e,
	(SELECT @r := 0,@s:=NULL) init
ORDER BY
	Salary DESC) t

或者(没有值为nulll则不排名，为0可以排名)：

SELECT id,name,salary,departmentid,case 
  when @s=Salary then @r
	when @s:=salary is not null then @r:=@r+1
end Rank
FROM
	employee e,
	(SELECT @r := 0,@s:=NULL) init
ORDER BY
	Salary DESC

按顺序排名(值相同，排名相同，排名不连续)

SELECT id,name,salary,departmentid,Rank from (
SELECT e.*,@r :=IF (@s = salary ,@r ,@t) as Rank,@t :=@t + 1 ,@s := salary
FROM
	employee e,
	(SELECT @r := 1 ,@s := NULL ,@t := 1) init
ORDER BY
	Salary DESC) t

分组后组内排名(值相同，排名不相同，排名连续)

select id,name,salary,departmentid,Rank from (
SELECT
	e.*,
IF(@d = departmentid ,@r :=@r + 1 ,@r := 1) AS Rank ,@d := departmentid
FROM
	employee e,
	(SELECT @r := 1 ,@d := NULL) init
ORDER BY
	departmentid,
	salary DESC) t

分组后组内排名(值相同,排名相同,排名连续)

IF函数嵌套用法

select id,name,salary,departmentid,Rank from (
SELECT
	e.*,
IF(@d = departmentid ,if(@s=salary,@r,@r:=@r+1) ,@r:=1) AS Rank ,@d := departmentid,@s:=salary
FROM
	employee e,
	(SELECT @r := 0 ,@d := NULL,@s=null) init
ORDER BY
	departmentid,
	salary DESC) t

或者IF函数，case when用法

select id,name,salary,departmentid,Rank from (
SELECT
	e.*,
IF(@d = departmentid ,case 
		when @s=salary then @r
		when @s:=salary then @r:=@r+1 end, @r:=1
) AS Rank ,@d := departmentid
FROM
	employee e,
	(SELECT @r := 0 ,@d := NULL,@s=null) init
ORDER BY
	departmentid,
	salary DESC) t

分组后取前N名/N个

这里取前2名(同名可能有多个)

 select id,name,salary,departmentid,Rank from (
SELECT
	e.*,
IF(@d = departmentid ,case 
		when @s=salary then @r
		when @s:=salary then @r:=@r+1 end, @r:=1
) AS Rank ,@d := departmentid
FROM
	employee e,
	(SELECT @r := 0 ,@d := NULL,@s=null) init
ORDER BY
	departmentid,
	salary DESC) t where rank<=2

或者 注意：count(DISTINCT salary)

SELECT
	*
FROM
	employee e1
WHERE
	(
		SELECT
			count(DISTINCT salary)
		FROM
			employee e2
		WHERE
			e1.departmentid = e2.departmentid
		AND e2.salary > e1.salary
	) < 2
ORDER BY
	departmentid,
	salary DESC

取前2个 注意：count(*)

SELECT
	*
FROM
	employee e1
WHERE
	(
		SELECT
			count(*)
		FROM
			employee e2
		WHERE
			e1.departmentid = e2.departmentid
		AND e2.salary > e1.salary
	) < 2
ORDER BY
	departmentid,
	salary DESC

:= 和 = 的真假判断

-- 初始变量没赋值为 NULL
select @test

-- =不能做赋值操作，所以这里变量无论赋值什么都为 NULL
select @test=2

-- :=可以作为赋值操作 2 
select @test:=2

-- 在mysql中的true用 1 表示，false用 0 表示 
select @test:=true
select @test:=false
-- Hello world
select @test:='Hello world'

-- case when 条件 then 值/列 else 值/列 end  
-- when条件: 值为 0,false,null,'' 都为假
select case when 2=2 then '真' else '假' end  -- 真
select case when 0 then '真' else '假' end  -- 假

-- 因为 = 在这里做判断作用，因为@ca初始为null，即@ca不等于2 , 即条件为 0
select case when @ca=2 then '真' else '假' end  -- 假

-- 这里 := 在这里做赋值作用，所以@ca被赋值为2，即条件为 1 
select case when @ca:=2 then '真' else '假' end  -- 真
select case when @ca:=-2 then '真' else '假' end  -- 真

-- 同理，这里的@ca被赋值为 0,false,null,''空值 则为假 
select case when @ca:=0 then '真' else '假' end  -- 假
select case when @ca:=null then '真' else '假' end  -- 假
select case when @ca:=false then '真' else '假' end  -- 假
select case when @ca:='' then '真' else '假' end  -- 假