stuff:
作用
stuff(param1, startIndex, length, param2)
將param1中自startIndex(SQL中都是從1開始,而非0)起,刪除length個字符,然后用param2替換刪掉的字符峰尝。
示例
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'
一.FOR XML PATH 簡單介紹
假設(shè)現(xiàn)在有一張興趣愛好表(hobby)用來存放興趣愛好,表結(jié)構(gòu)如下:
接下來我們來看應(yīng)用FOR XML PATH的查詢結(jié)果語句如下:
SELECT *
FROM @hobby
FOR XML PATH
結(jié)果:
<row>
<hobbyID>1</hobbyID>
<hName>爬山</hName>
</row>
<row>
<hobbyID>2</hobbyID>
<hName>游泳</hName>
</row>
<row>
<hobbyID>3</hobbyID>
<hName>美食</hName>
</row>
改變XML行節(jié)點的名稱:
SELECT *
FROM @hobby
FOR XML PATH('MyHobby')
原來的行節(jié)點<row> 變成在PATH()中自定義的名稱<MyHobby>,結(jié)果如下:
<MyHobby>
<hobbyID>1</hobbyID>
<hName>爬山</hName>
</MyHobby>
<MyHobby>
<hobbyID>2</hobbyID>
<hName>游泳</hName>
</MyHobby>
<MyHobby>
<hobbyID>3</hobbyID>
<hName>美食</hName>
</MyHobby>
列節(jié)點如何改變呢:
SELECT hobbyID as 'MyCode',hName as 'MyName'
FROM @hobby
FOR XML PATH('MyHobby')
列的節(jié)點名稱也會變成自定義的名稱 <MyCode>與<MyName>結(jié)果如下:
<MyHobby>
<MyCode>1</MyCode>
<MyName>爬山</MyName>
</MyHobby>
<MyHobby>
<MyCode>2</MyCode>
<MyName>游泳</MyName>
</MyHobby>
<MyHobby>
<MyCode>3</MyCode>
<MyName>美食</MyName>
</MyHobby>
自定義輸出方式:
SELECT '[ '+hName+' ]' FROM @hobby FOR XML PATH('')
結(jié)果如下
[ 爬山 ][ 游泳 ][ 美食 ]
其他類型的列,將它們轉(zhuǎn)換成字符串類型:
SELECT '{'+STR(hobbyID)+'}','[ '+hName+' ]'
FROM @hobby
FOR XML PATH('')
二.一個應(yīng)用場景
一張學生表俱恶,列分別為(stuID,sName,hobby)
- stuID代表學生編號
- sName代表學生姓名
- hobby列存學生的愛好
表結(jié)構(gòu)如下:
要求是查詢學生表,顯示所有學生的愛好的結(jié)果集范舀,代碼如下:
SELECT B.sName,LEFT(StuList,LEN(StuList)-1) as hobby
FROM (
SELECT sName,(SELECT hobby+','
FROM student
WHERE sName=A.sName
FOR XML PATH('')) AS StuList
FROM student A
GROUP BY sName
) B
結(jié)果如下:
- 通過FOR XML PATH 將愛好合是,顯示成格式為:“ 愛好1,愛好2锭环,愛好3聪全,”的格式!
- 外層的語句:
LEFT(StuList,LEN(StuList)-1)
就是來去掉逗號