使用SQL查询选择连续的数字 [英] selecting consecutive numbers using SQL query

问题描述

这是剧院座位预订计划.

Here is a theater seats booking plan.

Seat No Status
1 Booked
2 Available
3 Available
4 Available
5 Available
6 Available
7 Booked
8 Available
9 Available
10 Available

如果某人想预订6张票，他将获得2至6号座位和8号座位 如果某人只想预订5张票，他将获得2至6号座位.

If someone wants to book 6 tickets, he will get Seat No. 2 to 6 and seat No. 8 And if someone wants to book only 5 tickets, he will get Seat No. 2 to 6

我如何使用SQL查询(或PHP代码)知道相邻的可用座位是否超过请求的座位?

How do I know using SQL query (or PHP code) if the adjacent seats available are more than the seats requested?

顺序选择座位是我需要实现的主要目标.

Sequential seat selection is the primary goal that I need to achieve.

推荐答案

尝试一下:

select seat, status
from seats
where seat >= (
   select a.seat
   from seats a
      left join seats b on 
         a.seat < b.seat and
         b.seat < a.seat + 4 and
         b.status = 'Available'
   where a.status = 'Available'
   group by a.seat
   having count(b.seat)+1 = 4
   )
limit 4

设置为选择四个连续的座位.将所有"4"实例调整为所需的座位数，即可获得所需的座位.

This is set to select four consecutive seats. Adjust all instances of "4" to the desired number of seats to get what you want.

这篇关于使用SQL查询选择连续的数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！