在任何以数据为中心的工作中,对SQL有深刻的理解都是成功的关键,尽管这不是工作中最有趣的部分。事实上,除了SELECT FROM WHERE GROUP BY ORDER BY之外,还有更多的SQL方法。你知道的功能越多,操作和查询所需的内容就越容易。
本文中将与大家学习和交流以下两件事:
1)学习和教一些基本功能以外的SQL函数
2)探讨一些SQL面试练习问题
* 本文中的问题仅来自Leetcode
问题1:第二高的薪水
编写一个SQL查询用于从Employee表中获取第二高的薪水。例如,给定下面的Employee表,查询应返回200作为第二高的薪水。如果没有第二高的薪水,则查询应返回null。
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
1)解决方案A:使用IFNULL,OFFSET
SELECT
IFNULL(
(SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1
), null) as SecondHighestSalary
FROM Employee
LIMIT 1
2)解决方案B:使用MAX()
此查询表示选择的MAX薪水不等于最高薪水,这等于选择第二高的薪水。
SELECT MAX(salary) AS SecondHighestSalary
FROM Employee
WHERE salary != (SELECT MAX(salary) FROM Employee)
问题2:重复的电子邮件
编写SQL查询以在名为Person的表中查找所有重复的电子邮件。
+----+---------+
| Id | Email |
+----+---------+
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
+----+---------+
1)解决方案A:子查询中的COUNT()
首先,创建一个子查询来显示每封电子邮件的频率次数。然后子查询在计数大于1的地方被过滤。
SELECT Email
FROM (
SELECT Email, count(Email) AS count
FROM Person
GROUP BY Email
) as email_count
WHERE count > 1
2)解决方案B:HAVING子句
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()
简单来说,查询是选择给定日期的温度高于昨天的温度的ID。
SELECT DISTINCT a.Id
FROM Weather a, Weather b
WHERE a.Temperature > b.Temperature
AND DATEDIFF(a.Recorddate, b.Recorddate) = 1
问题4:部门最高薪资
下面的雇员表中包含所有雇员。每个员工都有一个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 |
+----+-------+--------+--------------+
下面的部门表包含公司的所有部门。
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
编写SQL查询来查找每个部门中薪水最高的员工。对于上述两个表,你的SQL查询应返回以下行(行的顺序无关紧要)。
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Jim |90000 |
| Sales | Henry | 80000 |
+------------+----------+--------+
解决方案:IN子句
SELECT
Department.name AS 'Department',
Employee.name AS 'Employee',
Salary
FROM Employee
INNER JOIN Department ON Employee.DepartmentId = Department.Id
WHERE (DepartmentId , Salary)
IN
( SELECT
DepartmentId, MAX(Salary)
FROM
Employee
GROUP BY DepartmentId
)
问题5:互换座位
玛丽是一所中学的老师,她有一张座位表,上面存储着学生的姓名和相应的座位ID。列ID是连续的增量,玛丽想为相邻的学生互换座位。
你可以编写SQL查询来输出玛丽的结果吗?
+---------+---------+
| 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
SELECT
CASE
WHEN((SELECT MAX(id) FROM seat)%2 = 1) AND id = (SELECT MAX(id) FROM seat) THENid
WHEN id%2 = 1 THEN id + 1
ELSE id - 1
END AS id, student
FROM seat
ORDER BY id
以上就是所有的解决方法。
(作者 | Alexei Ledenev,翻译 | 天道酬勤,责编 | Carol)