LeetCode数据库SQL练习（一）

• 练习题目来自：leetcode-cn.com/

组合两个表

表1: Person

+-------------+---------+
| 列名         | 类型     |
+-------------+---------+
| PersonId    | int     |
| FirstName   | varchar |
| LastName    | varchar |
+-------------+---------+
PersonId 是上表主键

表2: Address

+-------------+---------+
| 列名         | 类型    |
+-------------+---------+
| AddressId   | int     |
| PersonId    | int     |
| City        | varchar |
| State       | varchar |
+-------------+---------+
AddressId 是上表主键

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

FirstName, LastName, City, State

• SQL：

  • select
    p.FirstName, p.LastName, a.City, a.State
    from
    Person p
    left join
    Address a
    on
    p.PersonId = a.PersonId
    

组合两个表

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

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

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

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

• SQL：

  • select
    ifnull(
        (select
        distinct Salary
        from
        Employee
        order by Salary DESC
        limit 1,1),
        null
    ) SecondHighestSalary
    

第N高的薪水

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

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

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

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

• 自定义函数

  • 语法格式如下：

    • CREATE FUNCTION <函数名> ( [ <参数1> <类型1> [ , <参数2> <类型2>] ] … ) 
      RETURNS <类型>
      <函数主体>
      

  • 语法说明如下：
    • <函数名>：指定自定义函数的名称。注意，自定义函数不能与存储过程具有相同的名称。
    • <参数><类型>：用于指定自定义函数的参数。这里的参数只有名称和类型，不能指定关键字 IN、OUT 和 INOUT。
    • RETURNS<类型>：用于声明自定义函数返回值的数据类型。其中，<类型>用于指定返回值的数据类型。
    • <函数主体>：自定义函数的主体部分，也称函数体。所有在存储过程中使用的 SQL 语句在自定义函数中同样适用，包括前面所介绍的局部变量、SET 语句、流程控制语句、游标等。除此之外，自定义函数体还必须包含一个 RETURN<值> 语句，其中<值>用于指定自定义函数的返回值。

• SQL：

  • CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
    BEGIN
      SET N = N-1;
      IF N < 0 THEN
      RETURN NULL;
      ELSE
      RETURN (
          SELECT
          IFNULL(
              (SELECT
              DISTINCT Salary
              FROM
              Employee
              ORDER BY Salary DESC
              LIMIT N, 1
              ),
              NULL) getNthHighestSalary
      );
      END IF;
    END
    

分数排名

编写一个 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    |
+-------+------+

重要提示： 对于 MySQL 解决方案，如果要转义用作列名的保留字，可以在关键字之前和之后使用撇号。例如  Rank

• SQL1：
  • 获取rank：每次select搜索出的score，把重复的分数去掉，可以在另外一个子句中查找比这个score大或者相等的score的个数，作为这个score的排名。

  • select
    t.Score,
        (select
        count(distinct Score)
        from
        Scores
        where Score >= t.Score
        ) `Rank`
    from
        (select
        Score
        from
        Scores
        order by Score DESC) t
    

• SQL2：

  • select
    Score,
    (dense_rank() over(order by Score DESC)) `Rank`
    from
    Scores
    

连续出现的数字

表：Logs

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| num         | varchar |
+-------------+---------+
id 是这个表的主键。

编写一个 SQL 查询，查找所有至少连续出现三次的数字。
返回的结果表中的数据可以按 任意顺序 排列。
查询结果格式如下面的例子所示：

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

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

• SQL1：

  • select distinct l1.Num ConsecutiveNums
    from
    Logs l1,
    Logs l2,
    Logs l3
    where l1.Id = l2.Id - 1
    and l2.Id = l3.Id - 1
    and l1.Num=l2.Num
    and l2.Num=l3.Num
    

• SQL2：

  • select distinct
    a.num
    from logs a
    left join logs b
    on a.id = b.id + 1
    left join logs c
    on a.id = c.id + 2
    where a.num = b.num and a.num = c.num
    

• SQL3：

  • select
    distinct t.num as ConsecutiveNums
    from
        (select
        id, num,
        lag(id, 2, null) over(partition by num order by id) as pre
        from
        Logs) t
    where t.id = t.pre + 2
    

• SQL4：

  • select distinct num as ConsecutiveNums
    from (select num,
             lag(num, 1, null) over (order by id) lag_num,
             lead(num, 1, null) over (order by id) lead_num
          from logs) l
    where l.Num = l.lag_num and l.Num = l.lead_num
    

超过经理收入的员工

Employee 表包含所有员工，他们的经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的 Id。

+----+-------+--------+-----------+
| Id | Name  | Salary | ManagerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | NULL      |
| 4  | Max   | 90000  | NULL      |
+----+-------+--------+-----------+

