请编写一个查询语句,找出人流量的高峰期。高峰期时,至少连续三行记录中的人流量不少于100。
思路:多表联合查询
由于这里是三个连续,所以选择三张表联合。首先需要每张表满足>100,然后3个id需要连续,所以就有了+1,-1的条件。
(s1.id=s2.id-1 and s2.id=s3.id-1) 指的是s1.id在三个连续id中最小;
(s1.id=s2.id+1 and s1.id=s3.id-1) 指的是s1.id在中间;
(s1.id=s2.id+1 and s2.id=s3.id+1))指的是s1.id在三个连续id中最大;
在选择s1.*时,以上三种情况中,只有是s1.id的位置是明确的,而s2.id和s3.id可以交换,所以有可能出现多个s1.id被选进来。
select s1.* from stadium s1,stadium s2,stadium s3
where s1.people>100 and s2.people>100 and s3.people>100 and (
(s1.id=s2.id-1 and s2.id=s3.id-1) or
(s1.id=s2.id+1 and s1.id=s3.id-1) or
(s1.id=s2.id+1 and s2.id=s3.id+1))
order by s1.id
果然有重复的,且重复的只是6,7。原因:当s1.id为6,时,s2.id=5,s3.id=7或者s2.id=7,s3.id=5。依次类推即可。
select distinct s1.* from stadium s1,stadium s2,stadium s3
where s1.people>100 and s2.people>100 and s3.people>100 and (
(s1.id=s2.id-1 and s2.id=s3.id-1) or
(s1.id=s2.id+1 and s1.id=s3.id-1) or
(s1.id=s2.id+1 and s2.id=s3.id+1))
order by s1.id