登录 注册

业务场景 SQL 练习

本贴最后更新于 1013 天前，其中的信息可能已经事过景迁

公司场景

176.第二高的薪水

题目

获取 Employee 表中第二高的薪水（Salary）

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

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

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

题解

ORDER BY DESC + DISTINCT + LIMIT

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

PS

  • 去重（DISTINCT）
  • 考虑第二高的薪资不存在的情况
# 临时表
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

  • 去重（DISTINCT）
  • 考虑第二高的薪资不存在的情况
  • LIMIT 里面不能做运算，所以要处理下 N 的值
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  |
+------------+----------+--------+
PS：Max 和 Jim 在 IT 部门的工资都是最高的，Henry 在销售部的工资最高

题解

使用 JOIN 和 IN 语句

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  |
+------------+----------+--------+
PS：IT 部门中，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

学校场景

至少连续出现 3 次的字段

-- user_info(userId, username)  连续出现3次的username
SELECT DISTINCT 
	user_info
FROM 
	table_name a
WHERE
	a.username = (SELECT username FROM a.userId = userId-1) 
	AND 
	a.username = (SELECT username FROM a.userId = userId-2)
  • 数据库

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

    342 引用 • 708 回帖
  • MySQL

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

    691 引用 • 535 回帖
1 操作
Hefery 在 2022-02-14 16:52:15 更新了该帖
深圳 位置
1 赞同
1 操作
225 30 16 17 8 5 146

相关帖子

回帖
业务场景 SQL 练习

欢迎来到这里！

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

注册 关于
请输入回帖内容 ...
Hefery
深圳
回帖
0
 帖子
18
 积分
0