【LeetCode】数据库练习题

LeetCode上面公开的几道SQL题结果，欢迎大家讨论。

（https://leetcode-cn.com/problemset/database/）

 

175. 组合两个表【简单】

表1:Person（Id 是主键）

+-------------+---------+
| 列名         | 类型     |
+-------------+---------+
| PersonId    | int     |
| FirstName   | varchar |
| LastName    | varchar |
+-------------+---------+

表2: Address（AddressId 是主键）

+-------------+---------+
| 列名         | 类型    |     
+-------------+---------+
| AddressId   | int     |
| PersonId    | int     |
| City        | varchar |
| State       | varchar |
+-------------+---------+

编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：

FirstName, LastName, City, State

select p.firstname, p.lastname, a.city, a.state
from person p left join address a
on p.personid = a.personid

这里需要注意的是，select后字段最好加上表名，我第一遍没加表名，运行超时了。

 

176. 第二高的薪水【简单】

 

编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary） 。

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

例如上述 Employee 表，SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null。

+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

select ifnull
((select distinct salary from employee order by salary desc limit 1 offset 1), null)
SecondHighestSalary 

select max(a.salary) SecondHighestSalary from employee a
where 1 = (select count(distinct(b.salary)) from employee b where b.salary > a.salary)

select max(salary) SecondHighestSalary 
from employee 
where salary not in
(select max(salary) from employee)

这里需要注意的是，当没有第二高的薪水时，要返回null，ifnull就是起的这样的作用。

 

177. 第N高的薪水【中等】

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

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

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

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

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  set N = N - 1;
  RETURN (
      # Write your MySQL query statement below.
      (select distinct salary from employee group by Salary order by salary desc limit 1 offset N)
  );
END


CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
      select max(salary) from employee a
      where N - 1 = (
            select count(distinct(b.salary)) from employee b where b.salary > a.salary
      )
  );
END

 

178. 分数排名【中等】

编写一个 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,
(select count(distinct score) from scores where score >= s.score) ra