中高级题目不多，一起写了。
中级篇：
连续出现的数字：
先贴通过测试的SQL：
SELECT DISTINCT a.num AS ConsecutiveNums
FROM logs a, logs b, logs c
WHERE a.id = b.id - 1
AND a.id = c.id - 2
AND a.num = b.num
AND a.num = c.num
虽然通过了，但是如果遇到ID不连续的情况就会……
一开始我是这么写的：
select DISTINCT(a.num) as ConsecutiveNums from logs a,logs b,logs c where a.id=b.id-1=c.id-2 and a.num=b.num =c.num
但是这种写法在本地测试的过程中出现了很奇怪的问题，我查询a.id,b.id,c.id，出现了这个结果：
经过一般测试和猜想，我怀疑mysql是使用或来解释a.id=b.id-1=c.id-2这样的条件的，所以我们需要a.num=b.num && a.num=c.num这样写。
然后贴个别人的sql：
SELECT DISTINCT Num AS ConsecutiveNums
FROM (
SELECT Num, @C := CASE
WHEN @N = (@N := Num) THEN @C + 1
ELSE 1
END AS C
FROM (
SELECT @C := 0, @N := -1
) _init, Logs
) X
WHERE X.C >= 3
自己想了很久，写出一个SQL：
SELECT b.name AS Department, a.name AS Employee, salary
FROM (
SELECT *
FROM Employee
ORDER BY Salary DESC
) a
LEFT JOIN Department b ON a.DepartmentId = b.id
GROUP BY DepartmentId
ORDER BY salary
然而，我不知道为什么，同样的数据，在网站上和本地的查询结果不一样，明明SQL架构都是一样的。但是继而也想到一个问题，如果部门里有2个或以上工资并列最高的人，这个sql只能取出其中一个，于是继续改造，最终通过的SQL如下：
SELECT b.name AS Department, a.name AS Employee, salary
FROM Employee a
JOIN Department b
ON a.DepartmentId = b.id
AND Salary = (
SELECT MAX(Salary)
FROM Employee
WHERE DepartmentId = b.Id
)
换座位：
SQL:
SELECT id, IFNULL(CASE
WHEN mod(id, 2) = 1 THEN (
SELECT student
FROM seat
WHERE id = id + 1
)
WHEN mod(id, 2) = 0 THEN (
SELECT student
FROM seat
WHERE id = id - 1
)
END, student) AS student
FROM seat
这里要注意一点，case column when condition then…，这里的condition不能写表达式，只能写常量或是结果，第一次我没注意，结果一直出错了。
贴几个我觉得还可以的SQL：
通过修改ID来实现：
SELECT
IF(id < (SELECT MAX(id) From seat),IF(id%2=0,id-1,id+1),IF(id%2=0,id-1,id)) AS id, #不要漏了这里的逗号。给行取别名为id
student
FROM seat #不要AS t，这会给seat取别名为t
ORDER BY id ASC;
select (case
when mod(id,2)!=0 and id!=counts then id+1
when mod(id,2)!=0 and id=counts then id
else id-1
end)as id,student
from seat,(select count(*)as counts
from seat)as seat_counts
order by id asc
第N高的薪水：
老实说，这个有点想不着思路，摸不着头脑，因为limit后面不可以跟表达式，最终还是看了其他人的SQL才得以通过，但我想这个在程序应该简单些，毕竟程序可以对N做相关的运算。
下面语句的逻辑是，假如我们要找第二高的薪水，那么我们允许其中一个最大值存在，然后在其余的数字中找出最大的，即为整个的第二大的值；
一开始我不是很理解，后来我想唯一解释就是 e1 这张表的数据并不是固定不变的，而是会随着条件变化，我们可以通过select *测试而来，当n的值改变，结果也会改变。感觉还是有点抽象的
SQL:
SELECT MAX(Salary) FROM Employee E1
WHERE n-1 =
(SELECT COUNT(DISTINCT(E2.Salary)) FROM Employee E2
WHERE E2.Salary > E1.Salary);
看到到简单的方法…怪我对mysql的function不熟悉吧，毕竟用的真的太少了
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
declare M int;
SET M = N-1;
RETURN (
# Write your MySQL query statement below.
SELECT distinct salary FROM Employee ORDER BY salary desc limit M,1
);
END
分数排名：
SQL:
SELECT Score
, CASE
WHEN @preScore = score THEN CAST(@rank AS signed)
WHEN @preScore := score THEN CAST(@rank := @rank + 1 AS signed)
ELSE CAST(@rank := @rank + 1 AS signed)
END AS Rank
FROM scores s, (
SELECT @rank := 0, @preScore := NULL
) p
ORDER BY score DESC
CAST函数是拿来做类型转换的。。。
他人的SQL：
select b.Score,(select count(distinct a.Score)from Scores as a where a.Score>=b.Score) as Rank from Scores as b order by Rank
困难篇
行程和用户：
感觉就是复杂，并不难。
SQL：
SELECT Request_at AS Day
, ROUND(SUM(if(a.status = 'cancelled_by_driver'
OR a.status = 'cancelled_by_client', 1, 0)) / COUNT(a.status), 2) AS "`Cancellation Rate`"
FROM Trips a
JOIN users b
ON a.Client_Id = b.users_id
AND b.banned = 'No'
AND a.request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY a.request_at
体育馆的流量：
select distinct(a.id),a.date,a.people
from stadium as a,stadium as b, stadium as c
where a.people>=100 and b.people>=100 and c.people>=100
and ((a.id-b.id=1 and b.id-c.id=1) or(b.id-a.id=1 and a.id-c.id=1) or(c.id-b.id=1 and b.id-a.id=1))
order by a.id
部门工资前三高的员工：
SQL：
SELECT b.Name AS Department, a.name AS Employee, Salary
FROM Employee a
JOIN Department b ON a.DepartmentId = b.id
WHERE (
SELECT COUNT(DISTINCT Salary)
FROM Employee a2
WHERE a2.DepartmentId = a.DepartmentId
AND Salary > a.Salary
) < 3
ORDER BY b.Name, Salary DESC