在T—SQL中實(shí)現(xiàn)字符串類型的聚合統(tǒng)計(jì)查詢的一種方法

羅瑞明

摘要：在SQL Server數(shù)據(jù)庫中，沒有對(duì)字符串?dāng)?shù)據(jù)類型進(jìn)行聚合統(tǒng)計(jì)的函數(shù)，但是實(shí)際應(yīng)用中確實(shí)會(huì)有這方面的需求。文章通過對(duì)一條常用T-SQL查詢語句的分析，進(jìn)行優(yōu)化，并編寫自定義函數(shù)，實(shí)現(xiàn)了對(duì)字符串?dāng)?shù)據(jù)類型進(jìn)行聚合統(tǒng)計(jì)的功能，提供具有實(shí)際應(yīng)用價(jià)值的參考。

關(guān)鍵詞：SQL Server；字符串；聚合統(tǒng)計(jì)；查詢

中圖分類號(hào)：TP393 文獻(xiàn)標(biāo)識(shí)碼：A 文章編號(hào)：1009-3044（2017）02-0008-02

我們知道，在SQL中進(jìn)行統(tǒng)計(jì)查詢可以通過聚合函數(shù)來實(shí)現(xiàn)，常用的聚合函數(shù)有：

l AVG（expression）函數(shù)----按列計(jì)算平均值

l SUM（expression）函數(shù)----按列計(jì)算值的總和

l MAX（expression）函數(shù)----求一列中的最大值

l MIN（expression）函數(shù)----求一列中的最小值

l COUNT（expression）函數(shù)----按列值統(tǒng)計(jì)個(gè)數(shù)

但是，我們來看這樣一個(gè)問題，在如下圖1所示的一個(gè)產(chǎn)品表中，需要按照產(chǎn)品類別分類，將所有同類別的商品名稱合并在一起，中間用逗號(hào)“，”間隔開顯示。期望查詢的結(jié)果如圖2所示：

由此我們想到，需要用SQL查詢的聚合函數(shù)來進(jìn)行分組統(tǒng)計(jì)，分組的字段應(yīng)該是產(chǎn)品類別，即Group By CategroyId， 而需要進(jìn)行聚合統(tǒng)計(jì)的字段是ProductName。但是ProductName字段是字符串?dāng)?shù)據(jù)類型，在SQL Server 2012之前的版本中都沒有對(duì)字符串進(jìn)行連接合并的聚合函數(shù)。那么，如何實(shí)現(xiàn)對(duì)字符串的連接合并呢？ 一開始，我們可能會(huì)想到使用游標(biāo)來操作，因?yàn)橹挥惺褂糜螛?biāo)才能方便定位在每一條記錄上，將ProductName字段值提取出來，再依次進(jìn)行字符串連接操作，以得到期望的結(jié)果。這個(gè)過程比較麻煩，能不能有更簡便有效的方法呢？

我們知道，使用T-SQL從表中讀取字段值到變量時(shí)，可以這樣：

Select @變量 = 字段 From 表 Where 條件

代碼如下：

Declare @name varchar（20）

Select @name = name From tb_student Where stuNo = '200200001'

Print @name

結(jié)果顯示：黃清

通常，我們必須保證這條給變量賦值的Select查詢語句，只能查找到一條記錄，這樣就剛好將該記錄的name字段值賦值給@name變量；但是，如果Select查詢的結(jié)果是多條記錄呢？這樣意味著要將多個(gè)字段的值賦給1個(gè)變量，會(huì)不會(huì)導(dǎo)致程序出錯(cuò)呢？如以下代碼：

Declare @name varchar（20）

Select @name = name From tb_student Where stuNo Like '2002%'

Print @name

實(shí)踐證明，程序不會(huì)出錯(cuò)，但是@name變量的值也不可能是多個(gè)值，僅僅是得到結(jié)果集中最后一條記錄的name字段值。

結(jié)果顯示：徐世亮

原來經(jīng)過是這樣的，其實(shí)在select查詢語句查詢的時(shí)候，確實(shí)查詢出了多條結(jié)果記錄，而結(jié)果集中每查出一條記錄，都會(huì)向@name變量賦值一次，后面的值覆蓋了前面的值，導(dǎo)致@name變量的最終的結(jié)果為最后一條記錄的name字段值。 不信的話，我們可以使用另一段程序來驗(yàn)證該過程。

