《sql进阶教程》之用 SQL 处理数列

本文是《sql进阶教程》阅读笔记，感兴趣可以阅读该书对应章节，这本适合有一定sql基础的同学阅读。另外作者《sql基础教程》也值得一看

生成连续编号

在思考这道例题之前，请先思考下面一个问题：

00 ～ 99 的 100 个数中，0, 1, 2,…, 9 这 10 个数字分别出现了多少次？

如果把数看成字符串，其实它就是由各个数位上的数字组成的集合

Digits

digit( 数字 )
0
1
2
3
4
5
6
7
8
9

通过对两个 Digits 集合求笛卡儿积而得出 0 ～ 99的数字。

create table Digits (
	digit INTEGER
);

INSERT INTO Digits(digit)VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

-- 求连续编号(1)：求0~99 的数
SELECT D1.digit + (D2.digit * 10) AS seq
FROM Digits D1 CROSS JOIN Digits D2
ORDER BY seq;

通过追加 D3、D4 等集合，不论多少位的数都可以生成。而且，如果只想生成从 1 开始，或者到 542 结束的数，只需在 WHERE子句中加入过滤条件就可以了。

-- 求连续编号(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;

通过将这个查询的结果存储在视图里，就可以在需要连续编号时通过简单的 SELECT 来获取需要的编号

-- 生成序列视图（包含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;
-- 从序列视图中获取1~100
SELECT seq
FROM Sequence
WHERE seq BETWEEN 1 AND 100
ORDER BY seq;

---

求全部的缺失编号

查找连续编号中的缺失编号的方法。作为示例，假设存在下面这样一张编号有缺失的表。
Seqtbl

seq（连续编号）
1
2
4
5
6
7
8
10
11
12

缺失的编号 3、9、10

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

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

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

--像下面这么做性能会有所下降，但是通过扩
--展 BETWEEN 谓词的参数，可以动态地指定目标表的最大值和最
SELECT seq
FROM Sequence
WHERE seq BETWEEN (SELECT MIN(seq) FROM SeqTbl)
AND (SELECT MAX(seq) FROM SeqTbl)
EXCEPT
SELECT seq FROM SeqTbl;

这种写法在查询上限和下限未必固定的表时非常方便。两个自查询没有相关性，
而且只会执行一次。如果在“seq”列上建立索引，那么极值函数的运行可以变得更快速。

三个人能坐得下吗

预约火车票或机票时考虑连坐问题
Seats

seat ( 座位 )	status ( 状态 )
1	已预订
2	已预订
3	未预订
4	未预订
5	未预订
6	已预订
7	未预订
8	未预订
9	未预订
10	未预订
11	未预订
12	已预订
13	已预订
14	未预订
15	未预订

要求：找出连续 3 个空位的全部组合

把由连续的整数构成的集合，也就是连续编号的集合称为“序列”。这样序列中就不能出现缺失编号。

• 3～5
• 7 ～9
• 8 ～ 10
• 9 ～ 11

(7, 8, 9, 10, 11) 这个序列中，包含 3 个子序列 (7, 8, 9)、(8, 9, 10)、(9,10, 11)，我们也把它们当成不同的序列。还有，通常火车的一排只有几个座位，所以可能我们表里的座位会分布在几排里，但我们暂时忽略掉这个问题，假设所有的座位排成了一条直线

借助上面的图表我们可以知道，需要满足的条件是，以 n 为起点、 n+(3-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 <> '未预订'
);

注：“:head_cnt ”是表示需要的空位个数的参数

接下来看一下这道例题的升级版，即发生换排的情况。假设这列火车每一排有 5 个座位。我们在表中加上表示行编号row_id列。

Seats2

seat ( 座位 )	row_id( 行编号 ID)	status ( 状态 )
1	A	已预订
2	A	已预订
3	A	未预订
4	A	未预订
5	A	未预订
6	B	已预订
7	B	未预订
8	B	未预订
9	B	未预订
10	B	未预订
11	C	未预订
12	C	已预订
13	C	已预订
14	C	未预订
15	C	未预订

-- 找出需要的空位(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));

---

最多能坐下多少人

按现在的空位状况，最多能坐下多少人”。换句话说，要求的是最长的序列

Seats3

seat ( 座位 )	status ( 状态 )
1	已预订
2	未预订
3	未预订
4	未预订
5	未预订
6	已预订
7	未预订
8	已预订
9	未预订
10	未预订

长度为 4 的序列“2 ～ 5”就是我们的答案

条件 1：起点到终点之间的所有座位状态都是“未预订”。
条件 2：起点之前的座位状态不是“未预订”。
条件 3：终点之后的座位状态不是“未预订”

-- 第一阶段：生成存储了所有序列的视图
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 的否定

---

单调递增和单调递减

假设存在下面这样一张反映了某公司股价动态的表
MyStock

deal_date( 交易日期 )	price( 股价 )
2007-01-06	1000
2007-01-08	1050
2007-01-09	1050
2007-01-12	900
2007-01-13	880
2007-01-14	870
2007-01-16	920
2007-01-17	1000

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

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