在之前做算法题的途中发现，LeetCode 上面推出了数据库解答，有十道题，于是这两天晚上时间就给做了。解答是次要主要的好处是，正好复习复习 SQL 一些查询语句的写法，比如自定义变量和常用函数。题目都比较简单，少做解释，以贴题目和答案为主。
| 175 | Combine Two Tables | 32.5% | Easy | |
| 176 | Second Highest Salary | 23.8% | Easy | |
| 177 | Nth Highest Salary | 14.1% | Medium | |
| 178 | Rank Scores | 20.7% | Medium | |
| 180 | Consecutive Numbers | 20.2% | Medium | |
| 181 | Employees Earning More Than Their Managers | 44.2% | Easy | |
| 182 | Duplicate Emails | 38.0% | Easy | |
| 183 | Customers Who Never Order | 34.2% | Easy | |
| 184 | Department Highest Salary | 19.2% | Medium | |
| 185 | Department Top Three Salaries | 16.3% | Hard |
Combine Two Tables
【题目】
Table: Person
+-------------+---------+ | Column Name | Type | +-------------+---------+ | PersonId | int | | FirstName | varchar | | LastName | varchar | +-------------+---------+ PersonId is the primary key column for this table.
Table: Address
+-------------+---------+ | Column Name | Type | +-------------+---------+ | AddressId | int | | PersonId | int | | City | varchar | | State | varchar | +-------------+---------+ AddressId is the primary key column for this table.
Write a SQL query for a report that provides the following information for each person in the Person table, regardless if there is an address for each of those people:
FirstName, LastName, City, State
【解答】
1 | select p.FirstName, p.LastName, a.City, a.State from Person p left outer join Address a on p.PersonId=a.PersonId; |
Second Highest Salary
【题目】
Write a SQL query to get the second highest salary from the Employee table.
+----+--------+ | Id | Salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+
For example, given the above Employee table, the second highest salary is 200. If there is no second highest salary, then the query should return null.
【解答】题目简单，但是如果和我一样，发现几个常用函数都不记得了，可以复习一下。
1 | select IFNULL( (select e.Salary from Employee e group by e.Salary order by e.Salary desc limit 1, 1), NULL) SecondHighestSalary; |
Nth Highest Salary
【题目】
Write a SQL query to get the nth highest salary from the Employee table.
+----+--------+ | Id | Salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+
For example, given the above Employee table, the nth highest salary where n = 2 is 200. If there is no nth highest salary, then the query should return null.
【解答】第 n 高，这就得用自定义变量了，平时很少用这东西，于是复习了一下先。
1 2 3 4 5 6 7 8 9 10 11 12 | CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INTBEGIN RETURN ( select IFNULL(Salary, NULL) Salary from ( select @row_num := @row_num+1 Rank, Salary from ( select Salary from Employee group by Salary desc ) t1 join ( select @row_num := 0 from dual ) t2 ) t where t.Rank=N );END |
Rank Scores
【题目】
Write a SQL query to rank scores. If there is a tie between two scores, both should have the same ranking. Note that after a tie, the next ranking number should be the next consecutive integer value. In other words, there should be no “holes” between ranks.
+----+-------+ | Id | Score | +----+-------+ | 1 | 3.50 | | 2 | 3.65 | | 3 | 4.00 | | 4 | 3.85 | | 5 | 4.00 | | 6 | 3.65 | +----+-------+
For example, given the above Scores table, your query should generate the following report (order by highest score):
+-------+------+ | Score | Rank | +-------+------+ | 4.00 | 1 | | 4.00 | 1 | | 3.85 | 2 | | 3.65 | 3 | | 3.65 | 3 | | 3.50 | 4 | +-------+------+
【解答】又是一道自定义变量的题目，这种模式应当熟悉，还是挺常见的。如果不能使用 “set @var_name=0;” 的话（要求用一句 SQL 搞定），那可以在子句里面定义 “select @var_name:=0”，再在它的外面使用这个变量。
1 2 3 4 5 6 7 | select s.Score, t.Rank from ( select @row_num:=@row_num+1 Rank, Score from ( select Score from Scores group by Score desc ) t1 join ( select @row_num := 0 from dual ) t2) t, Scores s where s.Score=t.Score group by Score desc, Rank asc, Id; |
Consecutive Numbers
【题目】
Write a SQL query to find all numbers that appear at least three times consecutively.
+----+-----+ | Id | Num | +----+-----+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 1 | | 6 | 2 | | 7 | 2 | +----+-----+
For example, given the above Logs table, 1 is the only number that appears consecutively for at least three times.
【解答】
1 | select DISTINCT(l1.Num) from Logs l1, Logs l2, Logs l3 where l1.Id+1=l2.Id and l1.Id+2=l3.Id and l1.Num=l2.Num and l1.Num=l3.Num; |
Employees Earning More Than Their Managers
【题目】
The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id.
+----+-------+--------+-----------+ | Id | Name | Salary | ManagerId | +----+-------+--------+-----------+ | 1 | Joe | 70000 | 3 | | 2 | Henry | 80000 | 4 | | 3 | Sam | 60000 | NULL | | 4 | Max | 90000 | NULL | +----+-------+--------+-----------+
Given the Employee table, write a SQL query that finds out employees who earn more than their managers. For the above table, Joe is the only employee who earns more than his manager.
+----------+ | Employee | +----------+ | Joe | +----------+
【解答】
1 | select e.Name from Employee e, Employee m where e.ManagerId=m.Id and e.Salary>m.Salary; |
Duplicate Emails
【题目】
Write a SQL query to find all duplicate emails in a table named Person.
+----+---------+ | Id | Email | +----+---------+ | 1 | a@b.com | | 2 | c@d.com | | 3 | a@b.com | +----+---------+
For example, your query should return the following for the above table:
+---------+ | Email | +---------+ | a@b.com | +---------+
Note: All emails are in lowercase.
【解答】
1 | select distinct(p.Email) from Person p, Person q where p.Id!=q.Id and p.Email=q.Email; |
Customers Who Never Order
【题目】
Suppose that a website contains two tables, the Customers table and the Orders table. Write a SQL query to find all customers who never order anything.
Table: Customers.
+----+-------+ | Id | Name | +----+-------+ | 1 | Joe | | 2 | Henry | | 3 | Sam | | 4 | Max | +----+-------+
Table: Orders.
+----+------------+ | Id | CustomerId | +----+------------+ | 1 | 3 | | 2 | 1 | +----+------------+
Using the above tables as example, return the following:
+-----------+ | Customers | +-----------+ | Henry | | Max | +-----------+
【解答】
1 2 3 | select c.Name Customers from Customers c where c.Id not in ( select CustomerId from Orders) |
Department Highest Salary
【题目】
The Employee table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id.
+----+-------+--------+--------------+ | Id | Name | Salary | DepartmentId | +----+-------+--------+--------------+ | 1 | Joe | 70000 | 1 | | 2 | Henry | 80000 | 2 | | 3 | Sam | 60000 | 2 | | 4 | Max | 90000 | 1 | +----+-------+--------+--------------+
The Department table holds all departments of the company.
+----+----------+ | Id | Name | +----+----------+ | 1 | IT | | 2 | Sales | +----+----------+
Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, Max has the highest salary in the IT department and Henry has the highest salary in the Sales department.
+------------+----------+--------+ | Department | Employee | Salary | +------------+----------+--------+ | IT | Max | 90000 | | Sales | Henry | 80000 | +------------+----------+--------+
【解答】
1 2 3 | select d.Name Department, e.Name Employee, s.Salary from ( select MAX(e.Salary) Salary, e.DepartmentId from Employee e, Department d where e.DepartmentId=d.Id group by e.DepartmentId) s, Employee e, Department d where s.Salary=e.Salary and e.DepartmentId=d.Id and e.DepartmentId=s.DepartmentId; |
Department Top Three Salaries
【题目】
The Employee table holds all employees. Every employee has an Id, and there is also a column for the department Id.
+----+-------+--------+--------------+ | Id | Name | Salary | DepartmentId | +----+-------+--------+--------------+ | 1 | Joe | 70000 | 1 | | 2 | Henry | 80000 | 2 | | 3 | Sam | 60000 | 2 | | 4 | Max | 90000 | 1 | | 5 | Janet | 69000 | 1 | | 6 | Randy | 85000 | 1 | +----+-------+--------+--------------+
The Department table holds all departments of the company.
+----+----------+ | Id | Name | +----+----------+ | 1 | IT | | 2 | Sales | +----+----------+
Write a SQL query to find employees who earn the top three salaries in each of the department. For the above tables, your SQL query should return the following rows.
+------------+----------+--------+ | Department | Employee | Salary | +------------+----------+--------+ | IT | Max | 90000 | | IT | Randy | 85000 | | IT | Joe | 70000 | | Sales | Henry | 80000 | | Sales | Sam | 60000 | +------------+----------+--------+
【解答】这道题拿 MySql 做其实是有点难了，如果我用 Oracle，我就可以 rank() over(partition by xxx order by xxx desc) 这样搞了，但是 MySql 比较挫的地方在于没有这样分区操作的东西。不过最后还是借助了三个自定义变量搞定。意思就不解释了，还是好懂的：
1 2 3 4 5 6 7 8 9 10 11 12 | select Department, Employee, Salary from ( select IF(@lastDep!=t1.Department, @count:=0, @count:=@count), IF(@lastDep=t1.Department and @lastSalary!=t1.Salary, @count:=@count+1, @count:=@count) Cnt, @lastDep:=t1.Department, @lastSalary:=t1.Salary, t1.Department, t1.Employee, t1.Salary from ( select d.Name Department, e.Name Employee, e.Salary from Department d, Employee e where d.Id=e.DepartmentId order by Department asc, Salary desc ) t1, ( select @lastDep:=null, @lastSalary:=0, @count:=0 from dual ) t2) f where Cnt<3; |
事后，我去看了看讨论区，发现一个漂亮的解答，没有用任何自定义变量，关键就是 distinct(Salary) 去和原 Salary 比较，过滤掉这个条件下出现次数大于 3 的情况：
1 2 3 4 5 6 7 | select D.Name as Department, E.Name as Employee, E.Salary as Salary from Employee E, Department D where (select count(distinct(Salary)) from Employee where DepartmentId = E.DepartmentId and Salary > E.Salary) in (0, 1, 2) and E.DepartmentId = D.Id order by E.DepartmentId, E.Salary DESC; |
文章未经特殊标明皆为本人原创，未经许可不得用于任何商业用途，转载请保持完整性并注明来源链接 《四火的唠叨》
做完感觉 leetcode 上所有 mysql 的关于 rank 的题目（nth highest overall, nth highest with each department, etc）都是用 join + id 的巧妙应用解决的… 以前多用 oracle sql, join+看 id 只当作 rank 题目里面的一题多解来看。。。没想到 mysql 功能这么限制，大部分都得用这种方法也是醉了
我觉得你很多答案有点滥用 Oracle 语法了。你从讨论区见到的 Nth Highest Salary 的解法应该才是比较通用的。
比如 Nth highest salary, 可以这样
select *
from employee e1
where (select count(distinct e2.salary)
from employee e2
where e2.salary > e1.salary) = (N-1)