我曾經(jīng)擔(dān)任過大學(xué)的老師。我在開始講授子查詢時，會讓學(xué)生從Northwind數(shù)據(jù)庫的employees表中，找到年齡最小的雇員。大多數(shù)的學(xué)生很輕松地給出了下面的解答。

SELECT *

FROM employees

WHERE BirthDate =

(SELECT MAX(BirthDate) FROM employees)

然而，當(dāng)我要求他們找出下一個年紀(jì)最小的雇員的時候，許多人被難住了。有幾個學(xué)生給出了下面的解答。

SELECT *

FROM employees

WHERE BirthDate=

(SELECT MAX(BirthDate)

FROM employees

WHERE BirthDate <

(SELECT MAX(BirthDate) FROM employees))

這個問題的遞歸特性是很明顯的，我記得曾經(jīng)信誓旦旦地要編寫一個可以返回任何數(shù)據(jù)層次（年齡、重量、分?jǐn)?shù)等等）的第N個記錄的存儲過程。但是，直到兩年后我在做一個有人給我出資的電子商業(yè)項目時，才最終完成了這個存儲過程的編寫。

概述

遞歸，發(fā)生在一個存儲過程或者一個函數(shù)調(diào)用它本身的時候，是一個廣為人知并且很有用的數(shù)學(xué)和編程概念。然而，它也是很危險的，例如它會導(dǎo)致無限循環(huán)。（這可能是SQLSERVER2000將嵌套調(diào)用和嵌套層數(shù)限制為32的一個原因，你可以在存儲過程運行時，使用全程變量@@NESTLEVELl來檢查該過程的嵌套層。）DBMS程序員要牢記：遞歸調(diào)用會很顯著地延長事務(wù)處理的時間，因此，在在線事務(wù)處理系統(tǒng)中通常要避免使用遞歸。

我們由一個例子開始。假設(shè)你有一個存有學(xué)生測驗成績的名為checkRank的表，你的任務(wù)是找出成績排在第4位到第10位的學(xué)生。我寫了一個名為fillCheckRank的腳本和一個名為spu_testRecursion的存儲過程來演示該技巧。該腳本以相關(guān)的隨機(jī)數(shù)據(jù)創(chuàng)建和裝載checkRank表（見附帶光盤中的CreateCheckRank.sql），但是該存儲過程（見列表1）卻復(fù)雜得多，并且使用了遞歸算法、嵌套過程調(diào)用和嵌套子查詢來計算答案。

列表1 spu_testRecursion存儲過程：

IF EXISTS (SELECT * FROM sysobjects

WHERE id = object_id('spu_testRecursion')

and OBJECTPROPERTY(id, 'IsProcedure') = 1)

DROP PROCEDURE spu_testRecursion

GO

CREATE PROC spu_testRecursion

@level int, @tblName varchar(30),

@colName varchar(30), @answer varchar(8000) OUTPUT

AS

DECLARE @one_less int

SET NOCOUNT ON

–– Parameter @level greater than 31 is disallowed.

IF (@level < 0 OR @level > 31)

BEGIN

PRINT 'Illegal Parameter Value. Must be 0 through 31'

RETURN –1

END

IF (@level = 0 OR @level = 1)

BEGIN

SELECT @answer= 'select max(' + @colName + ')

from ' + @tblName

END

ELSE

BEGIN

SELECT @one_less = @level – 1

–– recursively call itself

EXEC spu_testRecursion @one_less, @tblName,

@colName, @answer output

IF @@ERROR <> 0 RETURN(–1)

SELECT @answer = 'select max(' + @colName + ')

from ' + @tblName + ' where ' + @colName +

' < ' + Char(10) + '(' + @answer + ')'

END

IF @@NESTLEVEL = 1

BEGIN

PRINT 'NESTED LEVEL '

+ CAST(@@NESTLEVEL AS VARCHAR(20)) + CHAR(10) +

@colName + ' rank ' + CAST(@level AS VARCHAR(10))

+ CHAR(10) + @answer

EXEC (@answer)

END

RETURN(0)

GO

/* How to run the procedure

DECLARE @answer varchar(8000)

exec spu_testRecursion 10, 'checkRank',

'testPoints', @answer output

*/

