1. 處理有序集合也並非SQL的直接用途 1.1. SQL語言在處理數據時預設地都不考慮順序 2. 處理數據的方法有兩種 2.1. 第一種是把數據看成忽略了順序的集合 2.2. 第二種是把數據看成有序的集合 2.2.1. 首先用自連接生成起點和終點的組合 2.2.2. 其次在子查詢中描述內部的各個元 ...
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;
