连续性问题

力扣603. 连续空余座位

如题：

方法一：使用多表连接，选出连续值进行去重

select distinct c1.seat_id from cinema c1
join cinema c2 
on abs(c1.seat_id - c2.seat_id) = 1
and c1.free  = 1 and c2.free = 1
order by c1.seat_id

例子：
当c1=c2=1，2，3，4，5时，
当c1=1时，abs(c1.seat_id - c2.seat_id) = 1,c2只能取[2],c2.free=1不成立
当c1=2时，abs(c1.seat_id - c2.seat_id) = 1,c2可以取[1,3],c1.free=1不成立
当c1=3时，abs(c1.seat_id - c2.seat_id) = 1,c2可以取[2,4],当c2=2时，c2.free=1不成立，但是c2=4时，c2.free=1成立，所以取一次[3]
当c1=4时，abs(c1.seat_id - c2.seat_id) = 1,c2可以取[3,5],c2都满足
所以要取两次[4,4]，所以最后要进行去重

方法二：使用窗口函数，进行排名筛选

select seat_id from
(select seat_id,count(seat_id) over(partition by cnt) cnt from
(select seat_id,(seat_id - aaa) as cnt from
(select *,row_number() over() as aaa from cinema
where free = 1)t1
)t2
) t3
where cnt >1

第一步，筛选出free=1的，进行排名

第二步，进行seat_id-aaa,挨着的，数字都会相等

第三步：最后分组筛选计算count(cnt)>1