很有意思的SQL多行數(shù)據(jù)拼接
要實(shí)現(xiàn)的SQL查詢很原始:
要求從第一個表進(jìn)行查詢得到第二個表格式的數(shù)據(jù),上網(wǎng)查詢之后竟然能寫出下面的SQL:
select * from userino
SELECT * FROM(
SELECT DISTINCT userpart
FROM userino
)A
OUTER APPLY(
SELECT
[usernames]= replace(replace(replace((SELECT username as value FROM userino N
WHERE userpart = A.userpart order by n.username asc FOR XML AUTO),'"/><N value="','/')
,'<N value="',''),'"/>','')
)N
OUTER APPLY(
SELECT
[username_cns]= replace(replace(replace((SELECT username_cn as value FROM userino M
WHERE userpart = A.userpart order by m.username asc FOR XML AUTO),'"/><M value="','/')
,'<M value="',''),'"/>','')
)M
現(xiàn)將SQL進(jìn)行一下分析:
總共使用到的點(diǎn)有:OUTER APPLY,F(xiàn)OR XML AUTO。由于對SQL Server沒有很深的研究,所以記錄一下
OUTER APPLY 是SQL2005開始支持的一種查詢方法,類似于連接查詢,是將兩個查詢結(jié)果進(jìn)行拼接,但是奇特的是,使用OUTER APPLY竟然能夠在Apply后面的查詢中使用前面已經(jīng)得到的查詢結(jié)果。
如:
select * from
(select * from userino) A
cross join (select username from userino
where username = A.username )B
select * from
(select * from userino) A
join (select username from userino ) B on a.username = b.username
select * from
(select * from userino) A
OUTER APPLY (select username from userino
where username = A.username ) B
第一段SQL顯然是錯的,有兩個原因:1.Cross Join本來就是無條件的,2. SQl Server會爆出如下錯誤:
The multi-part identifier "A.username" could not be bound.
大家可能會說有條件的Join查詢本來就不是這樣寫的,應(yīng)該寫為第二條SQL這樣的樣子,其實(shí)這樣寫和第三條SQL中使用Outer apply 實(shí)現(xiàn)的效果是一樣的
可是 Outer Apply還能實(shí)現(xiàn)如下的效果
select * from
(select * from userino) A
OUTER APPLY (select [value] = a.username+'test' ) B
這個恐怕直接使用join就有點(diǎn)麻煩了,上面的例子也許沒什么意義,其實(shí)SQL2005提出Apply連接方法主要是為了在連接查詢中使用已經(jīng)執(zhí)行的查詢語句的結(jié)果
除了“OUTER APPLY”,SQL Server還有CROSS APPLY,之間的區(qū)別主要是在Null值的處理上
FOR XML AUTO 主要用于將SQL的查詢結(jié)果直接返回成XML語句,F(xiàn)or Xml 除了auto外 還有RAW和EXPLICIT,詳見《超級簡單:使用FOR XML AUTO控制XML輸出》
在文章剛開始提出的SQL文,就是使用了上面的兩個特性,首先使用Outer Apply來實(shí)現(xiàn)類似于使用userpart進(jìn)行分組的效果,來分別篩選出各個userpart中的user,然后由于篩選出的結(jié)果是多行,所以使用 for xml 來把多行數(shù)據(jù)拼接成xml,最后很二的對xml進(jìn)行拆分....
綜上,感覺這種實(shí)現(xiàn)方式比較獨(dú)特,又學(xué)習(xí)了SQL Server中的一些特性,和大家分享一下
相關(guān)文章
Sql Server 存儲過程調(diào)用存儲過程接收輸出參數(shù)返回值
這篇文章主要介紹了Sql Server 存儲過程調(diào)用存儲過程接收輸出參數(shù)返回值,需要的朋友可以參考下2017-06-06SQL恢復(fù)master數(shù)據(jù)庫方法 只有mdf文件的數(shù)據(jù)庫如何恢復(fù)
這篇文章主要為大家詳細(xì)介紹了SQL恢復(fù)master數(shù)據(jù)庫方法,還分析了只有mdf文件的數(shù)據(jù)庫如何進(jìn)行恢復(fù)的情況,也就是沒有l(wèi)og文件的數(shù)據(jù)庫文件恢復(fù)方法,感興趣的小伙伴們可以參考一下2016-05-05mybatis collection 多條件查詢的實(shí)現(xiàn)方法
這篇文章主要介紹了mybatis collection 多條件查詢的實(shí)現(xiàn)方法的相關(guān)資料,希望通過本文能幫助到大家,需要的朋友可以參考下2017-10-10sqlserver數(shù)據(jù)庫大型應(yīng)用解決方案經(jīng)驗(yàn)總結(jié)
對于一個大型的互聯(lián)網(wǎng)應(yīng)用,每天百萬級甚至上億的PV無疑對數(shù)據(jù)庫造成了相當(dāng)高的負(fù)載。對于系統(tǒng)的穩(wěn)定性和擴(kuò)展性造成了極大的問題2013-10-10海量數(shù)據(jù)庫的查詢優(yōu)化及分頁算法方案
海量數(shù)據(jù)庫的查詢優(yōu)化及分頁算法方案...2007-03-03SQL?IFNULL()函數(shù)詳細(xì)解析(最新推薦)
IFNULL()?函數(shù)用于判斷第一個表達(dá)式是否為?NULL,如果為?NULL?則返回第二個參數(shù)的值,如果不為?NULL?則返回第一個參數(shù)的值,這篇文章主要介紹了SQL?IFNULL()函數(shù)詳細(xì)解析,需要的朋友可以參考下2023-01-01非常不錯的SQL語句學(xué)習(xí)手冊實(shí)例版
非常不錯的SQL語句學(xué)習(xí)手冊實(shí)例版...2007-03-03深入分析MSSQL數(shù)據(jù)庫中事務(wù)隔離級別和鎖機(jī)制
事務(wù)隔離級別簡單的說,就是當(dāng)激活事務(wù)時,控制事務(wù)內(nèi)因SQL語句產(chǎn)生的鎖定需要保留多入,影響范圍多大,以防止多人訪問時,在事務(wù)內(nèi)發(fā)生數(shù)據(jù)查詢的錯誤。設(shè)置事務(wù)隔離級別將影響整條連接。2014-08-08SQL Server Bulk Insert 只需要部分字段時的方法
上午在找Bulk Insert的資料看,還轉(zhuǎn)了一篇。不巧今天下午就用上了,我遇到的需求是導(dǎo)出表A中的N個字段,然后導(dǎo)入到表B的N個字段當(dāng)中。2011-10-10