SQL Story摘录（九）————不等联接

 
不等联接

通常来说，SQL语言进行的都是无序操作。想要进行有序的处理，比如比较一个序列的前后项，必须要使用游标。但是，在有些场合下，可采用另一种方法，不用游标，一样能处理有序的信息，这就是不等联接。先看下面一个例子

前一阵， CSDN网友BuildIt来信，和我讨论了这样的问题：以下表HISTORY

CREATE TABLE [HISTORY] (

[TheDate] [datetime] NULL ,

[Quantity] [int] NULL

) ON [PRIMARY]

中存储的是一系列的历史数据，例如：

INSERT HISTORY VALUES(@#2002-01-01 00:00:00.0@#,11)

GO

INSERT HISTORY VALUES(@#2002-01-02 00:00:00.0@#,34)

GO

INSERT HISTORY VALUES(@#2002-01-03 00:00:00.0@#,27)

GO

INSERT HISTORY VALUES(@#2002-01-04 00:00:00.0@#,43)

GO

现在，我们要查询自起始日期至每一个日期的总量。也就是说，显示这样一个结果集：

TheDate Quantity q_sum

2002-01-01 00:00:00.0 11 11

2002-01-02 00:00:00.0 34 45

2002-01-03 00:00:00.0 27 72

2002-01-04 00:00:00.0 43 115

直观上来讲，我们可以在SELECT * FROM HISTORY ORDER BY TheDate上建一个游标，从第一条开始，每一条，加一次。那换个想法呢？如果我们建立这样一个结果集，让每一个日期限，都对应它当天的数量及所有在它之前的数量记录。那么我们就可以按这个日期分组，对数量进行求和。很显然，一个不等查询就这样形成了。我最初的写法有错误，以下是经 BuildIt 修改后最终的语句

select l.TheDate,

l.quantity,

sum(r.quantity) as q_sum

from HISTORY l

join HISTORY r

on l.TheDate >= r.TheDate

group by l.TheDate, l.quantity

order by l.TheDate

不等联接本身就不是一一对应，它的对应关系和顺序有着密切的关系。这也就是我们能够拿它来进行有序操作的原因。再给一个很自然的例子：

SELECT L.I, SUM(R.I)

FROM N L

JOIN N R

ON L.I >=R.I

GROUP BY L.I

表N只有一个整型列I，保存自然数列。所以，没什么神秘，这就是求自然数列的和。这里SUM(R.I)表示自然数列N从零至I的累加和，比前面的那个问题还要简单。不过显然这不是不等联接发挥威力的地方，因为它会形成一个巨大的三角形数据集，就像下面这样

1 1

2 1

2 2

3 1

3 2

3 3

...

当我对十六位整型的列表执行这个查询时，我的AthlonXP1700+/256MDDR的机器足足运行了近三十分钟，当我写下现在这段文字时，它返回了一个数据溢出错误。显然，对于这个查询，即使是十六位整数的列表，也太大了。我的建议是，仅当结果集无法用公式表达时，使用不等联接。像这个累加，我们早已有了成熟的公式，何必再让计算机傻算呢？用下面这个语句

SELECT I, ((1+I)*I)/2

FROM N

相比老老实实地累加，速度奇快。发现数据溢出时，连一秒钟都不到，可这台计算机就是想不到用这个方法，唉……

传说数学界一代宗师高斯小学的时候，他老师考过他这个问题。所以几乎所有的中国小学生，都被老师用这道题折磨过。好像老师们的目的就在于告诉我们，我们的智商比不上高斯。可我压根就没想和人家比啊……

上大学时，教我们第一本《数学分析》的范先令老师说计算机是傻子，我当时只是觉得好玩而已，今天算是见识了，看来在归纳总结的能力方面，计算机也就是我小学时的水平，永远也赶不上高斯上小学那会儿了。

不过，这种东西用于公式难以表达的地方，还是有意义的。比如我的一个朋友用不等联接写过一个素数筛子，很有趣。它虽说不会比我们用过程化的代码写出来的程序效率更高，但却能把筛法根本的精要表达的清清楚楚，也许以后我们研究数论，会用的上这种SQL风格的表示法呢。这位朋友教了我很多计算机方面的知识，出于对他的尊重，我不会抄录他的代码。不过这个语句本身并不复杂，相信朋友们想到用联接查询后，都一定写得出来，大家有兴趣的话，自己不妨试试。用它还可以实现其它的一些数列，以后我们再讨论几个。

不等联接还有一个用法，可以用它生成一个序号列，比如

SELECT COUNT(L.AFIELD) AS ID,

L.AFIELD

FROM MYTABLE L

JION MYTABLE R

ON L.AFIELD > R.AFIELD

GROUP BY L.AFIELD

AFIELD字段可以是字符串、日期，当然也可以是数值，反正可排序就行。这东西有点奇技淫巧的味道，数据量太大，就不好玩了，一般还是用物理行号的好，虽说不是SQL标准，但实用啊。这个例子我在MCDBA的复习题中见过（据说这道题考过），不过我的那位朋友自己就做出来过，大家可能也有独立实现过这一方法的吧。

不等查询的有序操作能力，显然来自联接字段的可排序和互异性，所以，最好不要在有重复值的字段上做不等联接（事实上，最好不要在有重复值的字段上做任何联接，除非你非常肯定你在干什么）。等值联接出现数据爆炸就够可怕的了，不等联接要是玩爆了……嘿嘿嘿……

想像一个等值联接中有一对重复值，可能出现两对重复结果。不过要是不等联接，就和重复的位置有关了。因为这是一个三角形，所以出现在最上面还好，要是出现在这个三角形的下部……

不等联接查询，显然是一个有力的工具，但它也是招来麻烦的快捷方式之一。有几个建议，是我的经验：
如果联接会生成很大的“三角形”，就不要用，试试子查询或哪怕游标；

生成的结果集相对于原表越小越好，尽可能地把无用的数据先过滤掉；

用不等联接进行数列计算会表达的很清楚（因为是非过程化的），但通常在效率上它没有什么优势，所以，平时玩玩可以，真用的话最好先考虑好；

还有就是不等联接不要轻易用在多重联接中，否则可能会引起杠杆作用。

祝大家在这个神奇的世界中旅行愉快！

原文转自：http://www.ltesting.net