代码之家 › 专栏 › 技术社区 › Joshua Rodgers

SQL-查找给定大小的连续条目

mysql sql

Joshua Rodgers · 技术社区 · 15 年前

我正在研究一个预定座位的系统。用户输入希望预订的座位数,数据库将返回一组建议的座位,这些座位以前没有预订过,与预订的座位数匹配。

例如,如果我有桌子:

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

以及用户希望预订2个座位的输入,我希望查询返回未预订的座位(3、4)、(4、5)和(8、9),并与给定的输入座位数量匹配。座位分为几段和几排。连续座椅必须在同一排。

如何构造这个查询,使其能够找到与给定输入匹配的所有可用连续座位?

3 回复 | 直到 11 年前

Ben M 15 年前

使用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

请注意,这将返回IDS3、4和8,因为每个位置都有两个空闲座位。

这还假定座位ID始终是连续的。如果没有,你可以进入 ROW_NUMBER() .

Thomas 15 年前

此解决方案只能在SQL Server 2005+或其他支持CTE的产品中使用。此外,我假设中的“reserved”存储方式与您的示例中的字符串存储方式相同,而不是位存储方式。最后,我假设seatid是完全连续的:

   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

mstzn 11 年前

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