淘宝客网站主,北京工程建设信息网站,免费域名网站查询,做网站图片分辨率Q#xff1a;查找电影院所有连续可用的座位。 返回按 seat_id 升序排序 的结果表。 测试用例的生成使得两个以上的座位连续可用。 结果表格式如下所示。 A:我们首先找出所有的空座位#xff1a;1#xff0c;3#xff0c;4#xff0c;5 按照seat_id排序#xff08;上面已…Q查找电影院所有连续可用的座位。 返回按 seat_id 升序排序 的结果表。 测试用例的生成使得两个以上的座位连续可用。 结果表格式如下所示。 A:我们首先找出所有的空座位1345 按照seat_id排序上面已经有序并赋予排名 则
seat_idrnkseta_id-rnk110321431541
我们发现连续的数与其对应的排名均是连续的那么两者相减应该等于相同的数因此可以分为以下几步
# 1. t1:获取所有空座位
select seat_id from Cinema where free1# 2. t2:获取所有的连续数字对应的组
select
seat_id,
seat_id-row_number() over(order by seat_id) diff
from
(select seat_id from Cinema where free1
) t1# 3. t3:连续的数对应的diff是相同的可以按照diff分组并收集空座位号大于等于2的组号
select
diff
from
(selectseat_id,seat_id-row_number() over(order by seat_id) difffrom(select seat_id from Cinema where free1) t1
) t2
group by diff having count(seat_id) 2# 4. 根据t2表以及t3表获取大于等于2个以上的空座位号
select
seat_id
from
(selectseat_id,seat_id-row_number() over(order by seat_id) difffrom(select seat_id from Cinema where free1) t1
) t2
where diff in
(selectdifffrom (selectseat_id,seat_id-row_number() over(order by seat_id) difffrom(select seat_id from Cinema where free1) t1) t2group by diff having count(seat_id) 2
) 因此最终代码为
select
seat_id
from
(selectseat_id,seat_id-row_number() over(order by seat_id) difffrom(select seat_id from Cinema where free1) t1
) t2
where diff in
(selectdifffrom (selectseat_id,seat_id-row_number() over(order by seat_id) difffrom(select seat_id from Cinema where free1) t1) t2group by diff having count(seat_id) 2
)
