元旦假期就快到了,计划出去玩的朋友,都订好票了么?
今天,我们用 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
提前排除了,当然,不加这个条件对结果也没什么问题。