MySQL处理序列

小咪咪 2023-06-15 07:30 9阅读 0赞

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

## 序 ##

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

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

和朋友们一起去旅行，预约火车票或机票时，却发现没有能让所有人挨着坐的空位，于是某个人不得不和大家分开坐——这样不爽的事情可能不少人都遭遇过吧。  
我们假设存在下面这样一张存储了火车座位预订情况的表。  
![在这里插入图片描述][20191121162433180.png]  
先假设这趟列车不分排，即所有座位均在一排。我们一行3个人旅行，订这趟列车的票。作图如下：  
![在这里插入图片描述][20191121162754394.png]  
于是，我们希望得到的结果是下面四种：

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

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

新增座位行，如下：  
![在这里插入图片描述][201911211653113.png]  
所以，针对该结构，所需要的三连坐结果如下：  
![在这里插入图片描述][20191121165458326.png]  
目前满足条件的结果是：

●  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 ) 
    );

查询结果：  
![在这里插入图片描述][20191121170054562.png]

## 单调递增和单调递减 ##

假如存在某公司股价波动情况如下图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FfUnVubmVy_size_16_color_FFFFFF_t_70 1]  
这里，我们求一下股价单调递增的时间区间。从上表来看，目标结果是下面两个。

● 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);

查询结果：  
![在这里插入图片描述][20191121170913125.png]  
最后，我们要把这些不需要的子集排除掉。使用极值函数很容易就能实现。

-- 排除掉子集，只取最长的时间区间
    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;

查询结果：  
![在这里插入图片描述][20191121171046394.png]  
这段代码的关键在于最大限度地延伸起点和终点。

## 写在文末 ##

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

[SQL_PDF]: https://blog.csdn.net/A_Runner/article/details/103184166
[20191121162433180.png]: https://img-blog.csdnimg.cn/20191121162433180.png
[20191121162754394.png]: https://img-blog.csdnimg.cn/20191121162754394.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FfUnVubmVy_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191121163745432.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FfUnVubmVy,size_16,color_FFFFFF,t_70
[201911211653113.png]: https://img-blog.csdnimg.cn/201911211653113.png
[20191121165458326.png]: https://img-blog.csdnimg.cn/20191121165458326.png
[20191121170054562.png]: https://img-blog.csdnimg.cn/20191121170054562.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FfUnVubmVy_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20191121170349341.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FfUnVubmVy,size_16,color_FFFFFF,t_70
[20191121170913125.png]: https://img-blog.csdnimg.cn/20191121170913125.png
[20191121171046394.png]: https://img-blog.csdnimg.cn/20191121171046394.png