目錄

• FOR XML PATH是什么
• 創(chuàng)建測(cè)試數(shù)據(jù)
• 使用方法介紹
• 具體實(shí)際應(yīng)用
• 代碼優(yōu)化
• STUFF()
• STUFF()函數(shù)的作用
• STUFF()函數(shù)的語(yǔ)法
• 參數(shù)解釋
• 返回類型
• 注意事項(xiàng)

最近出的每日一題里面，有一道是關(guān)于合并同類型數(shù)據(jù)為一行的題，使用SQL Server 2017版本及以上的直接使用STRING_AGG()函數(shù)即可，但是2016版本以下是沒(méi)有這個(gè)功能的，那該如何求解？

今天就給大家介紹一下FOR XML PATH，它就是用來(lái)處理低版本數(shù)據(jù)庫(kù)中數(shù)據(jù)合并的，是一個(gè)比較古老的功能了，新版本中也依然還能使用。

FOR XML PATH是什么

FOR XML PATH 是將查詢結(jié)果集以XML形式展現(xiàn)，將多行的結(jié)果，展示在同一行。

我們用實(shí)例來(lái)給大家介紹它的神奇之處。

創(chuàng)建測(cè)試數(shù)據(jù)

我們創(chuàng)建一個(gè)統(tǒng)計(jì)學(xué)生愛(ài)好的表

CREATE TABLE Stu_Hobby(Stu_Name NVARCHAR(20),--姓名Age INT,--年齡Hobby NVARCHAR(20)  --愛(ài)好)INSERT INTO Stu_HobbyVALUES  ( N"張三",19,N"踢足球"), ( N"張三",19,N"打籃球"), ( N"張三",19,N"游泳"), ( N"李四",21,N"看電影"), ( N"李四",21,N"閱讀"), ( N"王五",22,N"唱歌"), ( N"王五",22,N"玩游戲"), ( N"馬六",19,N"踢足球"), ( N"趙七",20,N"爬山"), ( N"趙七",20,N"跑步")

查詢學(xué)生愛(ài)好表Stu_Hobby里面的數(shù)據(jù):

使用方法介紹

測(cè)試數(shù)據(jù)建立好后，我們開(kāi)始對(duì)這個(gè)表里面的數(shù)據(jù)進(jìn)行查詢，并使用上FOR XML PATH。

SELECT?*FROM?dbo.Stu_Hobby?FOR?XML?PATH;

結(jié)果如下：

它會(huì)生成一段XML代碼，我們點(diǎn)擊這行代碼會(huì)彈出一整個(gè)XML的頁(yè)面，由于篇幅較長(zhǎng)，我們只截取一部分，具體如下：

此外我們還可以在FOR XML PATH的后面寫參數(shù)，如果后面接參數(shù)，會(huì)將節(jié)點(diǎn)換成參數(shù)名稱，例如：

SELECT *FROM dbo.Stu_Hobby FOR XML PATH(hobby)

結(jié)果如下圖：

已經(jīng)變成了我們添加的參數(shù)了。

跟我們實(shí)際需求相接近的是下面這個(gè)功能

我們可以單獨(dú)輸出某個(gè)字段的值，例如我們想看看學(xué)生愛(ài)好表中Hobby這一列具體有一些什么值，可以這樣寫：

SELECT Hobby+"、"FROM dbo.Stu_Hobby FOR XML PATH("")

注意：上面的+是字段拼接，就是將兩個(gè)字符串用+連成一個(gè)字符串。然后我們把XML中的給去掉。

結(jié)果如下：

可以看到我們寫的所有愛(ài)好都給列出來(lái)了，沒(méi)有去掉重復(fù)的，可以理解成把列里的值都顯示出來(lái)了。

具體實(shí)際應(yīng)用

我們現(xiàn)在想把上面的學(xué)生表里每個(gè)學(xué)生的愛(ài)好單獨(dú)顯示一行，愛(ài)好用"、"隔開(kāi)。

SELECTA.Stu_Name,A.Age,(SELECT Hobby+"、"  FROM [dbo].Stu_HobbyWHERE--必須加的匹配條件Stu_Name=A.Stu_Name AND Age=A.Age  FOR XML PATH("")) AS HobbyFROM [dbo].Stu_Hobby AGROUP BY A.Stu_Name,A.Age

見(jiàn)證奇跡的時(shí)刻到了！！！

對(duì)比我們先前建的表，這里已經(jīng)將Hobby列的數(shù)據(jù)按每個(gè)學(xué)生變成了一行。

上面的WHERE條件是必須要的，如果去掉會(huì)怎么樣呢？我們把WHERE條件注釋掉看看會(huì)怎么樣？

SELECTA.Stu_Name,A.Age,(SELECT Hobby+"、"FROM [dbo].Stu_Hobby--WHERE--必須加的匹配條件--Stu_Name=A.Stu_Name AND Age=A.AgeFOR XML PATH("")) AS HobbyFROM [dbo].Stu_Hobby AGROUP BY A.Stu_Name,A.Age

結(jié)果如下：

