One join solution


  • 0
    select m.id, m.date, m.people from 
    (
        select 
            st.id,
            st.date,
            st.people,
            @Index := (case when st.id-1 = @PreId then @Index + 1 else 1 end) as xIndex,
            @PreId := st.id
        from stadium st, (select @Index := 0, @PreId :=-1) x where st.people>=100
    ) m left join 
    (
        select 
            st.id,
            st.date,
            st.people,
            @Index := (case when st.id-1 = @PreId then @Index + 1 else 1 end) as xIndex,
            @PreId := st.id
        from stadium st, (select @Index := 0, @PreId :=-1) x where st.people>=100
    ) n on (m.xIndex = 1 and m.id+2 = n.id) or (m.xIndex = 2 and m.id+1=n.id) 
    where m.xIndex>=3 or n.id is not null order by m.id ASC
    

    I am wondering if it's possible to copy table m into table n directly without select operation again.


Log in to reply
 

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.