leetcode mysql题由简入深(未完持续)

最新推荐文章于 2023-07-14 14:07:09 发布

原创最新推荐文章于 2023-07-14 14:07:09 发布 · 256 阅读

0 ·

CC 4.0 BY-SA版权

DB 专栏收录该内容

4 篇文章

订阅专栏

本文分享了LeetCode上多个SQL挑战题目的解决方案，包括BigCountries、SwapSalary、NotBoringMovies等，涵盖了查找、更新、分组、连接及复杂查询技巧。

面试被虐的我只能刷leetcode了

595、Big Countries（正常的查找）
select name, population, area from World where(population>25000000 or area>3000000);
627、Swap Salary（交换m和f）
update salary
set sex=
case sex
when ‘f’ then ‘m’
when ‘m’ then ‘f’
end;
看到一个case写的非常好的文章: https://www.cnblogs.com/aipan/p/7770611.html
620、Not Boring Movies（id为奇数，排列）
select * from cinema where (description!=‘boring’ and id%2=1) order by rating desc;
182、Duplicate Emails（输出重复行，select嵌套）
select Email from(select Email, count(Email) as count from Person group by Email) as haha where count>1;
也可以用group by having，详细参考： https://www.cnblogs.com/gqs92/archive/2017/04/26/6767973.html
175、Combine Two Tables（left join用法）
select a.Firstname, a.LastName, b.City, b.State from Person a left join Address b on a.PersonId=b.PersonId;

181、Employees Earning More Than Their Managers（inner join，select嵌套）
~~select Employee from(select a.Name as Employee,a.Salary,b.Name as mName, b.Salary as mSalary from Employee a inner join Employee b on a.ManagerId=b.Id)as haha where Salary>mSalary;~~
select a.Name as Employee from Employee as a inner join Employee as b on a.ManagerId=b.Id where a.Salary>b.Salary;

答案2:
SELECT
a.Name AS ‘Employee’
FROM
Employee AS a,
Employee AS b
WHERE
a.ManagerId = b.Id
AND a.Salary > b.Salary
;

183、Customers Who Never Order（两个表join，null判断）
~~select Customers from(select a.Name as Customers,b.Id as order_id from Customers a left join Orders b on a.Id=b.CustomerId)as haha where order_id is null;~~
select a.Name as Customers from Customers as a where a.Id not in (select CustomerId from Orders);
596、Classes More Than 5 Students（去重，分组，group by having）
select class from (
select class, count(class) as num from(
select * from courses group by class,student
) as heihei group by class
) as haha where num>=5;
select class from(select * from courses group by student,class)as haha group by class having count(class)>=5;
197、Rising Temperature