SQL Story摘錄（九）————不等聯(lián)接

2024-07-21 02:09:05

字體：大中小

供稿：網(wǎng)友

不等聯(lián)接

通常來說，sql語言進行的都是無序操作。想要進行有序的處理，比如比較一個序列的前后項，必須要使用游標。但是，在有些場合下，可采用另一種方法，不用游標，一樣能處理有序的信息，這就是不等聯(lián)接。先看下面一個例子

前一陣， csdn網(wǎng)友buildit來信，和我討論了這樣的問題：以下表history

create table [history] (

[thedate] [datetime] null ,

[quantity] [int] null

) on [primary]

中存儲的是一系列的歷史數(shù)據(jù)，例如：

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

現(xiàn)在，我們要查詢自起始日期至每一個日期的總量。也就是說，顯示這樣一個結(jié)果集：

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上建一個游標，從第一條開始，每一條，加一次。那換個想法呢？如果我們建立這樣一個結(jié)果集，讓每一個日期限，都對應它當天的數(shù)量及所有在它之前的數(shù)量記錄。那么我們就可以按這個日期分組，對數(shù)量進行求和。很顯然，一個不等查詢就這樣形成了。我最初的寫法有錯誤，以下是經(jīng) 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

不等聯(lián)接本身就不是一一對應，它的對應關(guān)系和順序有著密切的關(guān)系。這也就是我們能夠拿它來進行有序操作的原因。再給一個很自然的例子：

select l.i, sum(r.i)

from n l

join n r

on l.i >=r.i

group by l.i

表n只有一個整型列i，保存自然數(shù)列。所以，沒什么神秘，這就是求自然數(shù)列的和。這里sum(r.i)表示自然數(shù)列n從零至i的累加和，比前面的那個問題還要簡單。不過顯然這不是不等聯(lián)接發(fā)揮威力的地方，因為它會形成一個巨大的三角形數(shù)據(jù)集，就像下面這樣

1 1

2 1

2 2

3 1

3 2

3 3

...

當我對十六位整型的列表執(zhí)行這個查詢時，我的athlonxp1700+/256mddr的機器足足運行了近三十分鐘，當我寫下現(xiàn)在這段文字時，它返回了一個數(shù)據(jù)溢出錯誤。顯然，對于這個查詢，即使是十六位整數(shù)的列表，也太大了。我的建議是，僅當結(jié)果集無法用公式表達時，使用不等聯(lián)接。像這個累加，我們早已有了成熟的公式，何必再讓計算機傻算呢？用下面這個語句

select i, ((1+i)*i)/2

from n

相比老老實實地累加，速度奇快。發(fā)現(xiàn)數(shù)據(jù)溢出時，連一秒鐘都不到，可這臺計算機就是想不到用這個方法，唉……

傳說數(shù)學界一代宗師高斯小學的時候，他老師考過他這個問題。所以幾乎所有的中國小學生，都被老師用這道題折磨過。好像老師們的目的就在于告訴我們，我們的智商比不上高斯。可我壓根就沒想和人家比啊……

上大學時，教我們第一本《數(shù)學分析》的范先令老師說計算機是傻子，我當時只是覺得好玩而已，今天算是見識了，看來在歸納總結(jié)的能力方面，計算機也就是我小學時的水平，永遠也趕不上高斯上小學那會兒了。

不過，這種東西用于公式難以表達的地方，還是有意義的。比如我的一個朋友用不等聯(lián)接寫過一個素數(shù)篩子，很有趣。它雖說不會比我們用過程化的代碼寫出來的程序效率更高，但卻能把篩法根本的精要表達的清清楚楚，也許以后我們研究數(shù)論，會用的上這種sql風格的表示法呢。這位朋友教了我很多計算機方面的知識，出于對他的尊重，我不會抄錄他的代碼。不過這個語句本身并不復雜，相信朋友們想到用聯(lián)接查詢后，都一定寫得出來，大家有興趣的話，自己不妨試試。用它還可以實現(xiàn)其它的一些數(shù)列，以后我們再討論幾個。

不等聯(lián)接還有一個用法，可以用它生成一個序號列，比如

select count(l.afield) as id,

l.afield

from mytable l

jion mytable r

on l.afield > r.afield

group by l.afield

afield字段可以是字符串、日期，當然也可以是數(shù)值，反正可排序就行。這東西有點奇技淫巧的味道，數(shù)據(jù)量太大，就不好玩了，一般還是用物理行號的好，雖說不是sql標準，但實用啊。這個例子我在mcdba的復習題中見過（據(jù)說這道題考過），不過我的那位朋友自己就做出來過，大家可能也有獨立實現(xiàn)過這一方法的吧。

不等查詢的有序操作能力，顯然來自聯(lián)接字段的可排序和互異性，所以，最好不要在有重復值的字段上做不等聯(lián)接（事實上，最好不要在有重復值的字段上做任何聯(lián)接，除非你非常肯定你在干什么）。等值聯(lián)接出現(xiàn)數(shù)據(jù)爆炸就夠可怕的了，不等聯(lián)接要是玩爆了……嘿嘿嘿……

想像一個等值聯(lián)接中有一對重復值，可能出現(xiàn)兩對重復結(jié)果。不過要是不等聯(lián)接，就和重復的位置有關(guān)了。因為這是一個三角形，所以出現(xiàn)在最上面還好，要是出現(xiàn)在這個三角形的下部……

不等聯(lián)接查詢，顯然是一個有力的工具，但它也是招來麻煩的快捷方式之一。有幾個建議，是我的經(jīng)驗：
如果聯(lián)接會生成很大的“三角形”，就不要用，試試子查詢或哪怕游標；

生成的結(jié)果集相對于原表越小越好，盡可能地把無用的數(shù)據(jù)先過濾掉；

用不等聯(lián)接進行數(shù)列計算會表達的很清楚（因為是非過程化的），但通常在效率上它沒有什么優(yōu)勢，所以，平時玩玩可以，真用的話最好先考慮好；

還有就是不等聯(lián)接不要輕易用在多重聯(lián)接中，否則可能會引起杠桿作用。

祝大家在這個神奇的世界中旅行愉快！

上一篇：SQL猜想

下一篇：學習中的思考