sql - 找到三个连续可用的座位号

Question

预订

    ROW_NUM      SEAT_NUMBER    STATUS
    R1           S1             Reserved
    R1           S2             Reserved
    R1           S3             Free
    R1           S4             Free
    R1           S5             Free
    R2           S1             Reserved
    R2           S2             Reserved
    R2           S3             Free
    R2           S4             Free
    R2           S5             Free
    R3           S1             Free
    R3           S2             Reserved
    R3           S3             Free
    R3           S4             Reserved
    R3           S5             Reserved

所需输出：

ROW_NUM      SEAT_NUMBER 
   R1           S3             
   R1           S4             
   R1           S5 
   R2           S3            
   R2           S4             
   R2           S5    

标签： sqloracle

Answer 1

您可以使用row_number(). 以下标识了相邻的免费座位组：

select t.*, (seqnum - seqnum_s) as grp
from (select t.*,
             row_number() over (partition by row_num order by seat_number) as seqnum,
             row_number() over (partition by row_num, status order by seat_number) as seqnum_s
      from t
     ) t
where status = 'Free';

您可以使用更多窗口函数添加数字：

select t.*
from (select t.*,
             count(*) over (partition by row_num, seqnum - seqnum_s, status) as cnt
      from (select t.*,
                   row_number() over (partition by row_num order by seat_number) as seqnum,
                   row_number() over (partition by row_num, status order by seat_number) as seqnum_s
            from t
           ) t
      where status = 'Free'
     ) t
where cnt >= 3;