业务场景 SQL 练习

Hefery 的个人网站 Hefery's Personal Website 本文由博客端 http://hefery.icu 主动推送

公司场景

176.第二高的薪水

题目

获取 Employee 表中第二高的薪水(Salary)

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

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

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

题解

ORDER BY DESC + DISTINCT + LIMIT

薪资降序排序,然后使用 LIMIT 子句获得第二高的薪资

PS

# 临时表
SELECT
    (SELECT DISTINCT Salary
     FROM Employee
     ORDER BY Salary DESC
     LIMIT 1 OFFSET 1
    ) AS SecondHighestSalary

# IFNULL
SELECT
    IFNULL(
      (SELECT DISTINCT Salary
       FROM Employee
       ORDER BY Salary DESC
       LIMIT 1 OFFSET 1), NULL
    ) AS SecondHighestSalary

177.第 N 高的薪水

题目

获取 Employee 表中第 n 高的薪水(Salary)

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

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

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

题解

ORDER BY DESC + DISTINCT + LIMIT

薪资降序排序,然后使用 LIMIT 子句获得第二高的薪资

PS

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

181. 超过经理收入的员工

题目

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

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

查询可以获取收入超过他们经理的员工的姓名。在上面的表格中,Joe 是唯一一个收入超过他的经理的员工

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

题解

自查询

# WHERE
SELECT
    a.name AS Employee 
FROM
    Employee AS a,
    Employee AS b
WHERE
    a.ManagerId = b.Id AND a.Salary > b.Salary

# JOIN,更常用也更有效
SELECT a.name AS Employee 
FROM Employee AS a
JOIN Employee AS b
ON a.ManagerId = b.Id AND a.Salary > b.Salary

184. 部门工资最高的员工

题述

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  |
+------------+----------+--------+
PSMax  Jim  IT 部门的工资都是最高的,Henry 在销售部的工资最高

题解

使用 JOININ 语句

SELECT 
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    Salary
FROM
    Employee
JOIN Department ON Employee.DepartmentId = Department.Id
WHERE
    (Employee.DepartmentId , Salary) IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
    )

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

题述

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    |
+----+----------+

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

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Randy    | 85000  |
| IT         | Joe      | 85000  |
| IT         | Will     | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
+------------+----------+--------+
PSIT 部门中,Max 获得了最高的工资,Randy  Joe 都拿到了第二高的工资,Will 的工资排第三。销售部门(Sales)只有两名员工,Henry 的工资最高,Sam 的工资排第二

题解

使用 JOIN 和子查询

# JOIN + 子查询
SELECT
    d.Name AS 'Department', 
    e1.Name AS 'Employee', 
    e1.Salary
FROM
    Employee e1
JOIN Department d ON e1.DepartmentId = d.Id
WHERE
    3 > (SELECT COUNT(DISTINCT e2.Salary)
         FROM Employee e2
         WHERE e2.Salary > e1.Salary AND e1.DepartmentId = e2.DepartmentId )

# 子查询
SELECT
	Department.NAME AS Department,
	e1.NAME AS Employee,
	e1.Salary AS Salary 
FROM
	Employee AS e1, 
        Department
WHERE
	e1.DepartmentId = Department.Id 
	AND 3 > (SELECT count( DISTINCT e2.Salary ) 
		 FROM Employee AS e2 
		 WHERE	e1.Salary < e2.Salary AND e1.DepartmentId = e2.DepartmentId) 
ORDER BY Department.NAME,Salary DESC;

# dense_rank函数,找到每个部门最高,然后取dense_rank<=3的结果
SELECT 
    B.name AS Department,
    A.Employee,
    A.Salary 
FROM 
    (
        SELECT 
            DepartmentId,
            name AS employee,
            salary,
            dense_rank() over (partition by departmentid order by salary desc) as rk
        FROM employee
    ) AS A
LEFT JOIN department B ON A.departmentid = B.id
WHERE A.rk <= 3

183.从不订购的客户

题述

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

Customers 表:

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

Orders 表:

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

查询应返回:

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

题解

使用子查询和 NOT IN 子句

SELECT c.name AS Customers 
FROM Customers AS c
WHERE c.id NOT IN
    (
        SELECT CustomerId FROM Orders 
    )

182.查找重复的电子邮箱

题述

查找 Person 表中所有重复的电子邮箱

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

查询应返回以下结果:

+---------+
| Email   |
+---------+
| a@b.com |
+---------+
PS:所有电子邮箱都是小写字母

题解

# GROUP BY + HAVING
SELECT Email
FROM Person
GROUP BY Email HAVING COUNT(Email) > 1

# 子查询
SELECT DISTINCT a.Email
FROM Person a, Person b
WHERE a.Email=b.Email AND a.Id!=b.Id

196.删除重复的电子邮箱

题述

删除 Person 表中所有重复的电子邮箱,重复的邮箱里只保留 Id 最小的那个

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+

在运行你的查询语句之后,上面的 Person 表应返回以下几行:

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+

题解

DELETE p1 
FROM Person p1, Person p2
WHERE p1.Email=p2.Email AND p1.Id>p2.Id

学校场景

  • 数据库

    据说 99% 的性能瓶颈都在数据库。

    320 引用 • 602 回帖
  • MySQL

    MySQL 是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,目前属于 Oracle 公司。MySQL 是最流行的关系型数据库管理系统之一。

    625 引用 • 528 回帖 • 445 关注

欢迎来到这里!

我们正在构建一个小众社区,大家在这里相互信任,以平等 • 自由 • 奔放的价值观进行分享交流。最终,希望大家能够找到与自己志同道合的伙伴,共同成长。

注册 关于
请输入回帖内容 ...