SQLLL

595-big-countries

https://leetcode.com/problems/big-countries/description/

面积最大的国家

--
select name, population, area
from World
where area >= 3000000 or population >= 25000000

596-classes-with-at-least-5-students

https://leetcode.com/problems/classes-with-at-least-5-students/description/

超过 5 名学生的课

select class
from Courses
group by class
having count(student) >= 5

having和where关键区别：

WHERE：在聚合之前过滤行。
HAVING：在聚合之后过滤分组。

总结：WHERE 用于过滤行数据，而 HAVING 用于过滤分组后的数据。

601-human-traffic-of-stadium

https://leetcode.com/problems/human-traffic-of-stadium/description/

体育馆的人流量

id连续三行或更多

select distinct a.*
from stadium as a,stadium as b,stadium as c
where a.people >= 100 and b.people >= 100 and c.people >= 100
and ((a.id - b.id = 1 AND b.id - c.id = 1)OR (c.id - b.id = 1 AND b.id - a.id = 1)OR (b.id - a.id = 1 AND a.id - c.id = 1))
order by a.visit_date;
-- 用ID减去排名
with stadium_with_rnk as (select id, visit_date, people, rnk, (id - rnk) as islandfrom (select id, visit_date, people, rank() over (order by id) as rnkfrom stadiumwhere people >= 100) as t0
)
select id, visit_date, people
from stadium_with_rnk
where island in (select islandfrom stadium_with_rnkgroup by islandhaving count(*) >= 3)
order by visit_date

602-friend-requests-ii-who-has-the-most-friends

https://leetcode.com/problems/friend-requests-ii-who-has-the-most-friends/description/

好友申请 II ：谁有最多的好友

拥有最多的好友的人和他拥有的好友数目

with all_ids as (select requester_id as id from RequestAcceptedunion allselect accepter_id from RequestAccepted
)
select id, count(id) as num
from all_ids
group by id
order by count(id) desc limit 1;-- use rank
with all_ids as (select requester_id as id from RequestAcceptedunion allselect accepter_id from RequestAccepted
)
select id, num
from (select id, count(id) as num,rank() over (order by count(id) desc ) as rnkfrom all_idsgroup by id
)t0
where rnk = 1

607-sales-person

https://leetcode.com/problems/sales-person/description/

销售员

select name
from SalesPerson
where sales_id not in (select Orders.sales_idfrom Ordersjoin Company on Orders.com_id = Company.com_id and Company.name = 'RED')