1 取第N高的值

思路：取出降序的前N个，再取最小的。

select min(e.Salary) 
from (select distinct Salary from Employee order by Salary desc limit N) e

对于N超过distinct Salary数量的,用IF判断。

select (IF(
(select count(distinct Salary) from Employee)<N, #判断条件
NULL,  #是的话就返回NULL
(select min(e.Salary) from (select distinct Salary from Employee order by 
Salary desc limit N) e)  #不是的话就按思路取数
))

2 连续出现的数字

自己的第一次解，有bug。

select Num as ConsecutiveNums from(
select count(distinct n.Id) as cn,n.Num as Num
from(
select l3.Id, l3.Num
from(select l1.Id as Id1, l1.Num as Num1, l2.Id as Id2, l2.Num as Num2
from Logs l1, Logs l2
where l1.Id=l2.Id-1 and l1.Num=l2.Num)
 e, Logs l3
where (l3.Id=e.Id1 and l3.Num = e.Num1) or (l3.Id=e.Id2 and l3.Num = e.Num2)) n
group by n.Num)m
where m.cn >= 3

然后我用了暴力的解法。这应该不是最优、普适的。

select distinct l1.num as ConsecutiveNums
from Logs l1, Logs l2, Logs l3
where (l1.id=l2.id-1 and l1.num=l2.num) and (l1.id=l3.id+1 and l1.num=l3.num)

3 分数排名

关键在于计算比某个值大的数量

SELECT Score, (SELECT count(DISTINCT score) FROM Scores WHERE score >= s.score) AS Rank 
FROM Scores s 
ORDER BY Score DESC ;

4 删除重复电子邮箱

mysql的多表联合删除。

DELETE p1
FROM Person p1,Person p2
WHERE p1.Email = p2.Email AND p1.Id > p2.Id

另一种方法。

DELETE FROM Person
WHERE id not in (
    select id
    from (
        select min(id) as id,Email
        from Person
        group by Email
    ) n1
)

5 上升的温度

主要用到的是datediff这个函数。
mysql日期相关：https://www.jb51.net/article/138562.htm

select w2.id
from Weather w1 join Weather w2 on w1.Temperature<w2.Temperature 
and datediff(w2.RecordDate,w1.RecordDate)=1

6 体育馆的人流量

回想起了@直接在select里的用法，可参考这个https://zhidao.baidu.com/question/465055963.html
关键在
(select id,date,people from stadium) a, (select @n2:=0) b
select id,people, IF(people>=100,@n2,@n2:=@n2+1) as ncount

select id,date,people
from(
select id,people,date, IF(people>=100,@n,@n:=@n+1) as ncount
from  (select id,date,people from stadium) a, (select @n:=0) b) c
where ncount in (
select ncount
from(
select id,people, IF(people>=100,@n2,@n2:=@n2+1) as ncount
from
(select id,date,people from stadium) a1, (select @n2:=0) b1
) c1
group by (ncount)
having count(ncount)>=4) and people>=100

7 换座位

位运算辨别奇偶。

select if(id&1 and id = (select count(id) from seat),id, if(id&1,id+1,id-1)) as id, student 
from seat 
order by id asc;

这个快一些，用union。

SELECT * FROM(
    SELECT id-1 AS id,student FROM seat WHERE id%2=0
    UNION
    SELECT id+1 AS id,student FROM seat WHERE id%2=1 AND (id+1) <= (SELECT COUNT(*) FROM seat)
    UNION
    SELECT id AS id,student FROM seat WHERE id%2=1 AND (id+1) > (SELECT COUNT(*) FROM seat)
) AS T1
ORDER BY id ASC

这个更快，用case when。

select
    case when id%2=0 then id-1 #取余判断奇偶
    when id<(select max(id) from seat) then id+1 #注意聚合函数需要写成子查询
    else id
    end as id
    ,student
from seat
order by 
    case when id%2=0 then id-1
    when id<(select max(id) from seat) then id+1 #注意聚合函数需要写成子查询
    else id
    end #注意order by由于执行顺序在select前，所以不能order by id

8 行程和用户

带条件的count，带条件的sum。
https://www.cnblogs.com/1234cjq/p/7802768.html

select Request_at as Day,
round(count(IF(Status<>"completed",Status,null))/count(status),2) as 'Cancellation Rate'
from trips join Users on Client_id=Users_id
where banned<>'yes' and request_at between '2013-10-01' and '2013-10-03'
group by request_at

select t.Request_at as "Day",
round(count(case when t.Status != 'completed' then 1 else null end)/count(1),2) as "Cancellation Rate" 
from Trips t join Users u on t.Client_Id = u.Users_Id 
where u.Banned = 'No' and t.Request_at between '2013-10-01'and'2013-10-03' 
group by t.Request_at 
order by t.Request_at