注：當(dāng)你刪除和創(chuàng)建該存儲過程時，你會收到由于缺失“spu_testRecursion”無法在當(dāng)前存儲過程的sysdepend中添加行的信息。但是不必?fù)?dān)心，該存儲過程仍然可以創(chuàng)建。更多信息請見Q24641。

該存儲過程會收到這些參數(shù)

@level：在層次中的等級或者位置

@tblName:表名

@answer:返回生成的Select語句的輸出參數(shù)

并返回這兩個參數(shù)：

值，對應(yīng)于在層次結(jié)構(gòu)中所需的級別或者位置

一段您可以獲得同等結(jié)果的腳本

為得到成績?yōu)榈谒拿慕Y(jié)果，你可以這樣做：

DECLARE @answer varchar(4000)

EXEC spu_TestRecursion 4, 'checkRank', 'testPoints',

@answer output

下面是我的結(jié)果（你的結(jié)果可能不一樣，因為表里面的數(shù)據(jù)是隨機(jī)生成的）。

NESTED LEVEL 1

testPoints rank 4

select max(testPoints) from checkRank where testPoints <

(select max(testPoints) from checkRank where testPoints <

(select max(testPoints) from checkRank where testPoints <

(select max(testPoints) from checkRank)))

–––––––––––

93

這樣，第四名對應(yīng)的分?jǐn)?shù)是93分。

當(dāng)我開始執(zhí)行相同的查詢來得到第10名的分?jǐn)?shù)時，我的答案是87分。第4到第10的排名問題的最終答案也可以通過檢索來推斷，或者通過運行一個查詢來確定(見附帶光盤中的4ththru10th.sql)

實踐例子

接下來的場景對于許多電子商務(wù)交易是很常見的。假設(shè)交易雙方――買方和賣方開始交易、報價或者詢價。報價和詢價可以發(fā)給有限數(shù)目的買方（賣方），或者可以發(fā)給和被所有參加該價格交換的成員看到。

當(dāng)對詢價的第一個出價或者響應(yīng)到達(dá)時，交易開始了。從這時起,各種不同的情形都是可能的,并且每一種情形都將創(chuàng)建自己的交易鏈。報價、出價或者這個鏈中的其他部分都可以被終止、取消、拒絕或者接受。用戶可以發(fā)送一個還價、再收到一個還價等等。這個循環(huán)可以按照市場規(guī)則反復(fù)開始。對基本規(guī)則的各種背離也是允許的。例如,你可能會允許當(dāng)事人對已經(jīng)完成的交易作一些有限的變更，如，依次地接受或者拒絕，等等。

交易的實際執(zhí)行情況在細(xì)節(jié)上可能是會變化的，但是通常交易鏈的每個組成部分是以可能保存為xml文檔、java對象或者可以拆分和存儲在表格中的文檔來體現(xiàn)的。你可以使用文檔路徑來找到這些文檔在交易鏈中的順序。這與鏈接表類似，除根文件和結(jié)束文件以外，每個表（路徑）中的組成部分均有與其前和其后文件的聯(lián)系。

例如,假定有一個名為Documents的表，存有所有文檔并有一個名為docPath的列。對于docID(主鍵)=12315且docPath= 12217/12267/12299/12315的行,存在下一個洽談鏈:12217(作為根文檔或模板的已提出的報價原始資料).12267(已提出的報價項目――實際的報價).12299(出價).12315(反向文檔)

現(xiàn)在假定我要分析交易過程， 找到最終文檔和原始文檔中的價格、運費、數(shù)量和價值的差別。 如果我要分析交易失敗的可能性， 我必須用取消、終止或者拒絕的狀態(tài)來標(biāo)注文檔。 為了分析實際價值和數(shù)量，我需要以接收狀態(tài)提取協(xié)議和購貨訂單。在這兩種情況中， 最終文檔將是docPath中的最后一個文檔(在我們的例子里，12315)，但是原始文檔不是第一個文檔。在我的例子里，第一個文檔(12217)是一個只有基本報價參數(shù)的模板。 ( 只有當(dāng)我得到一個出價，我才能計算貨費、總價和其他參數(shù)。) 因此在我的例子里，第2 個文檔(12267) 是一個原始文檔。 總之，來自交易鏈的任何文檔，除了最后一個之外，都可能是原始文檔，因為每個隨后的文檔會給原始文檔添加一些新特性，而且我可能正好對那些新參數(shù)感興趣。