给定 Employee 表，编写一个 SQL 查询，该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中，Joe 是唯一一个收入超过他的经理的员工。

+----------+
| Employee |
+----------+
| Joe      |
+----------+

• SQL1：

  • select
    Name Employee
    from
    Employee t
    where Salary > (select Salary from Employee where Id = t.ManagerId)
    

• SQL2：

  • select a.Name Employee
    from
    employee a, employee b
    where a.ManagerId = b.Id and a.Salary > b.Salary
    

• SQL3：

  • select a.Name Employee
    from
    employee a join employee b
    WHERE a.ManagerId = b.Id and a.Salary > b.Salary
    

 

查找重复的电子邮箱

编写一个 SQL 查询，查找 Person 表中所有重复的电子邮箱。
示例：

+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+

根据以上输入，你的查询应返回以下结果：

+---------+
| Email   |
+---------+
| a@b.com |
+---------+

说明： 所有电子邮箱都是小写字母。

• SQL1：

  • select
    t.Email
    from
        (select
        Email, count(Email) as c
        from
        Person
        group by Email) t
    where t.c > 1
    

• SQL2：

  • select
    Email
    from
    Person
    group by Email
    having count(Email) > 1
    

• SQL3：

  • select distinct a.Email
    from
    Person a, Person b
    where a.Email = b.Email and a.Id != b.Id
    

从不订购的客户

某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。
Customers 表：

+----+-------+
| Id | Name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+

Orders 表：

+----+------------+
| Id | CustomerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+

例如给定上述表格，你的查询应返回：

+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

• SQL1：

  • select
    Name Customers
    from
    Customers
    where Id not in (select CustomerId from Orders)
    

• SQL2：

  • select
    c.Name Customers
    from
    Customers c left join Orders o
    on c.id = o.CustomerId
    where o.Id is null
    

部门工资最高的员工

Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Jim   | 90000  | 1            |
| 3  | Henry | 80000  | 2            |
| 4  | Sam   | 60000  | 2            |
| 5  | Max   | 90000  | 1            |
+----+-------+--------+--------------+

Department 表包含公司所有部门的信息。

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

编写一个 SQL 查询，找出每个部门工资最高的员工。对于上述表，您的 SQL 查询应返回以下行（行的顺序无关紧要）。

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Jim      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

解释： Max 和 Jim 在 IT 部门的工资都是最高的，Henry 在销售部的工资最高。

• 需要找出工资最高的所有值：(d.id,e.salary) in (select DepartmentId ,max(salary))

• SQL1：

  • select
    d.name as Department, e.name as Employee, e.Salary Salary
    from
    Employee e join Department d
    on e.DepartmentId = d.Id
    where (e.DepartmentId, e.Salary) in (select DepartmentId, max(Salary) from Employee group by DepartmentId)
    

• SQL2：

  • select
    d.name Department ,e.name Employee ,e.Salary Salary
    from
    Employee e, Department d
    where e.DepartmentId = d.id and (d.id, e.salary) in (select DepartmentId ,max(salary) from Employee group by DepartmentId)
    

部门工资前三高的所有员工

Employee 表包含所有员工信息，每个员工有其对应的工号 Id，姓名 Name，工资 Salary 和部门编号 DepartmentId 。

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 85000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
| 5  | Janet | 69000  | 1            |
| 6  | Randy | 85000  | 1            |
| 7  | Will  | 70000  | 1            |
+----+-------+--------+--------------+

Department 表包含公司所有部门的信息。

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

编写一个 SQL 查询，找出每个部门获得前三高工资的所有员工。例如，根据上述给定的表，查询结果应返回：

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Randy    | 85000  |
| IT         | Joe      | 85000  |
| IT         | Will     | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
+------------+----------+--------+

解释： IT 部门中，Max 获得了最高的工资，Randy 和 Joe 都拿到了第二高的工资，Will 的工资排第三。销售部门（Sales）只有两名员工，Henry 的工资最高，Sam 的工资排第二。

• SQL1：

  • select
    d.name as Department, e.name as Employee, e.Salary Salary
    from
    Employee e join Department d
    on e.DepartmentId = d.Id
    where e.Id in
        (select
        t1.Id
        from
        Employee t1 left join Employee t2
        on t1.DepartmentId = t2.DepartmentId and t1.Salary < t2.Salary
        group by t1.Id
        having count(distinct t2.Salary) <= 2)
    and e.DepartmentId in
        (select
        Id
        from
        Department)
    order by d.Id ASC, e.Salary DESC
    

• SQL2：

  • Select
    d.Name Department, e.Name Employee, e.Salary Salary
    from
    (Select
    *
    from
        (select
        *,
        (dense_rank() over (partition by DepartmentId order by Salary desc)) `rank`
        from
        Employee) t
    where `rank`<=3) e join Department d
    on e.DepartmentId = d.Id