MySQL处理序列

“东风又作无情计，艳粉娇红吹满地”

序

最近工作上不忙，于是自己在学习。目前在看《SQL进阶教程》一书。该书我已上传到百度云，仅供大家学习使用。网盘连接请看：SQL进阶教程PDF下载
接下来从几个例子说明SQL语句如何处理序列：

订票三连坐（座位不分排）

和朋友们一起去旅行，预约火车票或机票时，却发现没有能让所有人挨着坐的空位，于是某个人不得不和大家分开坐——这样不爽的事情可能不少人都遭遇过吧。
我们假设存在下面这样一张存储了火车座位预订情况的表。

先假设这趟列车不分排，即所有座位均在一排。我们一行3个人旅行，订这趟列车的票。作图如下：

于是，我们希望得到的结果是下面四种：

●  3 ～ 5
●  7 ～ 9
●  8 ～ 10
●  9 ～ 11
SQL语句：

SELECT
	S1.seat AS start_seat,
	'～',
	S2.seat AS end_seat 
FROM
	Seats S1,
	Seats S2 
WHERE
	S2.seat = S1.seat + (:head_cnt -1)
	AND NOT EXISTS ( SELECT * FROM Seats S3 WHERE S3.seat BETWEEN S1.seat AND S2.seat AND S3.STATUS <> '未预订' ) 
ORDER BY
	start_seat;

其中，“ :head_cnt ”是表示需要的空位个数的参数。通过往这个参数里赋具体值，可以应对任意多个人的预约。这里我们需要的是3个人连坐，即(:head_cnt -1) = (3 -1) = 2（下同）。
查询结果：

这条查询语句充分体现了 SQL 在处理有序集合时的原理，这里详细地解说一下。对于这个查询的要点，我们分成两个步骤来理解更容易一些。
第一步：通过自连接生成起点和终点的组合
就这条 SQL 语句而言，具体指的是 S2.seat = S1.seat + (:head_cnt-1) 的部分。这个条件排除掉了像1～8、2～3这样长度不是3的组合，从而保证结果中出现的只有从起点到终点刚好包含 3 个空位的序列。
第二步：描述起点到终点之间所有的点需要满足的条件
决定了起点和终点以后，我们需要描述一下内部各个点需要满足的条件。为此，我们增加一个在起点和终点之间移动的所有点的集合（即以上SQL 中的 S3）。限定移动范围时使用 BETWEEN 谓词很方便。
在SQL 中遇到需要全称量化的问题时，一般的思路都是把“所有行都满足条件 P”转换成它的双重否定——不存在不满足条件 P 的行。“肯定⇔ 双重否定”之间的等价转换是使用 SQL 进行全称量化时的必备技巧，请熟练掌握。

订票三连坐（座位分排，5个座位一排）

新增座位行，如下：

所以，针对该结构，所需要的三连坐结果如下：

目前满足条件的结果是：

●  3 ～ 5
●  8 ～ 10
●  11 ～ 13

SQL语句：

SELECT
	S1.seat AS start_seat,
	' ~ ',
	S2.seat AS end_seat 
FROM
	Seats2 S1,
	Seats2 S2 
WHERE
	S2.seat = S1.seat + (:head_cnt -1) 
	AND NOT EXISTS (
SELECT
* 
FROM
Seats2 S3 
WHERE
S3.seat BETWEEN S1.seat 
AND S2.seat 
AND ( S3.STATUS <> '未预订' OR S3.row_id <> S1.row_id ) 
);

查询结果：

单调递增和单调递减

假如存在某公司股价波动情况如下图：

这里，我们求一下股价单调递增的时间区间。从上表来看，目标结果是下面两个。

● 2007-01-06 ～ 2007-01-08
● 2007-01-14 ～ 2007-01-17

按照前面讲过的基本方法，首先进行第一步——通过自连接生成起点和终点的组合。

-- 生成起点和终点的组合的 SQL 语句
SELECT
	S1.deal_date AS start_date,
	S2.deal_date AS end_date 
FROM
	MyStock S1,
	MyStock S2 
WHERE
	S1.deal_date < S2.deal_date;

结果的行数较多，一共有 28 个组合，因此这里不再展示。接下来我们排除掉不符合条件的组合，即进行第二步——描述起点和终点之间的所有点需要满足的条件。

-- 求单调递增的区间的 SQL 语句 ：子集也输出
SELECT S1.deal_date AS start_date,
S2.deal_date AS end_date
FROM MyStock S1, MyStock S2
WHERE S1.deal_date < S2.deal_date -- 第一步 ：生成起点和终点的组合
AND NOT EXISTS
( SELECT * -- 第二步 ：描述区间内所有日期需要满足的条件
FROM MyStock S3, MyStock S4
WHERE S3.deal_date BETWEEN S1.deal_date AND S2.deal_date
AND S4.deal_date BETWEEN S1.deal_date AND S2.deal_date
AND S3.deal_date < S4.deal_date
AND S3.price >= S4.price);

查询结果：

最后，我们要把这些不需要的子集排除掉。使用极值函数很容易就能实现。

-- 排除掉子集，只取最长的时间区间
SELECT MIN(start_date) AS start_date, -- 最大限度地向前延伸起点
end_date
FROM (SELECT S1.deal_date AS start_date,
MAX(S2.deal_date) AS end_date -- 最大限度地向后延伸终点
FROM MyStock S1, MyStock S2
WHERE S1.deal_date < S2.deal_date
AND NOT EXISTS
(SELECT *
FROM MyStock S3, MyStock S4
WHERE S3.deal_date BETWEEN S1.deal_date AND S2.deal_date
AND S4.deal_date BETWEEN S1.deal_date AND S2.deal_date
AND S3.deal_date < S4.deal_date
AND S3.price >= S4.price)
GROUP BY S1.deal_date) TMP
GROUP BY end_date;

查询结果：

这段代码的关键在于最大限度地延伸起点和终点。

写在文末

这篇知识确实给了我很大的帮助，我并不是指工作上，而是我看到用SQL能处理各种数学上的问题，对于我这个数学不好的程序员来说，确实很激励我，让我对数学有了新的认识和学习的兴趣。