SQL进阶之处理数列

处理数列

越前须知（雾）

• 本系列参考《SQL进阶教程》¹，DBMS选用MySQL。
• 本系列不涉及数据库安装与基础语句，对初学者存在一定门槛；基础知识建议阅读《SQL必知必会（第四版）》与《SQL基础教程（第二版）》。

概述

关系模型的数据结构中，没有“顺序”这一概念，因此SQL处理有序集合的方法，与过程语言（以处理顺序为目的）不同。

具体用法

连续编号

• 创表

-- 连续编号 & 查询质数示例1
REATE TABLE Digits
 (digit INTEGER PRIMARY KEY); 
​
INSERT INTO Digits VALUES (0);
INSERT INTO Digits VALUES (1);
INSERT INTO Digits VALUES (2);
INSERT INTO Digits VALUES (3);
INSERT INTO Digits VALUES (4);
INSERT INTO Digits VALUES (5);
INSERT INTO Digits VALUES (6);
INSERT INTO Digits VALUES (7);
INSERT INTO Digits VALUES (8);
INSERT INTO Digits VALUES (9);

-- 查询缺失编号示例2
CREATE TABLE SeqTbl
 (seq INTEGER PRIMARY KEY); 
​
INSERT INTO SeqTbl VALUES (1);
INSERT INTO SeqTbl VALUES (2);
INSERT INTO SeqTbl VALUES (4);
INSERT INTO SeqTbl VALUES (5);
INSERT INTO SeqTbl VALUES (6);
INSERT INTO SeqTbl VALUES (7);
INSERT INTO SeqTbl VALUES (8);
INSERT INTO SeqTbl VALUES (11);
INSERT INTO SeqTbl VALUES (12);

Q：生成1-100、1-542的连续编号
A：把连续数表当成存储在各个数位上的“数字表”：先创建0-9的基础数表，再CROSS JOIN

-- 两位数
select D1.digit + (D2.digit * 10) as seq
    from Digits D1 cross join Digits D2
    order by seq;

-- 三位数，且限定编号
-- 因为原则上where的执行顺序比select早，所以不可以用 where seq between 1 and 542；
-- 但MySQL会先扫描select子句内容，MySQL可直接用seq
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;

Q：寻找0-99中的质数
A：生成0-99编号的视图 + 质数条件

-- 生成 0-99 编号的视图
create view Numbers(seq) as
    select D1.digit + (D2.digit * 10)
    from Digits D1 cross join Digits D2;

-- NOT EXISTS + 否定 = 双重否定 <=> 肯定
select seq as prime
    from Numbers Dividend
    where seq > 1
    and not exists (select * from Numbers Divisor
                        where Divisor.seq <= Dividend.seq / 2  
                    -- 除了自身之外的约数必定小于等于自身值的一半（条件1）
                        and Divisor.seq <> 1   
                    -- 约数中不包含1（条件2）
                        and mod(Dividend.seq, Divisor.seq) = 0)
                    -- 在条件1&2的基础上，质数无法除尽，这里用“否定 + NOT EXISTS”表示肯定
    order by prime;

Q：查询表中缺失编号
A：用完整连续编号列表减现有表

-- 先创建连续编号视图
drop view Numbers;
create view Numbers(num) as 
  select D1.digit + (D2.digit * 10) + (D3.digit * 100)
    from Digits D1 cross join Digits D2 cross join Digits D3