(资料图片)

1.处理有序集合也并非SQL的直接用途

1.1.SQL语言在处理数据时默认地都不考虑顺序

2.处理数据的方法有两种

2.1.第一种是把数据看成忽略了顺序的集合

2.2.第二种是把数据看成有序的集合

2.2.1.首先用自连接生成起点和终点的组合

2.2.2.其次在子查询中描述内部的各个元素之间必须满足的关系

2.2.2.1.要在SQL中表达全称量化时，需要将全称量化命题转换成存在量化命题的否定形式，并使用NOT EXISTS谓词

3.生成连续编号

3.1.序列对象（sequence object）

3.1.1.CONNECT BY（Oracle）

3.1.2.WITH子句（DB2、SQL Server）

3.1.3.依赖数据库实现的方法

3.2.示例

3.2.1.

3.2.1.1.--求连续编号(1)：求0~99的数

SELECT D1.digit + (D2.digit ＊ 10) AS seq FROM Digits D1 CROSS JOIN Digits D2 ORDER BY seq;

3.2.1.2.--求连续编号(2)：求1~542的数

SELECT D1.digit + (D2.digit ＊ 10) + (D3.digit ＊ 100) AS seq FROM Digits D1 CROSS JOIN Digits D2 CROSS JOIN Digits D3 WHERE D1.digit + (D2.digit ＊ 10) + (D3.digit ＊ 100) BETWEEN 1 AND 542 ORDER BY seq;

3.2.1.3.--生成序列视图（包含0~999）

CREATE VIEW Sequence (seq) AS SELECT D1.digit + (D2.digit ＊ 10) + (D3.digit ＊ 100) FROM Digits D1 CROSS JOIN Digits D2 CROSS JOIN Digits D3;

3.2.1.3.1.--从序列视图中获取1~100

SELECT seq FROM Sequence WHERE seq BETWEEN 1 AND 100 ORDER BY seq;

3.3.冯·诺依曼的方法使用递归集合定义自然数，先定义0然后得到1，定义1然后得到2，是有先后顺序的

3.3.1.适用于解决位次、累计值等与顺序相关的问题

3.4.这里的解法完全丢掉了顺序这一概念，仅把数看成是数字的组合。这种解法更能体现出SQL语言的特色

4.求全部的缺失编号

4.1.示例

4.1.1.--EXCEPT版

SELECT seq FROM Sequence WHERE seq BETWEEN 1 AND 12 EXCEPT SELECT seq FROM SeqTbl;

4.1.1.1.--NOT IN版

SELECT seq FROM Sequence WHERE seq BETWEEN 1 AND 12 AND seq NOT IN (SELECT seq FROM SeqTbl);

4.1.2.--动态地指定连续编号范围的SQL语句

SELECT seq FROM Sequence WHERE seq BETWEEN (SELECT MIN(seq) FROM SeqTbl) AND (SELECT MAX(seq) FROM SeqTbl) EXCEPT SELECT seq FROM SeqTbl;

4.1.2.1.查询上限和下限未必固定的表时非常方便

4.1.2.2.两个自查询没有相关性，而且只会执行一次

4.1.2.3.如果在“seq”列上建立索引，那么极值函数的运行可以变得更快速

5.座位预订

5.1.三个人能坐得下吗

5.1.1.

5.1.1.1.--找出需要的空位(1)：不考虑座位的换排

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 ＜＞’未预订’);

5.1.1.1.1.“:head_cnt”是表示需要的空位个数的参数

5.1.1.1.2.如果不减1，会多取一个座位

5.1.1.2.第一步：通过自连接生成起点和终点的组合

5.1.1.2.1.S2.seat = S1.seat + (:head_cnt-1)的部分

5.1.1.2.2.排除掉了像1～8、2～3这样长度不是3的组合

5.1.1.3.第二步：描述起点到终点之间所有的点需要满足的条件

5.1.1.3.1.序列内的点需要满足的条件“所有座位的状态都是‘未预订’”

5.1.1.4.--找出需要的空位(2)：考虑座位的换排

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

5.1.1.4.1.所有座位的状态都是‘未预订’，且行编号相同

5.2.最多能坐下多少人

5.2.1.

5.2.1.1.条件1：起点到终点之间的所有座位状态都是“未预订”

5.2.1.2.条件2：起点之前的座位状态不是“未预订”

5.2.1.3.条件3：终点之后的座位状态不是“未预订”

5.2.2.--第一阶段：生成存储了所有序列的视图

CREATE VIEW Sequences (start_seat, end_seat, seat_cnt) AS SELECT S1.seat AS start_seat, S2.seat AS end_seat, S2.seat - S1.seat + 1 AS seat_cnt FROM Seats3 S1, Seats3 S2 WHERE S1.seat ＜= S2.seat --第一步：生成起点和终点的组合 AND NOT EXISTS --第二步：描述序列内所有点需要满足的条件 (SELECT ＊ FROM Seats3 S3 WHERE ( S3.seat BETWEEN S1.seat AND S2.seat AND S3.status ＜＞’未预订’) --条件1的否定 OR (S3.seat = S2.seat + 1 AND S3.status =’未预订’) --条件2的否定 OR (S3.seat = S1.seat -1 AND S3.status =’未预订’)); --条件3的否定

5.2.2.1.--第二阶段：求最长的序列

SELECT start_seat, "~", end_seat, seat_cnt FROM Sequences WHERE seat_cnt = (SELECT MAX(seat_cnt) FROM Sequences);

6.单调递增和单调递减

6.1.示例

6.1.1.

6.1.2.--生成起点和终点的组合的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;

6.1.2.1.--求单调递增的区间的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);

6.1.2.1.1.--排除掉子集，只取最长的时间区间

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;

标签：