leet code sql 做题笔记一easy part

一、easy题部分 20题

总结：

a. date的时间差用datediff，

b. 余数用%或者mod(分母，分子），

c.join的时候要注意有没有Null，

d.自Join的时候要注意逻辑关系，

e.not in后面可以跟select 嵌套，

f. union 比Or更快，

g.limit(a,b)实现从a+1也开始分b页，

h. 去重可以用delete

i.distinct 后面不用加括号所以想count distinct就是count(distinct A)

j.会用abs()函数

k. update table set column = case... when来更新某列

l. ifnull和round的用法

下面是具体的题目

1.183. Customers Who Never Order

select name as"Customers" from customers where id not in (select customerid from orders);

not in 后面不能直接跟customerid要跟从xxtable里面选出来customerid 要跟select!

还有简单的题也要搞清楚逻辑关系

2. union is faster than or

595. Big Countries

当需要scan两个不同的列的时候，用union比用or快;

union 和union all 的区别：union只留下distinct 的结果，union all会保留duplicate的结果；

3.176. Second Highest Salary 怎么选第二高

一个思路第二高是除去第一高以外最高的

select max(Salary) as 'SecondHighestSalary' from Employee where Salary<(select max(Salary) from Employee)

另一个思路用排序然后Limit

limit 1 offset 1是用来实现分页功能的，意思是返回一页，从第二页开始，也可以写作limit 1,1

举个例子 limit 9,4意思是返回四页，从第十页开始，也就是10,11,12,13这四页；

4. 175. Combine Two Tables

select FirstName, LastName,City,State from Person left join Address on Person.PersonID= Address.PersonID

当题目表示不管有没有Null值的时候就可以用left join, 直接用join是不会保留Null的；正如官方的解释“Considering there might not be an address information for every person, we should use outer join instead of the default inner join.”

5.182. Duplicate Emails:

Write a SQL query to find all duplicate emails in a table

select Email from Person group by Email having count(*)>1

用groupby 和having

6.620. Not Boring Movies

select * from cinema where id %2=1 and description !='boring' order by rating desc

取余数的一种表达方式是%，另一种表达方式是mod(id,2)意思是id除以2得到的余数

7.196. Delete Duplicate Emails

根据内容去重但id只留下最小的那个id 用delete的方法把同一个Email但是序号更大的那个删掉 ,可谓去重（这个想法很巧妙了）

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

8.197. Rising Temperature

和date有关的sql 我们需要用到DATEDIFF() 日期差，隔一天合并表即self join的时候用的on是datediff=1，这个interval是(date1,date2)前者减后者的值

select w1.Id from Weather as w1 join Weather as w2 on datediff(w1.RecordDate,w2.RecordDate)=1 where w1.Temperature>w2.Temperature

9.181. Employees Earning More Than Their Managers 又是一个self join的问题

select e1.Name as 'Employee' from Employee as e1, Employee as e2 where e1.ManagerId=e2.id and e1.Salary>e2.Salary

自Join一定要弄清逻辑关系是谁等于谁

selectnameasEmployeefromemployeeewheresalary > (selectsalaryfromemployeewhereid =e.managerid) 另一种解法用subquery 但一般来说subquery要慢一些

10.619. Biggest Single Number

一个subquery问题

select max(num) from number where num in (select num from number group by num having count(*)=1)

11. 577. Employee Bonus

这里的trick是如何处理Null值不要忘了null值

12.584. Find Customer Referee 依然是别忘了null值

13.586. Customer Placing the Largest Number of Orders

碰到largest 用groupby然后order desc然后limit1

14.596. Classes More Than 5 Students

distinct 后面不用加括号所以想count distinct就是count(distinct A)

15. 597. Friend Requests I: Overall Acceptance Rate

这是一个distinct和round的问题，还要注意有没有Null值

注意格式上不要犯错误比如select distinct * from (select sender_id, send_to_id from friend_request) as f 这是对的 select distinct * from select sender_id, send_to_id from friend_request as f这就是错的

关于Null值可以用ifnull(var1,var2)的意思是如果为null则取var2 如果不为null则取var1(一般是原值），round(数值，小数点后几位）

select round(ifnull(count(distinct requester_id, accepter_id)/count(distinct sender_id, send_to_id),0),2) as accept_rate from friend_request, request_accepted

16. 603. Consecutive Available Seats

当连续两个值一样的时候，我们在join的时候要考虑可能是和上一行值相同也可能是和下一行相同，所以仅仅用t1.id=t2.id-1是不对的,这个只显示了单方向，如何双向呢？要用绝对值，即abs(t1.id-t2.id)=1。

select distinct c1.seat_id from cinema as c1, cinema as c2 where abs(c1.seat_id-c2.seat_id)=1 and c1.free=c2.free and c1.free="1" order by c1.seat_id

17.607. Sales Person

这题有三个表，在写条件的时候要好好检查, 还有就是简化join的过程，能先处理好再Join就先处理好，不要先Join再处理，会降低查询的效率

eg: 低效的query长这样:

select distinct s.name from salesperson as s, company as c, orders as o where o.com_id=c.com_id and s.sales_id not in (select sales_id from orders where com_id=(select com_id from company where name='RED'))

高效的query长这样：

SELECT s.name FROM salesperson s WHERE s.sales_id NOT IN (SELECT o.sales_id FROM orders o LEFT JOIN company c ON o.com_id = c.com_id WHERE c.name = 'RED')

或者select s.name from salesperson as s where s.sales_id not in (select sales_id from orders where com_id=(select com_id from company where name='RED'))

18.610. Triangle Judgement 这是一条case when题，注意NO和YES要加单引号，记住case when的语句写法

select x,y,z,

case

when x+y<=z or x+z<=y or y+z<=x then 'No'

else 'Yes'

end as triangle

from triangle

19.613. Shortest Distance in a Line 找最短路径题

select min(abs(p2.x-p1.x)) as shortest from point p1, point p2 where p1.x<>p2.x 这里用到绝对值因为距离是绝对值，以及不等于<>

20.627. Swap Salary 更新某一列用update和case...when

update salary

set sex=

case sex

when 'm' then 'f'

when 'f' then 'm'

end

注意格式 update后面跟table，然后set你要的列=case when