问题遇到的现象和发生背景
4个人一起去看电影,准备预订电影票横向坐一起,从这么多排座位中,找出连续4个空位的全部组合,结果输出座位组合情况,例(1
问题相关代码,请勿粘贴截图
--use sqlsever
select CONCAT('(',a.seat,'~',b.seat,')') 座位
from eight_five_three a left join
(select seat,status,rowid from eight_five_three group by seat,status,rowid) b on (a.seat=b.seat-1 and b.status = '未预订'and a.rowid = b.rowid) left join
(select *,count(seat) x from eight_five_three group by seat,status,rowid) c on ((c.seat between b.seat and a.seat) and c.status = '未预订' and c.rowid = a.rowid)
where a.status = '未预订' and c.x=2
group by a.seat,b.seat
运行结果及报错内容
尝试写出了如上代码,不知道为什么查不出数据
我的解答思路和尝试过的方法
查出首座位和尾座位,查中间满足条件的座位数量是否满足要求,满足则输出首尾座位
我想要达到的结果
结果输出座位组合情况