LeetCode上的8个SQL问题，测测你会几个
LeetCode上的8个SQL问题
1.
换座位
小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。
其中纵列的 id 是连续递增的。
小美想改变相邻俩学生的座位。
你能不能帮她写一个 SQL query 来输出小美想要的结果呢？
示例：
+---------+---------+ | id | student | +---------+---------+ | 1 | Abbot | | 2 | Doris | | 3 | Emerson | | 4 | Green | | 5 | Jeames | +---------+---------+假如数据输入的是上表，则输出结果如下：
+---------+---------+ | id | student | +---------+---------+ | 1 | Doris | | 2 | Abbot | | 3 | Green | | 4 | Emerson | | 5 | Jeames | +---------+---------+注意：
如果学生人数是奇数，则不需要改变最后一个同学的座位。
MySQL代码：
1set @n=(select count(id) from seat);
2select @m:=if(id=@n&&mod(@n,2)=1,id,if(mod(id,2)=0,id-1,id+1)) as id,student
3from seat, (select @m:=-1) b ORDER BY id ASC
2.
第N高的薪水
编写一个 SQL 查询，获取 Employee 表中第 n 高的薪水（Salary）。
例如上述 Employee 表，n = 2 时，应返回第二高的薪水 200。如果不存在第 n 高的薪水，那么查询应返回 null。
MySQL代码：
1CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
2BEGIN
3 DECLARE m int;
4 set @i=0;
5 SELECT b.salary into m from (SELECT a.salary,@i:=@i+1 as rown
6 from (SELECT DISTINCT(salary) from employee ORDER BY salary DESC) a) b
7 WHERE b.rown=N;
8 RETURN m;
9END;
10
11select getNthHighestSalary(2);
3.
分数排名
编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。
+----+-------+ | Id | Score | +----+-------+ | 1 | 3.50 | | 2 | 3.65 | | 3 | 4.00 | | 4 | 3.85 | | 5 | 4.00 | | 6 | 3.65 | +----+-------+例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：
MySQL代码：
1SELECT m.Score, (SELECT count(t.Score)+1 from
2(SELECT s.Score,count(s.Score) from scores s GROUP BY Score ORDER BY Score DESC) t
3WHERE t.Score>m.Score) Rank
4FROM scores m
5ORDER BY m.Score DESC 4.
连续出现的数字
编写一个 SQL 查询，查找所有至少连续出现三次的数字。
+----+-----+ | Id | Num | +----+-----+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 1 | | 6 | 2 | | 7 | 2 | +----+-----+例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。
MySQL代码：
1SELECT cast(DISTINCT(t.Num) as ) ConsecutiveNums
2FROM (SELECT os.Num, @Rank:=IF(@pre=os.Num,@Rank+1,1) Rank,@pre:=os.Num
3from (SELECT Num FROM `logs` ORDER BY Num DESC) as os,(SELECT @Rank:=0,@pre:=-1) as init) as t WHERE t.Rank>3
5.
部门工资最高的员工
Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。
Department 表包含公司所有部门的信息。
编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。
+------------+----------+--------+ | Department | Employee | Salary | +------------+----------+--------+ | IT | Max | 90000 | | Sales | Henry | 80000 | +------------+----------+--------+MySQL代码：
1SELECT B.Name Department,A.Name Employee,A.Salary FROM (
2 SELECT Name,Salary,DepartmentId FROM (
3 SELECT * from Employee order by DepartmentId,Salary DESC
4 ) C GROUP BY C.DepartmentId
5 ) A
6 JOIN department B
7 ON A.DepartmentId=B.Id 6.
行程和用户
Trips 表中存所有出租车的行程信息。每段行程有唯一健 Id，Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外键。Status 是枚举类型，枚举成员为 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)。
Users 表存所有用户。每个用户有唯一键 Users_Id。Banned 表示这个用户是否被禁止，Role 则是一个表示（‘client’, ‘driver’, ‘partner’）的枚举类型。
写一段 SQL 语句查出 2013年10月1日 至 2013年10月3日 期间非禁止用户的取消率。基于上表，你的 SQL 语句应返回如下结果，取消率（Cancellation Rate）保留两位小数。
+------------+-------------------+ | Day | Cancellation Rate | +------------+-------------------+ | 2013-10-01 | 0.33 | | 2013-10-02 | 0.00 | | 2013-10-03 | 0.50 | +------------+-------------------+MySQL代码：
1SELECT A.Request_at Day,FORMAT(SUM(A.StatusID)/count(A.Status),2) 'Cancellation Rate'
2from (select Id,Client_Id,Driver_Id,Status,Request_at,IF(Status='completed',0,1) StatusID
3from Trips
4where (Client_Id IN (select Users_Id from Users where Banned="No" AND Role='client')) AND (Driver_Id IN (select Users_Id from Users where Banned="No" AND Role='driver'))) A
5GROUP BY A.Request_at 7.
体育馆的人流量
X 市建了一个新的体育馆，每日人流量信息被记录在这三列信息中：序号 (id)、日期 (date)、 人流量 (people)。
请编写一个查询语句，找出高峰期时段，要求连续三天及以上，并且每天人流量均不少于100。
例如，表 stadium：
对于上面的示例数据，输出为：
+------+------------+-----------+ | id | date | people | +------+------------+-----------+ | 5 | 2017-01-05 | 145 | | 6 | 2017-01-06 | 1455 | | 7 | 2017-01-07 | 199 | | 8 | 2017-01-08 | 188 | +------+------------+-----------+Note:
每天只有一行记录，日期随着 id 的增加而增加。
MySQL代码：
1SELECT id,date,people from stadium,(
2 SELECT if(COUNT(id)>=3,GROUP_CONCAT(id),null) e FROM (
3 select id,date,people,(@b:=@b+1) as b, id-@b as c
4 from stadium a,(SELECT @b := 0) tmp_b
5 where people>=100
6 ) t GROUP BY t.c
7) f WHERE f.e is not null and FIND_IN_SET(id , f.e) 8.
部门工资前三高的员工
Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id 。
Department 表包含公司所有部门的信息。
编写一个 SQL 查询，找出每个部门工资前三高的员工。例如，根据上述给定的表格，查询结果应返回：
+------------+----------+--------+ | Department | Employee | Salary | +------------+----------+--------+ | IT | Max | 90000 | | IT | Randy | 85000 | | IT | Joe | 70000 | | Sales | Henry | 80000 | | Sales | Sam | 60000 | +------------+----------+--------+MySQL代码：
1SELECT D1.Name Department, E1.Name Employee, E1.Salary
2FROM Employee E1, Employee E2, Department D1
3WHERE E1.DepartmentID = E2.DepartmentID
4AND E2.Salary >= E1.Salary
5AND E1.DepartmentID = D1.ID
6GROUP BY E1.Name
7HAVING COUNT(DISTINCT E2.Salary) <= 3
8ORDER BY D1.Name, E1.Salary DESC;
看完觉得有用就点个赞呗！