在LeetCode上有一些有趣的MySQL查询问题,这里记录下来,方便以后查阅。
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.
1 | +----+-------+--------+--------------+ |
The Department table holds all departments of the company.
1 | +----+----------+ |
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.
1 | +------------+----------+--------+ |
答案
先说答案:
1 | select dep.Name as "Department", emp.Name as "Employee", emp.Salary as "Salary" |
首先Department和Employee联接是不可少的,因为结果包含两张表的字段;再用子查询(exists),保留同部门内高于本身薪资的不超过三个的员工(having),并列排名只占一位,需要去重(distinct)。
注意:以上'x'
并无特殊意义,只是有值时表示存在,无值时表示不存在。;having
应该配合group by
使用,单独使用having
时视当前数据为一组;count()
函数也是,单独使用having
时也相当于给当前数据分成一组。
另外:只用一个select
(没有子查询)也能实现,就是将Employee和自身join
,on
薪资比自己高的员工;然后用group by
去重,再结合having count(distinct emp1.Salary) < 3
,保留有效结果。这个join
方法效率远低于上面子查询方法,所以不要对子查询有偏见。
Human Traffic of Stadium
连续达标问题:查询连续3天体育馆人数高于100的日期。
原题
X city built a new stadium, each day many people visit it and the stats are saved as these columns: id, date, people
Please write a query to display the records which have 3 or more consecutive rows and the amount of people more than 100(inclusive).
For example, the table stadium:
1 | +------+------------+-----------+ |
For the sample data above, the output is:
1 | +------+------------+-----------+ |
Note:
Each day only have one row record, and the dates are increasing with id increasing.
答案
思路是先找出人数大于100的日期,然后在这些日期附近再找数大于100的日期,保留能够形成连续3天的日期。
提示里说表中每一天都有一条记录,而且日期是随id递增,所以我们只需要计算id,而不是日期。
用子查询的方法:
1 | select s.id, s.date, s.people |
在当前日期的前2天和后2天找,找到人数大于100的日期个数大于2的话,那么当前日期必定可以形成连续3天人数大于100;找到人数大于100的日期个数等于2,且这两个日期与当前日期的距离和不是1或-1,绝对距离和不是4的话,那么当前日期也是可以形成连续3天人数大于100;其他情况都不行。
用join
的方法:
1 | select distinct t1.* |
同样,上面的子查询方法依然比这个join方法效率高。
Trips and Users
原题
用户取消叫车服务问题:统计一个时间段内每天用户取消叫车服务占所有叫车服务的比例。
The Trips table holds all taxi trips. Each trip has a unique Id, while Client_Id and Driver_Id are both foreign keys to the Users_Id at the Users table. Status is an ENUM type of (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’).
1 | +----+-----------+-----------+---------+--------------------+----------+ |
The Users table holds all users. Each user has an unique Users_Id, and Role is an ENUM type of (‘client’, ‘driver’, ‘partner’).
1 | +----------+--------+--------+ |
Write a SQL query to find the cancellation rate of requests made by unbanned clients between Oct 1, 2013 and Oct 3, 2013. For the above tables, your SQL query should return the following rows with the cancellation rate being rounded to two decimal places.
1 | +------------+-------------------+ |
答案
1 | select Request_at as "Day", round(1-sum(t.Status="completed")/count(t.id), 2) as "Cancellation Rate" |
Median Employee Salary
原题
中位薪资问题:查询各家公司中薪资排中位的的员工(薪资)。
The Employee table holds all employees. The employee table has three columns: Employee Id, Company Name, and Salary.
1 | +-----+------------+--------+ |
Write a SQL query to find the median salary of each company. Bonus points if you can solve it without using any built-in SQL functions.
1 | +-----+------------+--------+ |
答案
1 | select emp.Id, emp.Company, emp.Salary |