LeetCode | SQL | 3题

凡是遥远的地方 对我们都有一种诱惑
不是诱惑于美丽 就是诱惑于传说
即便远方的风景 并不尽人意
我们也无需在乎 因为这实在是一个迷人的错
——汪国真《远方》

595. Big Countries

image.png

select name, population, area from world
where area > 3000000 or population > 25000000;

596. Classes More Than 5 Students

image.png image.png
笔记：此处条件选择不能用where，而应使用having.

select class from courses
group by class
having count(distinct student) >= 5;

601. Human Traffic of Stadium
X city built a new stadium, each day many people visit it and the stats are saved as these columns: id, date, people
Please write a query to display the records which have 3 or more consecutive rows and the amount of people more than 100(inclusive).

image.png
笔记：此处使用了三个表的笛卡尔乘积，适用于表较小的情况。

select distinct a.* from stadium a, stadium b, stadium c
where a.people >= 100 and b.people >= 100 and c.people >= 100
and 
(   (a.id - b.id = 1 and a.id - c.id = 2 and b.id - c.id = 1)  
or  (b.id - a.id = 1 and b.id - c.id = 2 and a.id - c.id = 1)  
or  (c.id - b.id = 1 and c.id - a.id = 2 and b.id - a.id = 1)  
)
order by a.id;