读SQL进阶教程笔记08_处理数列

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的数

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

复制代码

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

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

复制代码

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

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

复制代码

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

1.    SELECT seq
2.      FROM Sequence
3.     WHERE seq BETWEEN 1 AND 100
4.     ORDER BY seq;

复制代码

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

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

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

4. 求全部的缺失编号

4.1. 示例

4.1.1. --EXCEPT版

1.    SELECT seq
2.      FROM Sequence
3.     WHERE seq BETWEEN 1 AND 12
4.    EXCEPT
5.    SELECT seq FROM SeqTbl;

复制代码

4.1.1.1. --NOT IN版

1.    SELECT seq
2.      FROM Sequence
3.     WHERE seq BETWEEN 1 AND 12
4.      AND seq NOT IN (SELECT seq FROM SeqTbl);

复制代码

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

1.    SELECT seq
2.      FROM Sequence
3.     WHERE seq BETWEEN (SELECT MIN(seq) FROM SeqTbl)
4.                  AND (SELECT MAX(seq) FROM SeqTbl)
5.    EXCEPT
6.    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)：不考虑座位的换排

1.    SELECT S1.seat   AS start_seat, '~', S2.seat AS end_seat
2.      FROM Seats S1, Seats S2
3.     WHERE S2.seat = S1.seat + (:head_cnt -1)  --决定起点和终点
4.      AND NOT EXISTS
5.              (SELECT ＊
6.                FROM Seats S3
7.                WHERE S3.seat BETWEEN S1.seat AND S2.seat
8.                  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)：考虑座位的换排

1.    SELECT S1.seat   AS start_seat, '~', S2.seat AS end_seat
2.      FROM Seats2 S1, Seats2 S2
3.     WHERE S2.seat = S1.seat + (:head_cnt -1)  --决定起点和终点
4.      AND NOT EXISTS
5.              (SELECT ＊
6.                FROM Seats2 S3
7.                WHERE S3.seat BETWEEN S1.seat AND S2.seat
8.                  AND (    S3.status ＜＞’未预订’
9.                        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. --第一阶段：生成存储了所有序列的视图

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

复制代码

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

1.    SELECT start_seat, '~', end_seat, seat_cnt
2.      FROM Sequences
3.     WHERE seat_cnt = (SELECT MAX(seat_cnt) FROM Sequences);

复制代码

6. 单调递增和单调递减

6.1. 示例

6.1.1.


6.1.2. --生成起点和终点的组合的SQL语句

1.    SELECT S1.deal_date  AS start_date,
2.          S2.deal_date  AS end_date
3.      FROM MyStock S1, MyStock S2
4.     WHERE S1.deal_date ＜ S2.deal_date;

复制代码

6.1.2.1. --求单调递增的区间的SQL语句：子集也输出

1.    SELECT S1.deal_date   AS start_date,
2.          S2.deal_date   AS end_date
3.      FROM MyStock S1, MyStock S2
4.     WHERE S1.deal_date ＜ S2.deal_date  --第一步：生成起点和终点的组合
5.      AND  NOT EXISTS
6.              ( SELECT ＊  --第二步：描述区间内所有日期需要满足的条件
7.                  FROM MyStock S3, MyStock S4
8.                  WHERE S3.deal_date BETWEEN S1.deal_date AND S2.deal_date
9.                  AND S4.deal_date BETWEEN S1.deal_date AND S2.deal_date
10.                    AND S3.deal_date ＜ S4.deal_date
11.                    AND S3.price ＞= S4.price);

复制代码

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

1.    SELECT MIN(start_date) AS start_date,      --最大限度地向前延伸起点
2.          end_date
3.      FROM  (SELECT S1.deal_date AS start_date,
4.                    MAX(S2.deal_date) AS end_date  --最大限度地向后延伸终点
5.              FROM MyStock S1, MyStock S2
6.              WHERE S1.deal_date ＜ S2.deal_date
7.                AND NOT EXISTS
8.                (SELECT ＊
9.                    FROM MyStock S3, MyStock S4
10.                  WHERE S3.deal_date BETWEEN S1.deal_date AND S2.deal_date
11.                    AND S4.deal_date BETWEEN S1.deal_date AND S2.deal_date
12.                    AND S3.deal_date ＜ S4.deal_date
13.                    AND S3.price ＞= S4.price)
14.            GROUP BY S1.deal_date) TMP
15.    GROUP BY end_date;

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！