题目

几个朋友来到电影院的售票处，准备预约连续空余座位。你能利用表 cinema ，帮他们写一个查询语句，获取所有空余座位，并将它们按照 seat_id 排序后返回吗？
Day03 几个朋友来到电影院的售票处，准备预约连续空余座位 - 图1
对于如上样例，你的查询语句应该返回如下结果。

Day03 几个朋友来到电影院的售票处，准备预约连续空余座位 - 图2
seat_id 字段是一个自增的整数，free 字段是布尔类型（’1’ 表示空余， ‘0’ 表示已被占据）。
连续空余座位的定义是大于等于 2 个连续空余的座位。

数据准备

  1. create table cinema (
  2. seat_id int,
  3. free int
  4. );
  6. insert into cinema values(1,1);
  7. insert into cinema values(2,0);
  8. insert into cinema values(3,1);
  9. insert into cinema values(4,1);
  10. insert into cinema values(5,1);
  11. insert into cinema values(6,0);
  12. insert into cinema values(7,1);
  13. insert into cinema values(8,1);

解题

  1. 本题考查和连续时间类似,先按照free分组,座位ID排序,计算排序与座位ID的差值

    1. SELECT seat_id ,
    2.         free ,
    3.         ROW_NUMBER() OVER (PARTITION BY free ORDER BY seat_id) rn1,
    4.         ROW_NUMBER() OVER (PARTITION BY free ORDER BY seat_id) -  seat_id diff 
    5. FROM dbo.cinema
    6. WHERE free = 1 --之需要计算free = 1的未被占用座位

    image.png

  2. 计算出连续的diff值

    1. SELECT
    2.      T.free,
    3.      diff,
    4.      COUNT( 1 ) Times 
    5.  FROM
    6.      (
    7.      SELECT
    8.          seat_id,
    9.          free,
    10.          ROW_NUMBER ( ) OVER ( PARTITION BY free ORDER BY seat_id ) rn1,
    11.          ROW_NUMBER ( ) OVER ( PARTITION BY free ORDER BY seat_id ) - seat_id diff 
    12.      FROM
    13.          dbo.cinema 
    14.      WHERE
    15.          free = 1 
    16.      ) T 
    17.  GROUP BY
    18.      T.free,
    19.      diff 
    20.  HAVING
    21.      COUNT( 1 ) > 1

    image.png

  3. 再同diff关联查找出对应的座位ID

    1. SELECT
    2.  seat_id 
    3. FROM
    4.  (
    5.  SELECT
    6.      T.free,
    7.      diff,
    8.      COUNT( 1 ) Times 
    9.  FROM
    10.      (
    11.      SELECT
    12.          seat_id,
    13.          free,
    14.          ROW_NUMBER ( ) OVER ( PARTITION BY free ORDER BY seat_id ) rn1,
    15.          ROW_NUMBER ( ) OVER ( PARTITION BY free ORDER BY seat_id ) - seat_id diff 
    16.      FROM
    17.          dbo.cinema 
    18.      WHERE
    19.          free = 1 
    20.      ) T 
    21.  GROUP BY
    22.      T.free,
    23.      diff 
    24.  HAVING
    25.      COUNT( 1 ) > 1 
    26.  ) t
    27.  LEFT JOIN (
    28.  SELECT
    29.      seat_id,
    30.      free,
    31.      ROW_NUMBER ( ) OVER ( PARTITION BY free ORDER BY seat_id ) rn1,
    32.      ROW_NUMBER ( ) OVER ( PARTITION BY free ORDER BY seat_id ) - seat_id diff 
    33.  FROM
    34.      dbo.cinema 
    35.  WHERE
    36.  free = 1 
    37.  ) T2 ON t2.diff = t.diff;

    image.png