LeetCode SQL做题笔记

176题

第一次答案select salary as SecondHighestSalary from employee order by salary asc limit 1 offset 1 没通过，因为题目要求没有的时候返回null，所以再外面再加一层select，把上一个查询作为临时表。select (select distinct salary as SecondHighestSalary from employee order by salary asc limit 1 offset 1) as SecondHighestSalary;还需要考虑去重。

607题

第一次答案select sales_person.name from sales_person inner join orders on sales_person.sales_id=orders.sales_id where com_id not in (select com_id from company where name="RED");错误，应为order表中有销售员有其他公司的关联，导致错误，sql也是错误的

第二次答案select salesperson.name from salesperson where salesperson.sales_id not in (select orders.sales_id from orders inner join company on orders.com_id=company.com_id where company.name="RED")使用反向的条件，在子查询中找到所有有关联的销售，然后不要这些销售，就完成了。

620题

第一次答案select * from cinema where (id%2 = 1) and description not like "%boring%"
第二次答案select * from cinema where (id%2 = 1) and description not like "%boring%" order by rating desc
总结，忘记排序了。

第1050题

第一次答案select actor_id, director_id from (select actor_id, director_id, count(actordirector.timestamp) as c from actordirector group by actor_id, director_id) as temp where temp.c>=3;一次通过。

262题

多次尝试后得到结果select temp1.request_at as Day, round(sum(if(status="completed", 0, 1))/count(id), 2) as "Cancellation Rate" from (select distinct * from trips where client_id not in (select users_id from users where banned ="YES") and driver_id not in (select users_id from users where banned ="YES")) as temp1 group by request_at
但是提交后有一个测试用例不通过，问题是数据就只有一条，有时间再看看。

别人题解，

SELECT T.request_at AS `Day`,  ROUND(SUM(IF(T.STATUS = 'completed',0,1))/COUNT(T.STATUS),2) AS `Cancellation Rate` FROM trips AS T WHERE T.Client_Id NOT IN ( SELECT users_id FROM users WHERE banned = 'Yes') AND T.Driver_Id NOT IN ( SELECT users_id FROM users WHERE banned = 'Yes') AND T.request_at BETWEEN '2013-10-01' AND '2013-10-03' GROUP BY T.request_at

结合别人的题解后，我的最终题解，其实是少了时间约束条件

select temp1.request_at as Day, round(sum(if(status="completed", 0, 1))/count(id), 2) as "Cancellation Rate" from (select distinct * from trips where client_id not in (select users_id from users where banned ="YES") and driver_id not in (select users_id from users where banned ="YES") and request_at between '2013-10-01' and '2013-10-03') as temp1 group by request_at

总结，粗心导致条件没有用全，解题思路没问题。要细心。

1141题

第一次答案select activity_date as day, count(distinct user_id) as active_users from activity group by activity_date;测试用例不通过，因为缺少近30天的条件。

第二次答案select activity_date as day, count(distinct user_id) as active_users from activity where activity_date >= date_sub("2019-07-27", interval 29 day) and activity_date <="2019-07-27" group by activity_date;添加完善的时间限制，通过所有测试用例，但是性能不太好。

别人的答案select activity_date as day ,count(distinct user_id) as active_users from Activity where activity_date between '2019-06-28' and '2019-07-27' group by activity_date; 会稍微快一点，其中的6月28号可以用date_sub替换，
也可以使用DATEDIFF函数SELECT activity_date AS day,COUNT(DISTINCT user_id) AS active_user FROM Activity WHERE DATEDIFF('2019-7-27',activity_date) <30 AND activity_date <= '2019-07-27' GROUP BY activity_date

627题

一次通过，答案update salary set sex=if(sex="m", "f", "m");

官方题解使用case替换了if UPDATE salary SET sex = CASE sex WHEN 'm' THEN 'f' ELSE 'm' END;