SQL - Finding continuous entries of a given size

I am working on a system for reserving seats. A user inputs how many seats they wish to reserve and the database will return a set of suggested seats that are not previously reserved that matches the number of seats being reserved.

For instance if I had the table:

SeatID | Reserved
-----------------
1      | false
2      | true
3      | false
4      | false
5      | false
6      | true
7      | true
8      | false
9      | false
10     | true

And the user inputs that they wish to reserve 2 seats, I would expect the query to return that seats (3, 4), (4, 5), and (8, 9) are not reserved and match the given number of input seats. Seats are organized into sections and rows. Conti开发者_如何学运维nuous seats must be in the same row.

How would I go about structuring this query to work in such a way that it finds all available continuous seats that match the given input?

Using SQL Server 2005/2008:

WITH FreeSeatGroups AS
(
    SELECT S1.SeatID AS StartID,
           (SELECT MIN(S2.SeatID)
            FROM Seats S2 
            WHERE S2.SeatID > S1.SeatID
            AND S2.Reserved = 1) - S1.SeatID AS FreeSeatCount
    FROM Seats AS S1
    WHERE S1.Reserved = 0
)
SELECT StartID, FreeSeatCount
FROM FreeSeatGroups
WHERE FreeSeatCount >= 2

Note that this returns IDs 3, 4, and 8, since there are two free seats starting at each of those positions.

This also assumes that Seat IDs are always sequential. If not, you can get into ROW_NUMBER().

This solution will only work in SQL Server 2005+ or some other product that supports CTEs. In addition, I assumed that "Reserved" in was stored just as in your example as a string as opposed to a bit. Lastly, I assumed that SeatId was perfectly sequential:

   With
    StartSeats As
    (
    Select SeatId + 1 As SeatId
    From Seats As S
    Where Reserved = 'true'
        And Not Exists(
                        Select 1
                        From Seats As S2
                        Where S2.SeatId = S.SeatId + 1
                            And S2.Reserved = 'true'
                        )
        And SeatId < ( Select Max(S1.SeatId) From Seats As S1 ) 
    Union All
    Select SeatId
    From Seats
    Where SeatId = 1 
        And Reserved = 'false'
    Union All
    Select SeatId
    From Seats
    Where SeatId = ( Select Max(S1.SeatId) From Seats As S1 )   
        And Reserved = 'false'
    )
    , SeatRanges As
    (
    Select S1.SeatId As FirstOpenSeat, Min(S2.SeatId) As LastReservedSeat
    From StartSeats As S1
        Left Join Seats As S2
            On S2.SeatId > S1.SeatId
                And S2.Reserved = 'true'
    Group By S1.SeatId          
    )
Select *
From SeatRanges
Where LastReservedSeat - FirstOpenSeat = 2

WITH FreeSeatGroups AS
(
   select s1.ss StartID,(select  min (s2.ss) 
   from test123 s2
   WHERE S2.ss >= S1.ss
   and S2.rr = 1) -s1.ss FreeSeatCount  from test123 s1
)
SELECT StartID, FreeSeatCount
FROM FreeSeatGroups
WHERE FreeSeatCount >= 1