因此我的任務(wù)是根據(jù)一些條件選出docPath的第n個組成部分，如果你使用T-SQL函數(shù)編寫一個腳本、存儲過程或者UDF，這會是一項微不足道的工作。但是，如果你想要使用SELECT語句(你可以想像一下實時的電子商務(wù))得到"on the fly"的結(jié)果，任務(wù)變得非常復(fù)雜。

子鏈幫助程序

假設(shè)一個示例字符串'12/23/34/45/56/67/78/89/90/11/22/33/44/55/66/77/88/99/00/A/B/E/F/'。為了找到這個字符串的任何一個組成部分，我們可以使用一個簡單的算法選出定界符的兩個連續(xù)位置之間的子串:

Member (1) = substring (string, 1, pos(1) – 1);

Member (2) = substring (string, pos(1) + 1, pos(2) – pos(1) – 1); ...

Member (n) = substring (string, pos(n–1) + 1, pos(n) – pos(n–1) – 1),

T_SQL的方法如下:

Member (1) = SUBSTRING (string,1,CHARINDEX('/', string,1)–1)

Member (2) = SUBSTRING (string, CHARINDEX('/', string,1)+1,CHARINDEX('/', string, CHARINDEX('/', string,1)+1)–CHARINDEX('/', string,1)–1) And so on.

spu_indDocID存儲過程(在下載文件中可以得到)生成允許我們選取這個字符串第1至第31個任意一個組成部分的腳本。該過程執(zhí)行了我先前概略介紹過的算法并且使用了這些參數(shù)：

@str—The name of the string, usually variable or column name.

@level—This is actually a member's number or depth of recursion call.

@prevPos and @pos—I use these output parameters to save positions of delimiters and use them in the next procedure call.

@answer—One more output parameter to accumulate the result. 例子

為了察看交易鏈的例子, 運行FindSource.sql腳本來看一下交易鏈的例子。腳本的第一部分創(chuàng)建了一個名為“documents“的表, 然后在其中載入了示例數(shù)據(jù)。這些是這種情形的潛在規(guī)則：

假如在docPath中的第一個（最左邊）的文檔的docTypeID是1，那么在docPath中的第一個文檔是文檔源。

假如第一個文檔的docTypeID是2，那么文檔源是docPath中的第二個文檔

假如第一個文檔的docTypeID是3，那么文檔源是docPath中的第三個文檔

因此利用存儲過程sup_findDocID，它能夠為docPath中的第一、第二和第三個文檔產(chǎn)生相關(guān)的腳本

DECLARE @answer varchar(8000), @prevPos varchar(3000),

@pos varchar(3000)

EXEC spu_findDocID 'docPath', 1, @prevPos output,

@pos output, @answer output

EXEC spu_findDocID 'docPath', 2, @prevPos output,

@pos output, @answer output

EXEC spu_findDocID 'docPath', 3, @prevPos output,

@pos output, @answer output

最后，使用該腳本來看所有未成功交易的原始資料：

SELECT

failed.docID [failedDoc],

failed.docParh,

frst.docID [firstDoc],

frst.docTypeID [first docType],

CASE

WHEN frst.docTypeID = 1 THEN /*COPY GENERATED SCRipT

FOR FIRST MEMBER OF docPath HERE*/

WHEN frst.docTypeID = 2 THEN /*COPY GENERATED SCRIPT

FOR SECOND MEMBER OF docPath HERE*/

WHEN frst.docTypeID = 3 THEN /*COPY GENERATED SCRIPT

FOR THIRD MEMBER OF docPath*/

END sourceDoc

FROM

(SELECT docID, docParh

FROM documents WHERE docTypeID IN(7, 8)) failed

INNER JOIN

(SELECT docID, docTypeID FROM documents) frst

ON frst.docID = SUBSTRING(docParh,1,

CHARINDEX('/', docParh,1)–1)

以下是使用我的數(shù)據(jù)的查詢結(jié)果：

failedDoc docParh firstDoc first docType sourceDoc

10 1/5/7/10/ 1 1 1

11 3/7/9/11/ 3 3 9

12 2/6/8/12/ 2 2 6