SQL 预订座位

元旦假期就快到了，计划出去玩的朋友，都订好票了么？

今天，我们用 SQL 模拟订座的场景。

seats 是座位预订表，表结构如下：

CREATE TABLE `seats` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `row_no` int DEFAULT NULL COMMENT '第几排',
  `seat` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '座位',
  `status` int NOT NULL COMMENT '预定状态 0-未预定 1-已预定',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8

其中，id 是主键，从 1 起连续递增。

seats 表的数据：

    id  row_no  seat    status  
------  ------  ------  --------
     1       1  A              1
     2       1  B              1
     3       1  C              0
     4       1  D              0
     5       1  F              0
     6       2  A              1
     7       2  B              0
     8       2  C              0
     9       2  D              0
    10       2  F              0
    11       3  A              0
    12       3  B              1
    13       3  C              1
    14       3  D              0
    15       3  F              0

假设是 3 个朋友一起出去玩，希望能预订到相邻的座位。现在这趟车某个车厢里每排的座位的编号是 A、B、C、D、F，其中，A 和 F 是靠窗位置，C 和 D 之间是过道。即使隔着过道，C 和 D 仍是可以看作是相邻的座位。

因此，预订到同一排的三个座位的编号是 A ~ C、B ~ D、C ~ F 其中一种都行。

如果你看了我的上一篇文章，你就会发现，这个需求和上一篇文章里面的需求很相似，只不过在这个需求里多了一个限定条件：要求连续的子序列在同一排（组）中。

我们把上一篇文章的实现方案稍微改改，就能实现本次的查询需求。

WITH cte AS 
(SELECT 
  *,
  row_number () over (PARTITION BY row_no 
ORDER BY id) AS rn 
FROM
  seats 
WHERE STATUS = 0) 
SELECT 
  a.row_no,
  CONCAT_WS('~', a.seat, b.seat) AS seat 
FROM
  cte a 
  INNER JOIN cte b 
    ON a.id + 2 = b.id 
    AND a.rn + 2 = b.rn 

预订到的座位>>>

row_no  seat    
------  --------
     1  C~F     
     2  B~D     
     2  C~F     

如果不用窗口函数呢，是否能实现？当然，也不是要换成用户变量（在 MySQL 中，可通过用户变量实现窗口函数的大部分功能）。我的意思是说，换个思路。

另一种实现方式：获取同一排中所有相邻的三个座位，如果这三个座位都没有被预订，那就说明可以预订。

获取所有相邻的三个座位的 SQL 实现：

SELECT 
  a.row_no,
  CONCAT_WS('~', a.seat, b.seat) AS seat 
FROM
  seats a 
  INNER JOIN seats b 
    ON b.row_no = a.row_no AND b.id = a.id + 2 

相邻的三个座位 >>>

row_no  seat    
------  --------
     1  A~C     
     1  B~D     
     1  C~F     
     2  A~C     
     2  B~D     
     2  C~F     
     3  A~C     
     3  B~D     
     3  C~F     

再加上座位没有被预订的过滤条件，完整的 SQL ：

SELECT 
  a.row_no,
  CONCAT_WS('~', a.seat, b.seat) AS seat 
FROM
  seats a 
  INNER JOIN seats b 
    ON b.row_no = a.row_no 
    AND b.id = a.id + 2 
WHERE a.status = 0 
  AND b.status = 0 
  AND NOT EXISTS 
  (SELECT 
    NULL 
  FROM
    seats c 
  WHERE c.id BETWEEN a.id 
    AND b.id 
    AND c.status = 1)

SQL 中在获取相邻的座位时把两头的座位已被预订的情况通过条件 WHERE a.status = 0 AND b.status = 0 提前排除了，当然，不加这个条件对结果也没什么问题。