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 island
    from (
        select id, visit_date, people, rank() over (order by id) as rnk
        from stadium
        where people >= 100
    ) as t0
)
select id, visit_date, people
from stadium_with_rnk
where island in (
    select island
    from stadium_with_rnk
    group by island
    having 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 RequestAccepted
    union all
    select 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 RequestAccepted
    union all
    select accepter_id from RequestAccepted
)
select id, num
from (
    select id, count(id) as num,
           rank() over (order by count(id) desc ) as rnk
    from all_ids
    group 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_id
    from Orders
    join Company on Orders.com_id = Company.com_id and Company.name = 'RED'
    )