SQL练习题（二）

目录

1.第N高的薪水(177)

2.分数排名(178)

---

1.第N高的薪水(177)

表: Employee

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| salary      | int  |
+-------------+------+
Id是该表的主键列。
该表的每一行都包含有关员工工资的信息。

编写一个SQL查询来报告 Employee 表中第 n 高的工资。如果没有第 n 个最高工资，查询应该报告为 null 。

示例 1:

输入: 
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
n = 2
输出: 
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200                    |
+------------------------+

示例 2:

输入: 
Employee 表:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
+----+--------+
n = 2
输出: 
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| null                   |
+------------------------+

两种解法:我想到的就是最简单的分页，直接用现成的limit就可以返回我们想要的数据。

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
declare m INT;
SET m = N-1;
  RETURN (
      # Write your MySQL query statement below.
      select ifnull
      (
          (
              select distinct salary
              from employee
              order by salary desc
              limit 1 offset m
          ), null
      )
  );
END

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
declare m INT;
SET m = N-1;
  RETURN (
      # Write your MySQL query statement below.
      select ifnull
      (
          (
              select distinct salary
              from employee
              order by salary desc
              limit m,1
          ), null
      )
  );
END

2.分数排名(178)

表: Scores

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| score       | decimal |
+-------------+---------+
Id是该表的主键。
该表的每一行都包含了一场比赛的分数。Score是一个有两位小数点的浮点值。

编写 SQL 查询对分数进行排序。排名按以下规则计算:

• 分数应按从高到低排列。
• 如果两个分数相等，那么两个分数的排名应该相同。
• 在排名相同的分数后，排名数应该是下一个连续的整数。换句话说，排名之间不应该有空缺的数字。

按 score 降序返回结果表。

示例 1:

输入: 
Scores 表:
+----+-------+
| id | score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+
输出: 
+-------+------+
| score | rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+

答案：

方法一（力扣）

dense_rank() over
作用：查出指定条件后的进行排名，条件相同排名相同，排名间断不连续。 说明：和rank() over 的作用相同，区别在于dense_rank() over 排名是密集连续的。例如学生排名，使用这个函数，成绩相同的两名是并列，下一位同学接着下一个名次。即：1 1 2 3 4 5 5 6

# Write your MySQL query statement below
SELECT 
    score,tmp.rank
FROM 
    (SELECT 
        score, dense_rank() over (ORDER BY score DESC) AS 'rank'
      FROM 
        scores) tmp
ORDER BY score DESC

方法二（力扣）

第一步：对去重后的分数进行排序，并增加一个自增列，得到去重排序

@rank_no:=0，设置变量@rank_no的初始值为0。

@rank_no:=@rank_no+1，累加@rank_no的值。

select score,(@rank_no := @rank_no + 1) as 'rank'
from 
    (select distinct score from scores) s,
    (select @rank_no := 0) t
order by score desc

第二步：把上面的结果和我们的scores表关联，最终得到没去重的排序。

select
    a.score,
    b.rank
from 
    Scores a
    inner join
    (第一步的数据) b
    on a.score = b.score
order by a.score desc;

select
    a.score,
    b.rank
from 
    Scores a
    inner join
    (select score,(@rank_no := @rank_no + 1) as 'rank'
    from (select distinct score from scores) s,(select @rank_no := 0) t
    order by score desc) b
    on a.score = b.score
order by a.score desc;