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')
