SQLStory摘录(九)————不等联接

发表于:2007-05-25来源:作者:点击数: 标签:
不等联接 通常来说, SQL 语言进行的都是无序操作。想要进行有序的处理,比如比较一个序列的前后项,必须要使用游标。但是,在有些场合下,可采用另一种方法,不用游标,一样能处理有序的信息,这就是不等联接。先看下面一个例子 前一阵, CSDN网友BuildIt来
 

不等联接

通常来说,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