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