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

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

  •  4
  • 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 年前
        1
  •  2
  •   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() .

        2
  •  1
  •   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
    
        3
  •  0
  •   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