leetcode刷mysql遇到的一些小tips

196

题目是删除重复邮箱，抄的解答

#因为mysql不允许同时对一个表delete和select联合操作select, 所以会select一个虚拟的表出来
delete
from Person where Id 
not in (select need.id from 
    (select min(Id) as id from Person group by Email) as need
);

以及作者说exist似乎可以优化一些, 有待探究

197

datediff和timestampdiff是后减前

select TIMESTAMPDIFF(DAY,'2019-05-20', '2019-05-21'); # 1

262

本题不熟悉的地方在于不知道 select后面的东西可以函数套函数

ELECT x.Request_at Day,ROUND(SUM(IF(Status!='completed',1,0))/COUNT(*),2) 'Cancellation Rate'
FROM (
    SELECT t.*
    FROM Trips  t, Users  u1, Users u2
    WHERE t.Client_Id=u1.Users_Id AND t.Driver_Id=u2.Users_Id
    AND u1.Banned='No' AND u2.Banned='No'
    AND Request_at BETWEEN '2013-10-01' AND '2013-10-03'
) x
GROUP BY x.Request_at
ORDER BY x.Request_at;

601

体育馆连续三天人流量超过100的date 不知道哪里有问题

# Write your MySQL query statement below
select a.* from stadium as a
inner join stadium as b
on a.id = b.id -1
inner join stadium as c
on b.id = c.id -1
where a.people >100
and b.people >100
and c.people >100
union
select a.* from stadium as a
inner join stadium as b
on a.id = b.id +1
inner join stadium as c
on b.id = c.id +1
where a.people >100
and b.people >100
and c.people >100;

评论里大神@Derrick 的思路：
我的猜测是在头查一次, 在尾查一次, 中间查一次
另一位大神@皮革马利翁 的对于去重操作解释:

比如, id为: 1, 2, 3, 4的满足, 我们讨论id 为2的这一天, id为2 满足是因为, 1, 2, 3; 也可以是因为2, 3, 4

每种情况都记录一次，那是不是这两种情况就记录了两次，重复了。

select distinct a.* from stadium a,stadium b,stadium 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 ##a<b<c
     (a.id = b.id-1 and a.id = c.id +1) or ##c<a<b
     (a.id = b.id+1 and b.id = c.id +1)    ##c<b<a
) order by a.id