2个人一起去看电影,准备预定电影票横向坐在一起,从1-15编号中从这么多排座位中,找出连续2个空位的全部组合,结果输出座位组合情况例(1、2
4、5)
还有个问题:如果有4个人,连续写出4个空位连续组合怎么写语句,如果有又N个人怎么查询
2个人一起去看电影,准备预定电影票横向坐在一起,从1-15编号中从这么多排座位中,找出连续2个空位的全部组合,结果输出座位组合情况例(1、2
4、5)
先说下数据库类型及版本
如果支持开窗函数,可以用lead或者lag来获取相邻行的数据,再来对数据进行判断即可
下面是连续两个的
with t(seat,status) as (
select 1,'Y' FROM DUAL UNION ALL
select 2,'Y' FROM DUAL UNION ALL
select 3,'N' FROM DUAL UNION ALL
select 4,'N' FROM DUAL UNION ALL
select 5,'N' FROM DUAL UNION ALL
select 6,'Y' FROM DUAL UNION ALL
select 7,'N' FROM DUAL UNION ALL
select 8,'N' FROM DUAL UNION ALL
select 9,'N' FROM DUAL UNION ALL
select 10,'N' FROM DUAL UNION ALL
select 11,'N' FROM DUAL UNION ALL
select 12,'Y' FROM DUAL UNION ALL
select 13,'Y' FROM DUAL UNION ALL
select 14,'N' FROM DUAL UNION ALL
select 15,'N' FROM DUAL )
select SEAT,L_SEAT from (
select T.*,
LAG(SEAT) OVER(ORDER BY SEAT) L_SEAT,
LAG(STATUS) OVER(ORDER BY SEAT) L_STATUS from T)
WHERE status='N' AND L_STATUS='N'
下面是4个及以上连续座位的
select MIN(SEAT),MAX(SEAT) from (
select tt.*,sum(l) over(order by seat) d from (
select T.*,
case when LAG(STATUS) OVER(ORDER BY SEAT) =status then 0 else 1 end l from T) tt
) where status='N'
GROUP BY D HAVING COUNT(1)>=4