Click here to see the full article post
select stadium.id, stadium.date, stadium.people from stadium,
select s1.id as id1, s2.id as id2 from stadium s1, stadium s2 where s2.id - s1.id >= 2 and s1.people >= 100 and ( select max(s5.people) from stadium s5 where s5.id = s1.id - 1 or s5.id = s2.id + 1 ) < 100 and ( select min(s3.people) from stadium s3 where s3.id >= s1.id and s3.id <= s2.id ) >=100
where id >=s4.id1 and id <= s4.id2
SELECT DISTINCT t1.id,t1.date,t1.people FROM stadium t1,stadium t2,stadium t3 WHERE
ORDER BY t1.id;
i do not think this problem to described appropriately ,questions reveal that consecutive days,however,after 5-29,it seems to be 6-1 the next day.so ,we downgrade to check conditions as id.i don't think this a nice way
@zangguodong Is it a test case issue or what? Let me know so that I may correct/improve it, please.
Firstly,thank you for your note.
It's my fault to misunderstand the question.Question puts that " 3 or more consecutive rows " ,not" 3 or more consecutive days ",so my ans:
select distinct a.id,a.date,a.people from stadium a,stadium b,stadium c where a.people>=100
and b.people>=100 and c.people>=100 and
((b.date=a.date+1 and c.date=a.date+2) or
(b.date=a.date-1 and c.date=a.date+1) or
(b.date=a.date-2 and c.date=a.date-1)) order by a.date
cannot pass test when there be data:
test case are ok,sorry again foo my disturbance
@zangguodong Not a problem. I am glad you figure it out finally.
select id,date, people from (
(select count() from stadium where id=aa.id-2 and people>=100) as a,
(select count() from stadium where id=aa.id-1 and people>=100) as b,
people>=100 as c,
(select count() from stadium where id=aa.id+1 and people>=100) as d,
(select count(*) from stadium where id=aa.id+2 and people>=100) as e
from stadium aa) bb
where c and ((a and b) or (b and d) or (d and e));
select s1.* from
((s1.id-s2.id=1 and s2.id-s3.id=1) or
(s3.id-s2.id=1 and s2.id-s1.id=1)or
(s3.id-s1.id=1 and s1.id-s2.id=1)) and
(s1.people>=100 and s2.people>=100 and s3.people>=100)
group by s1.id
Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.