Declare @name varchar（200）

set @name = ''

Select @name = @name + name From tb_student Where stuNo Like '2002%'

Print @name

這一次，我們用累加的方式，將select每次查詢出的字段值依次連接在@name變量的后面。

結(jié)果顯示：黃清張楠田淑芳李強(qiáng)王蘭徐世亮

從這個(gè)結(jié)果可以看出， Select查詢語句每查出一條記錄都將字段值賦值給了@name變量，通過@name = @name + name不斷累加，所有字段值都連接在了一起。

由此，我們想到，如果我們需要將結(jié)果連接在一起，是不是可以采用此“累加”的方法，這樣可以省去使用游標(biāo)來操作的麻煩。

下面，我們就利用此功能，將字符串類型的字段進(jìn)行分組統(tǒng)計(jì)合并。

首先，我們設(shè)計(jì)一個(gè)自定義聚合函數(shù)，用于將相同CategoryId的ProductName字符串進(jìn)行連接合并。

CREATE FUNCTION mergeName（@cateGoryid int）

RETURNS NVARCHAR（255）

AS

BEGIN

DECLARE @r NVARCHAR（255）

SET @r = ''

SELECT @r = @r + '，' + productName FROM Product

WHERE CategoryId = @CategoryId

RETURN STUFF（@r， 1， 1， ''）

END

其中：

SELECT @r = @r + '，' + productName FROM Product WHERE CategoryId = @CategoryId的作用就是將某CategoryId的所有productName連接合并，然后使用系統(tǒng)自帶函數(shù)STUFF（）將合并后的字符串的首字符“，”逗號(hào)去除掉（因?yàn)橐婚_始@r=，連接后，勢必會(huì)在首字符位置多出一個(gè)逗號(hào)）。

然后，我們在進(jìn)行聚合統(tǒng)計(jì)查詢時(shí)，就可以使用該函數(shù)進(jìn)行字符串連接操作了：

SELECT CategoryId ，dbo.mergeName（CategoryId） AS productNames

FROM product GROUP BY CategoryId

結(jié)果如圖4所示：

結(jié)論：

大功告成，總結(jié)一下實(shí)現(xiàn)的原理和過程：

首先，利用Select @變量 = @變量 + 字段 From 表 Where 條件 的查詢，可以將查詢到的結(jié)果集中的字符串字段值連接起來，將該過程定義成自定義聚合函數(shù)。

然后，在分組統(tǒng)計(jì)查詢時(shí)，使用該聚合函數(shù)將每一組的字符串字段值進(jìn)行連接操作。

如果要使字符串連接的結(jié)果更易于閱讀，可以在連接字符串時(shí)加上分隔符，并將最后結(jié)果進(jìn)行去除多余字符的處理，如使用STUFF（）等函數(shù)。

如果希望連接字符串中的分隔符不一定是“，”逗號(hào)，也可以是自定義的符號(hào)，則只需要稍微修改前面的自定義聚合函數(shù)（mergeName）的代碼即可。

后語：

除了上面介紹的方法外，T-SQL還支持FOR XML PATH將查詢結(jié)果集以XML形式展現(xiàn)，它也可以簡化查詢語句以實(shí)現(xiàn)一些可能需要借助函數(shù)或存儲(chǔ)過程來完成的工作。

另外，在SQL Server 2012之后的版本，也增加了一個(gè)類似于MySQL數(shù)據(jù)庫中的Group_Concat（）函數(shù)，它能將相同的行組合起來，比起自己定義函數(shù)，要方便很多。有興趣的讀者也可以了解以上的一些技術(shù)。

參考文獻(xiàn)：

[1] 何繼業(yè)，易丹，陳國榮.數(shù)據(jù)庫應(yīng)用技術(shù)-SQL Server 2008[M].中國水利水電出版社，2014.

[2] 微軟公司. SQL Server 2005數(shù)據(jù)庫開發(fā)與實(shí)現(xiàn)[M].高等教育出版社，2008.