SQL中的for xml path
點擊關(guān)注“SQL數(shù)據(jù)庫開發(fā)”，
設(shè)為“置頂或星標”，第一時間送達干貨
FOR XML PATH是什么
FOR XML PATH 是將查詢結(jié)果集以XML形式展現(xiàn)，將多行的結(jié)果，展示在同一行。
我們用實例來給大家介紹它的神奇之處。
創(chuàng)建測試數(shù)據(jù)
我們創(chuàng)建一個統(tǒng)計學生愛好的表
CREATE TABLE Stu_Hobby(
Stu_Name NVARCHAR(20),--姓名
Age INT,--年齡
Hobby NVARCHAR(20) --愛好
)
INSERT INTO Stu_Hobby
VALUES ( 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里面的數(shù)據(jù):
使用方法介紹
測試數(shù)據(jù)建立好后，我們開始對這個表里面的數(shù)據(jù)進行查詢，并使用上FOR XML PATH。
SELECT *
FROM dbo.Stu_Hobby FOR XML PATH;結(jié)果如下：
它會生成一段XML代碼，我們點擊這行代碼會彈出一整個XML的頁面，由于篇幅較長，我們只截取一部分，具體如下：
此外我們還可以在FOR XML PATH的后面寫參數(shù)，如果后面接參數(shù)，會將節(jié)點
SELECT *
FROM dbo.Stu_Hobby FOR XML PATH(hobby)結(jié)果如下圖：
跟我們實際需求相接近的是下面這個功能
我們可以單獨輸出某個字段的值，例如我們想看看學生愛好表中Hobby這一列具體有一些什么值，可以這樣寫：
SELECT Hobby+'、'
FROM dbo.Stu_Hobby FOR XML PATH('')注意：上面的+是字段拼接，就是將兩個字符串用+連成一個字符串。然后我們把XML中的
結(jié)果如下：
可以看到我們寫的所有愛好都給列出來了，沒有去掉重復(fù)的，可以理解成把列里的值都顯示出來了。
具體實際應(yīng)用
我們現(xiàn)在想把上面的學生表里每個學生的愛好單獨顯示一行，愛好用"、"隔開。
SELECT
A.Stu_Name,
A.Age,
(SELECT Hobby+'、'
FROM [dbo].Stu_Hobby
WHERE
--必須加的匹配條件
Stu_Name=A.Stu_Name AND Age=A.Age
FOR XML PATH('')) AS Hobby
FROM [dbo].Stu_Hobby A
GROUP BY A.Stu_Name,A.Age見證奇跡的時刻到了！！！
對比我們先前建的表，這里已經(jīng)將Hobby列的數(shù)據(jù)按每個學生變成了一行。
上面的WHERE條件是必須要的，如果去掉會怎么樣呢？我們把WHERE條件注釋掉看看會怎么樣？
SELECT
A.Stu_Name,
A.Age,
(SELECT Hobby+'、'
FROM [dbo].Stu_Hobby
--WHERE
--必須加的匹配條件
--Stu_Name=A.Stu_Name AND Age=A.Age
FOR XML PATH('')) AS Hobby
FROM [dbo].Stu_Hobby A
GROUP BY A.Stu_Name,A.Age結(jié)果如下：
就會將Hobby列所有值都顯示出來，很顯然這不是我們要的結(jié)果
代碼優(yōu)化
不知道小伙伴們有沒有發(fā)現(xiàn)Hobby列的結(jié)果尾部多了一個"、"，看著好別扭，有沒有什么辦法將它去掉呢？答案是肯定的。
先用一個LEFT()和LEN()函數(shù)來處理一下Hobby列
SELECT
T.Stu_Name,
T.Age,
LEFT(T.Hobby,LEN(T.Hobby)-1) AS Hobby
FROM
(SELECT
A.Stu_Name,
A.Age,
(SELECT Hobby+'、'
FROM [dbo].Stu_Hobby
WHERE
--必須加的匹配條件
Stu_Name=A.Stu_Name AND Age=A.Age
FOR XML PATH('')) AS Hobby
FROM [dbo].Stu_Hobby A
GROUP BY A.Stu_Name,A.Age
) T結(jié)果如下：
這樣我們的需求就得到了完美解決，但是這個代碼有點長額，能不能簡短一點呀？答案也是肯定滴！在將代碼精簡之前，我們需要先給大家介紹一個配合使用的函數(shù)：
STUFF()
STUFF()函數(shù)的作用
STUFF()函數(shù)用于刪除指定長度的字符，并可以在指定的起點處插入另一組字符。STUFF()函數(shù)中如果開始位置或長度值是負數(shù)，或者如果開始位置大于第一個字符串的長度，將返回空字符串。如果要刪除的長度大于第一個字符串的長度，將刪除到第一個字符串中的第一個字符。
STUFF()函數(shù)的語法
STUFF ( character_expression , start , length ,character_expression )
參數(shù)解釋
character_expression：一個字符數(shù)據(jù)表達式。character_expression 可以是常量、變量，也可以是字符列或二進制數(shù)據(jù)列。
start ：一個整數(shù)值，指定刪除和插入的開始位置。如果 start 或 length 為負，則返回空字符串。如果 start 比第一個 character_expression 長，則返回空字符串。start 可以是 bigint 類型。
length：一個整數(shù)，指定要刪除的字符數(shù)。如果 length 比第一個 character_expression 長，則最多刪除到最后一個 character_expression 中的最后一個字符。length 可以是 bigint 類型。
返回類型
如果 character_expression 是受支持的字符數(shù)據(jù)類型，則返回字符數(shù)據(jù)。如果 character_expression 是一個受支持的 binary 數(shù)據(jù)類型，則返回二進制數(shù)據(jù)。
注意事項
1、如果開始位置或長度值是負數(shù)，或者如果開始位置大于第一個字符串的長度，將返回空字符串。如果要刪除的長度大于第一個字符串的長度，將刪除到第一個字符串中的第一個字符。
2、如果結(jié)果值大于返回類型支持的最大值，則產(chǎn)生錯誤。
--以上信息來源微軟官方文檔
這定義看的頭暈，我們還是來看看怎么使用吧
實例：
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'說了這么多，我們看看STUFF怎么解決我們上面的問題吧，上代碼：
SELECT
A.Stu_Name,
A.Age,
STUFF(
(SELECT '、'+Hobby
FROM [dbo].Stu_Hobby
WHERE
--必須加的匹配條件
Stu_Name=A.Stu_Name AND Age=A.Age
FOR XML PATH('')
),1,1,'') AS Hobby
FROM [dbo].Stu_Hobby A
GROUP BY A.Stu_Name,A.Age是不是比LEFT簡短一些啦？我們看一下結(jié)果是不是我們想要的。
完美！
好了，F(xiàn)OR XML PATH就介紹到這里了，小伙伴可以對比以上兩種優(yōu)化的方法，自行比較哪種方式更加簡單易懂。
我是岳哥，最后給大家分享我寫的SQL兩件套：《SQL基礎(chǔ)知識第二版》和《SQL高級知識第二版》的PDF電子版。里面有各個語法的解釋、大量的實例講解和批注等等，非常通俗易懂，方便大家跟著一起來實操。
有需要的讀者可以下載學習，在下面的公眾號「數(shù)據(jù)前線」(非本號)后臺回復(fù)關(guān)鍵字：SQL，就行
數(shù)據(jù)前線
——End——
后臺回復(fù)關(guān)鍵字：1024，獲取一份精心整理的技術(shù)干貨 后臺回復(fù)關(guān)鍵字：進群，帶你進入高手如云的交流群。
推薦閱讀
