用 SQL 处理数列

       

       SQL 语言在处理数据时默认地都不考虑顺序。因此，如果遇到了需要考虑数据顺序的情况，处理方法与面向过程语言及文件系统的处理方法很不一样。关系模型的数据结构里，并没有“顺序”这一概念。因此，基于它实现的关系数据库中的表和视图的行和列也必然没有顺序。同样地，处理有序集合也并非 SQL 的直接用途。 因此，SQL 处理有序集合的方法，与原本就以处理顺序为目的的面向过程语言及文件系统的处理方法在性质上是不同的。尽管性质不同，但其背后都有坚实的理论基础。用一句话概括就是，集合和谓词 。

1、生成连续编号

       我们来思考一下如何使用 SQL 生成连续编号。目前很多数据库实现都包含了序列对象（sequence object），如果要按照顺序一个一个地获取连续编号，可以使用这个方法。但是，如何只用一条 SQL 就能生成任意长的连续编号序列呢？例如生成 0 ～ 99 这 100 个连续编号。有一些依赖数据库实现的方法，比如 CONNECT BY （Oracle）、WITH 子句（DB2、SQL Server），但是这里要求必须使用不依赖数据库实现的方法来实现。

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

谜题：00 ～ 99 的 100 个数中，0, 1, 2,…, 9 这 10 个数字分别出现了 多少次？（对于只有一位的数字，我们在前面加上 0，比如 01、07）

       算出来了吗？正确答案是，每个数字都出现了 20 次。例如，我们数一下出现在十位和个位上的数字1 一共有多少个。我们会发现，十位上的数字 1 有 10 个，个位上的数字 1 也有 10 个。11 的十位和个位都是 1，但是 11 本来就包括两个 1，所以数字 1 并没有被重复计数。

通过这个谜题想让大家明白的是，如果把数看成字符串，其实它就是由各个数位上的数字组成的集合。谜题我们就分析到这里。

       接下来回到正题。首先我们生成一张存储了各个数位上数字的表“数 字表”。这张表只有 10 行，我们只用来读取数据。我们都知道，无论 多大的数，都可以由这张表中的 10 个数字组合而成。 

这样，我们就可以通过对两个 Digits 集合求笛卡儿积而得出 0 ～ 99 的数字。
 

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

执行结果：

这段代码中，D1 代表个位数字的集合，D2 代表十位数字的集合。交叉连接可以得到两个集合中元素的“所有可能的组合”，像下面这样。 

同样地，通过追加 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;

       也许大家已经注意到了，这种生成连续编号的方法，完全忽略了数的“顺序”这一属性。将这个解法和本书多次介绍过的冯·诺依曼型有序数的定义进行比较，可以很容易发现它们的区别  。冯·诺依曼的方法使用递归集合定义自然数，先定义 0 然后得到 1，定义 1 然后得到 2，是有先后顺序的（因此这种方法适用于解决位次、累计值等与顺序相关的问题）。

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

通过将这个查询的结果存储在视图里，就可以在需要连续编号时通过简单的 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;

这个视图可以用于多种目的，非常方便，因此事先生成一个后，它就 可以在很多场景发挥作用。

摘自《SQL进阶教程》--生成连续编号。