就會(huì)將Hobby列所有值都顯示出來(lái)，很顯然這不是我們要的結(jié)果

代碼優(yōu)化

不知道小伙伴們有沒(méi)有發(fā)現(xiàn)Hobby列的結(jié)果尾部多了一個(gè)"、"，看著好別扭，有沒(méi)有什么辦法將它去掉呢？答案是肯定的。

先用一個(gè)LEFT()和LEN()函數(shù)來(lái)處理一下Hobby列

SELECTT.Stu_Name,T.Age,LEFT(T.Hobby,LEN(T.Hobby)-1) AS HobbyFROM(SELECTA.Stu_Name,A.Age,(SELECT Hobby+"、"FROM [dbo].Stu_HobbyWHERE--必須加的匹配條件Stu_Name=A.Stu_Name AND Age=A.AgeFOR XML PATH("")) AS HobbyFROM [dbo].Stu_Hobby AGROUP BY A.Stu_Name,A.Age) T

結(jié)果如下：

這樣我們的需求就得到了完美解決，但是這個(gè)代碼有點(diǎn)長(zhǎng)額，能不能簡(jiǎn)短一點(diǎn)呀？答案也是肯定滴！在將代碼精簡(jiǎn)之前，我們需要先給大家介紹一個(gè)配合使用的函數(shù)：

STUFF()

STUFF()函數(shù)的作用

STUFF()函數(shù)用于刪除指定長(zhǎng)度的字符，并可以在指定的起點(diǎn)處插入另一組字符。STUFF()函數(shù)中如果開(kāi)始位置或長(zhǎng)度值是負(fù)數(shù)，或者如果開(kāi)始位置大于第一個(gè)字符串的長(zhǎng)度，將返回空字符串。如果要?jiǎng)h除的長(zhǎng)度大于第一個(gè)字符串的長(zhǎng)度，將刪除到第一個(gè)字符串中的第一個(gè)字符。

STUFF()函數(shù)的語(yǔ)法

STUFF ( character_expression , start , length ,character_expression )

參數(shù)解釋

character_expression：一個(gè)字符數(shù)據(jù)表達(dá)式。character_expression 可以是常量、變量，也可以是字符列或二進(jìn)制數(shù)據(jù)列。

start ：一個(gè)整數(shù)值，指定刪除和插入的開(kāi)始位置。如果 start 或 length 為負(fù)，則返回空字符串。如果 start 比第一個(gè) character_expression 長(zhǎng)，則返回空字符串。start 可以是 bigint 類型。

length：一個(gè)整數(shù)，指定要?jiǎng)h除的字符數(shù)。如果 length 比第一個(gè) character_expression 長(zhǎng)，則最多刪除到最后一個(gè) character_expression 中的最后一個(gè)字符。length 可以是 bigint 類型。

返回類型

如果 character_expression 是受支持的字符數(shù)據(jù)類型，則返回字符數(shù)據(jù)。如果 character_expression 是一個(gè)受支持的 binary 數(shù)據(jù)類型，則返回二進(jìn)制數(shù)據(jù)。

注意事項(xiàng)

1.如果開(kāi)始位置或長(zhǎng)度值是負(fù)數(shù)，或者如果開(kāi)始位置大于第一個(gè)字符串的長(zhǎng)度，將返回空字符串。如果要?jiǎng)h除的長(zhǎng)度大于第一個(gè)字符串的長(zhǎng)度，將刪除到第一個(gè)字符串中的第一個(gè)字符。

2.如果結(jié)果值大于返回類型支持的最大值，則產(chǎn)生錯(cuò)誤。

--以上信息來(lái)源微軟官方文檔

這定義看的頭暈，我們還是來(lái)看看怎么使用吧

實(shí)例：

SELECT STUFF("abcdefg",1,0,"1234") --結(jié)果為"1234abcdefg"SELECT STUFF("abcdefg",1,1,"1234") --結(jié)果為"1234bcdefg"SELECT STUFF("abcdefg",2,1,"1234") --結(jié)果為"a1234cdefg"SELECT STUFF("abcdefg",2,2,"1234") --結(jié)果為"a1234defg"

說(shuō)了這么多，我們看看STUFF怎么解決我們上面的問(wèn)題吧，上代碼：

SELECTA.Stu_Name,A.Age,STUFF((SELECT "、"+HobbyFROM [dbo].Stu_HobbyWHERE--必須加的匹配條件Stu_Name=A.Stu_Name AND Age=A.Age    FOR XML PATH("")),1,1,"") AS HobbyFROM [dbo].Stu_Hobby AGROUP BY A.Stu_Name,A.Age

是不是比LEFT簡(jiǎn)短一些啦？我們看一下結(jié)果是不是我們想要的。

完美！

好了，F(xiàn)OR XML PATH就介紹到這里了，小伙伴可以對(duì)比以上兩種優(yōu)化的方法，自行比較哪種方式更加簡(jiǎn)單易懂。

到此這篇關(guān)于SQL 中的For Xml Path的文章就介紹到這了,更多相關(guān)SQL For Xml Path內(nèi)容請(qǐng)搜索以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持！