【Leetcode】数据库题目：第N高的薪水、连续出现的数字、分数排名

177. 第N高的薪水：编写一个 SQL 查询，获取 Employee 表中第 n 高的薪水（Salary）。

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

例如上述 Employee 表，n = 2 时，应返回第二高的薪水 200。如果不存在第 n 高的薪水，那么查询应返回 null。

+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200                    |
+------------------------+

思路：

查询思路类似求第二高的薪水，需注意考虑N不合理的情况。测试第一轮就遇到N为0的情况，所以加上了处理逻辑。详细如下：(group by效率好于distinct)

 

解答

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT AS
BEGIN
    DECLARE P1 INT;
    DECLARE P2 INT;
    if(N<1)
        then set P1=0,P2=0;
    else 
        set P1 = 1, P2 = N-1;
    end if;
    RETURN (
      SELECT (SELECT Salary from Employee group by Salary order by Salary desc Limit P1 Offset P2) as SecondHighestSalary
    );
END

 

177. 连续出现的数字：编写一个 SQL 查询，查找所有至少连续出现三次的数字。

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

例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。

+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+

 

解法一、自关联

select 
    distinct l1.Num AS ConsecutiveNums  # 注意要distinct，因为如果有四天连续的就会有两个结果
from 
    Logs l1 left join Logs l2 on l1.Num = l2.Num
    left join Logs l3 on l2.Num = l3.Num
where 
    l1.id = l2.id -1 and l2.id = l3.id -1 # 符合此条件说明有三个连续的

# 作者：babysmm
# 链接：https://leetcode-cn.com/problems/consecutive-numbers/solution/ke-yi-shi-yong-biao-# # lian-jie-lai-jia-kuai-su-du-by/
# 来源：力扣（LeetCode）

 

解法二（非常简单易懂，速度也不错）
 

select distinct Num ConsecutiveNums
from 
(
    select Num,Id
    from Logs 
    where (Id+1,Num) in (select Id,Num from Logs ) and (Id+2,Num) in (select Id,Num from Logs )
)as temp;

解法三、运用变量（三种最快）

# 法一、
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


# 法二、
select 
    distinct c.num n # 记得要distinct
from
    (
        select 
            a.Num num,
            @count := if(@pre = a.Num,@count+1,1) count, 
            @pre := a.Num pre
        from 
            Logs a,
            (select @pre := null,@count := 0) b
    ) c 
where 
    c.count >= 3 
   

和前两种相比，这种方法的效率更高，且不受Logs表中的Id是否连续的限制，而且可以任意设定某个值连续出现的次数。

 

 

177. 分数排名：编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

+----+-------+
| Id | Score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+

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

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

 

解答一、运用变量

SELECT 
    Score,
    cast((CASE
     WHEN @prev = Score THEN @cur
     WHEN @prev := Score THEN @cur := @cur + 1
     WHEN Score <= 0 THEN @cur := @cur + 1
     END) as signed) AS Rank
FROM 
    Scores,
    (SELECT @cur := 0, @prev := null) r
ORDER BY 
     Score DESC;
    

 

解答二（同样思路，只是用的方式不一）
 

法一、笛卡尔积
select 
    s1.Score,
    count(distinct(s2.score)) Rank
from
    Scores s1,Scores s2
where
    s1.score<=s2.score
group by 
    s1.Id
order by 
    Rank

法二、子查询
select 
    a.Score,
    (
        select count(distinct(b.Score))
        from Scores as b
        where b.Score >= a.Score
    ) as rank
FROM
    Scores as a
order by 
    a.Score desc