一、题目
地址:
二、分析
三种情况下满足筛选条件:
1、判断第一行时,如果本行和后面两行都满足大于等于100,,则第一行满足要求;
2、判断最后一行时,如果本行和前面两行都满足大于等于100,,则最后一行满足要求;
3、判断中间行时,如果本行和前、后相邻行都满足大于等于100,,则该行满足要求;
要逐行判断,判断上下文与要判断的行相关,用相关子查询。
三、代码
select *
from Stadium st --逐行查询
where (
(
select count(*)
from Stadium s
where (s.id between st.id-1 and st.id+1) and s.people >=100
) =3 --判断中间行
or
(
select count(*)
from Stadium s
where (s.id between st.id and st.id+2) and s.people >=100
) =3 --判断第一行
or
(
select count(*)
from Stadium s
where (s.id between st.id-2 and st.id) and s.people >=100
) =3 --判断最后一行
)