一、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
网友评论