leetcode mysql(完结)

1.https://www.cnblogs.com/aspwebchh/p/6726181.htmlsql
连接查询中on筛选与where筛选的区别
2.https://blog.csdn.net/zz_moon/article/details/80626759
leetcode sql第四题解法
3.https://blog.csdn.net/weixin_42074565/article/details/80192590
leetcode sql第五题解法
4.sql中distinct的用法：与5中的题目相关：
https://www.cnblogs.com/leonlee/p/6042461.html
重点注意distinct A,B这样的模式，这个的意思是说A,B对是唯一的
比如说(1,2)(1,3)或者(1,2)(2,2)这样的对是不会被过滤出去的，必须是A,B对应完全相同才会被过滤出去如(1,2)(1,2)
5.leetcode sql部门工资前三高的员工题目我的答案

# Write your MySQL query statement below
select name as Department,na1 as Employee,sa1 as Salary
from 
(select e1.id as id1,e1.departmentid as dp1,e1.name as na1,e1.salary as sa1
from employee e1,(select distinct e3.salary,e3.departmentid
                        from employee e3) e2
where e1.departmentid=e2.departmentid and e1.salary<=e2.salary
group by e1.id
having count(e1.id)<=3)as temp,department d1
where temp.dp1=d1.id
order by name,sa1 desc

6.SQL中=null查询不出结果而is null可以查询结果说明
https://www.cnblogs.com/kaiwensievert/p/7047112.html
7.leetcode中行程和用户题的答案

# Write your MySQL query statement below
select request_at as Day,round(K.co3/K.co1,2) as 'Cancellation Rate'
from(select co1,ifnull(co2,0) as co3,A.request_at
from(select request_at,count(id) as co1
from trips,users
where client_id=users_id and banned='No'
group by request_at) A left join
(select request_at,count(1) as co2
from trips,users
where client_id=users_id and banned='No' and (status='cancelled_by_driver' 
                                             or status='cancelled_by_client')
group by request_at) B
on A.request_at=B.request_at) K
where request_at between "2013-10-01" and "2013-10-03"
group by request_at

本题注意以下知识点：第一行中若列名有空格注意列名要用’ '引起来，注意MYSQL中若要算两列中每行各值之商，需要先group by request_at，第二行中为null的值若想用别的值代替，语句格式是ifnull,若不是null,则用co2填充该值，否则用0，倒数第二行注意如何判断一个日期是否在某个区间，利用between and语句即可，注意时间的格式是"ABCD-EF-GH"
ifnull参考链接：https://blog.csdn.net/qq_24549805/article/details/51892651
date参考链接：https://zhidao.baidu.com/question/2053253967813194187.html
小数位数控制参考链接：https://zhidao.baidu.com/question/686712295324393012.html
注意第一行中round的内容就是和小数位数控制相关的。
8.从不订购的客户题目中有关is null 与=null的说明：
https://www.cnblogs.com/kaiwensievert/p/7047112.html
9.count(1)与count(*):
https://blog.csdn.net/ifumi/article/details/77920767
10.部门工资最高的员工一题解答，该题没什么新意：

# Write your MySQL query statement below
select d.name as Department,e1.name as Employee,e1.salary as Salary 
from employee e1,(select distinct departmentid,salary from employee) e2,
department d
where e1.departmentid=e2.departmentid and e1.salary<=e2.salary and e1.departmentid=d.id
group by e1.id
having count(1)=1 

11.换座位题：注意union的使用

# Write your MySQL query statement below
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<(select count(1) from seat) union
select id as id,student from seat where id%2=1 and id=(select count(1) from seat)) s1
order by id

12.上升的温度一题，注意如何进行对日期加一天的操作，诸如date+1的操作是不可行的，这是因为如果date=2016-12-31,该date+1无法得到2017-01-01，而是得到2016-12-32，应使用相关函数：date_add(date,interval 1 day)
相关函数参考见：https://blog.csdn.net/liujava621/article/details/26599035
我的答案：

# Write your MySQL query statement below
select w1.Id
from weather w1,weather w2
where w1.recorddate=date_add(w2.recorddate,interval 1 day) and w1.temperature>w2.temperature

另外，根据本题测试了以下where和join on两者的效率，结论是貌似join on效率更高，以后会尽量使用join on，如有特殊情况再说明

13.删除重复的电子邮箱一题中，注意delete的语义：delete table1 from table2 where …
注意操作对象是table这一表，对该表中满足where条件的项进行删除，table1和where均可省略，因此该题的答案为：

# Write your MySQL query statement below
delete p1
from person p1,person p2
where p1.email=p2.email and p1.id>p2.id