面试常见5大SQL题目：培养SQL技能，助你完胜任何面试！

Jun 23, 2021 by Zhang in Blog

虽然SQL并不是数据类工作中最吸引人的部分，但对它有深刻理解并能熟练使用还是很有必要的，因为它是所有数据岗位成功的必经之路。

实际上，除了SELECT FROM、 WHERE、 GROUP BY、ORDER BY之外，SQL还有很多其他功能。也就是说，你知道的函数越多，就越容易操作和查询你想要的任何东西。

在这篇文章中，我们主要来学习和探讨以下两点：

1.讲解除基础原理以外的 SQL函数相关知识
2.学习几个SQL面试的练习题

问题1：查询第二高的薪水

编写一个SQL查询，从Employee表中找到第二高的薪水。比如，对于下面的Employee表，查询应该返回200作为第二高薪水。但是如果表里没有第二高薪水，那么返回结果应为null。

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

解决方案 A：使用 IFNULL, OFFSET

IFNULL(expression, alt) : ifnull() 的结果为空就返回你指定的值；若不是，就返回预期的值。所以如果没有第二高薪水，它就会返回结果为null。
OFFSET : offset 与 ORDER BY 一起用，可以跳过你之前指定的前n行。这个功能很有用，因为你想要的结果就是第二行(第二高的薪水)。

SELECT
    IFNULL(
        (SELECT DISTINCT Salary
        FROM Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1
        ), null) as SecondHighestSalary
FROM Employee
LIMIT 1

解决方案 B：使用 MAX()

下面的MAX查询找到的是除开最高薪水以外的最高薪水，所以它其实筛选的就是第二高薪水！

SELECT MAX(salary) AS SecondHighestSalary
FROM Employee
WHERE salary != (SELECT MAX(salary) FROM Employee)

问题2：查找重复的电子邮件地址

编写一个SQL查询，来查找 Person 表中的所有重复的电子邮件。

+----+---------+
| Id | Email   |
+----+---------+
| 1  | [email protected] |
| 2  | [email protected] |
| 3  | [email protected] |
+----+---------+

解决方案 A：子查询中使用 COUNT()

首先，创建一个子查询，显示每个电子邮件地址出现的频率。然后过滤掉计数大于1的情况。

SELECT Email
FROM (
    SELECT Email, count(Email) AS count
    FROM Person
    GROUP BY Email) as email_count
WHERE count > 1

解决方案 B：HAVING子句

HAVING本质上可以让你将WHERE和GROUP BY结合起来使用。

SELECT Email
FROM Person
GROUP BY Email
HAVING count(Email) > 1

问题3：查找气温升高的组

给定一个天气表格，编写一个 SQL 查询来查找与之前（昨天）相比温度更高的所有日期ID。

+---------+------------------+------------------+
| Id(INT) | RecordDate(DATE) | Temperature(INT) |
+---------+------------------+------------------+
|     1 |     2015-01-01 |          10 |
|     2 |     2015-01-02 |          25 |
|     3 |     2015-01-03 |           20 |
|     4 |     2015-01-04 |          30 |
+---------+------------------+------------------+

解决方案：DATEDIFF()

DATEDIFF 会计算两个日期之间的气温差异，让我们比较今天和昨天的气温变化。

简单地说，这个查询就是选出所有温度大于昨天温度的日期ID。

SELECT DISTINCT a.Id
FROM Weather a, Weather b
WHERE a.Temperature > b.Temperature
AND DATEDIFF(a.Recorddate, b.Recorddate) = 1

问题4：部门里的最高工资

Employee表格中包含了一个公司的所有员工。而且每个员工都有自己的个人Id、薪资情况和一个部门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查询，查找每个部门中工资最高的员工。你的结果应返回以下内容(行的顺序无关紧要)：

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

解决方案：IN子句

IN可以让你在WHERE中使用多个OR子句。比如：WHERE country = ‘ Canada ‘or country = ‘ USA ‘，也可以用WHERE country IN (‘ Canada ‘，’ USA ‘)。
本例中，我们要过滤Department表，只显示每个部门（部门ID）的最高工资。然后，通过department Id 、Salary和已过滤的Department表格join到一起。

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

问题5：交换座位

Mary是一名中学老师，她有一个专门的表格，里面有学生的名字和他们对应的座位ID。ID都是连续递增的。最近Mary想给相邻的学生换座。

编写一个SQL来为Mary输出结果

+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Abbot   |
|    2    | Doris   |
|    3    | Emerson |
|    4    | Green   |
|    5    | Jeames  |
+---------+---------+

得出以下输出结果：

+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Doris   |
|    2    | Abbot   |
|    3    | Green   |
|    4    | Emerson |
|    5    | Jeames  |
+---------+---------+

注意：如果人数是奇数，就不需要交换最后一个人的座位。

解决方案：CASE WHEN语句

可以把“CASE WHEN ，THEN”语句理解成编程中的IF语句。
第一个WHEN语句是用来检查行数是否为奇数的，如果是，就不改动这个ID号。
第二个 WHEN 语句给每个现有ID加1（也就是，1、3、5 变成了2、4、6）
同理，第三个WHEN语句在每个ID基数上减1(也就是，2、4、6变成1、3、5)

SELECT
     CASE
         WHEN((SELECT MAX(id) FROM seat)%2 = 1) AND id = (SELECT MAX(id) FROM seat) THEN id
        WHEN id%2 = 1 THEN id + 1
        ELSE id - 1
    END AS id, student
FROM seat
ORDER BY id

以上就是全部内容这些！如有任何不清楚的地方，请在下方评论，我会竭尽所能解释清楚，感谢你的阅读！

原文作者：Terence Shin
翻译作者：Lea
美工编辑：过儿
校对审稿：Jiawei Tong
原文链接：https://medium.com/data-science-rush/data-science-books-you-should-start-reading-in-2021-73b40a76aad9

View profile