SQL 中的For Xml Path詳解
目錄
- FOR XML PATH是什么
- 創建測試數據
- 使用方法介紹
- 具體實際應用
- 代碼優化
- STUFF()
- STUFF()函數的作用
- STUFF()函數的語法
- 參數解釋
- 返回類型
- 注意事項
最近出的每日一題里面，有一道是關于合并同類型數據為一行的題，使用SQL Server 2017版本及以上的直接使用STRING_AGG()函數即可，但是2016版本以下是沒有這個功能的，那該如何求解？
今天就給大家介紹一下FOR XML PATH，它就是用來處理低版本數據庫中數據合并的，是一個比較古老的功能了，新版本中也依然還能使用。
FOR XML PATH是什么
FOR XML PATH 是將查詢結果集以XML形式展現，將多行的結果，展示在同一行。
我們用實例來給大家介紹它的神奇之處。
創建測試數據
我們創建一個統計學生愛好的表
CREATE TABLE Stu_Hobby(Stu_Name NVARCHAR(20),--姓名Age INT,--年齡Hobby NVARCHAR(20) --愛好)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"跑步")
查詢學生愛好表Stu_Hobby里面的數據:
使用方法介紹
測試數據建立好后，我們開始對這個表里面的數據進行查詢，并使用上FOR XML PATH。
SELECT?*FROM?dbo.Stu_Hobby?FOR?XML?PATH;
結果如下：
它會生成一段XML代碼，我們點擊這行代碼會彈出一整個XML的頁面，由于篇幅較長，我們只截取一部分，具體如下：
此外我們還可以在FOR XML PATH的后面寫參數，如果后面接參數，會將節點換成參數名稱，例如：
SELECT *FROM dbo.Stu_Hobby FOR XML PATH(hobby)
結果如下圖：
已經變成了我們添加的參數了。
跟我們實際需求相接近的是下面這個功能
我們可以單獨輸出某個字段的值，例如我們想看看學生愛好表中Hobby這一列具體有一些什么值，可以這樣寫：
SELECT Hobby+"、"FROM dbo.Stu_Hobby FOR XML PATH("")注意：上面的+是字段拼接，就是將兩個字符串用+連成一個字符串。然后我們把XML中的給去掉。
結果如下：
可以看到我們寫的所有愛好都給列出來了，沒有去掉重復的，可以理解成把列里的值都顯示出來了。
具體實際應用
我們現在想把上面的學生表里每個學生的愛好單獨顯示一行，愛好用"、"隔開。
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見證奇跡的時刻到了！！！
對比我們先前建的表，這里已經將Hobby列的數據按每個學生變成了一行。
上面的WHERE條件是必須要的，如果去掉會怎么樣呢？我們把WHERE條件注釋掉看看會怎么樣？
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結果如下：
就會將Hobby列所有值都顯示出來，很顯然這不是我們要的結果
代碼優化
不知道小伙伴們有沒有發現Hobby列的結果尾部多了一個"、"，看著好別扭，有沒有什么辦法將它去掉呢？答案是肯定的。
先用一個LEFT()和LEN()函數來處理一下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結果如下：
這樣我們的需求就得到了完美解決，但是這個代碼有點長額，能不能簡短一點呀？答案也是肯定滴！在將代碼精簡之前，我們需要先給大家介紹一個配合使用的函數：
STUFF()
STUFF()函數的作用
STUFF()函數用于刪除指定長度的字符，并可以在指定的起點處插入另一組字符。STUFF()函數中如果開始位置或長度值是負數，或者如果開始位置大于第一個字符串的長度，將返回空字符串。如果要刪除的長度大于第一個字符串的長度，將刪除到第一個字符串中的第一個字符。
STUFF()函數的語法
STUFF ( character_expression , start , length ,character_expression )
參數解釋
character_expression：一個字符數據表達式。character_expression 可以是常量、變量，也可以是字符列或二進制數據列。
start ：一個整數值，指定刪除和插入的開始位置。如果 start 或 length 為負，則返回空字符串。如果 start 比第一個 character_expression 長，則返回空字符串。start 可以是 bigint 類型。
length：一個整數，指定要刪除的字符數。如果 length 比第一個 character_expression 長，則最多刪除到最后一個 character_expression 中的最后一個字符。length 可以是 bigint 類型。
返回類型
如果 character_expression 是受支持的字符數據類型，則返回字符數據。如果 character_expression 是一個受支持的 binary 數據類型，則返回二進制數據。
注意事項
1.如果開始位置或長度值是負數，或者如果開始位置大于第一個字符串的長度，將返回空字符串。如果要刪除的長度大于第一個字符串的長度，將刪除到第一個字符串中的第一個字符。
2.如果結果值大于返回類型支持的最大值，則產生錯誤。
--以上信息來源微軟官方文檔
這定義看的頭暈，我們還是來看看怎么使用吧
實例：
SELECT STUFF("abcdefg",1,0,"1234") --結果為"1234abcdefg"SELECT STUFF("abcdefg",1,1,"1234") --結果為"1234bcdefg"SELECT STUFF("abcdefg",2,1,"1234") --結果為"a1234cdefg"SELECT STUFF("abcdefg",2,2,"1234") --結果為"a1234defg"說了這么多，我們看看STUFF怎么解決我們上面的問題吧，上代碼：
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簡短一些啦？我們看一下結果是不是我們想要的。
完美！
好了，FOR XML PATH就介紹到這里了，小伙伴可以對比以上兩種優化的方法，自行比較哪種方式更加簡單易懂。
到此這篇關于SQL 中的For Xml Path的文章就介紹到這了,更多相關SQL For Xml Path內容請搜索以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持！
相關文章：
網公網安備