很有意思的SQL多行數(shù)據(jù)拼接
要實(shí)現(xiàn)的SQL查詢很原始:
要求從第一個(gè)表進(jìn)行查詢得到第二個(gè)表格式的數(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。由于對(duì)SQL Server沒有很深的研究,所以記錄一下
OUTER APPLY 是SQL2005開始支持的一種查詢方法,類似于連接查詢,是將兩個(gè)查詢結(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顯然是錯(cuò)的,有兩個(gè)原因:1.Cross Join本來就是無條件的,2. SQl Server會(huì)爆出如下錯(cuò)誤:
The multi-part identifier "A.username" could not be bound.
大家可能會(huì)說有條件的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
這個(gè)恐怕直接使用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,詳見《超級(jí)簡(jiǎn)單:使用FOR XML AUTO控制XML輸出》
在文章剛開始提出的SQL文,就是使用了上面的兩個(gè)特性,首先使用Outer Apply來實(shí)現(xiàn)類似于使用userpart進(jìn)行分組的效果,來分別篩選出各個(gè)userpart中的user,然后由于篩選出的結(jié)果是多行,所以使用 for xml 來把多行數(shù)據(jù)拼接成xml,最后很二的對(duì)xml進(jìn)行拆分....
綜上,感覺這種實(shí)現(xiàn)方式比較獨(dú)特,又學(xué)習(xí)了SQL Server中的一些特性,和大家分享一下
相關(guān)文章
Sql Server 存儲(chǔ)過程調(diào)用存儲(chǔ)過程接收輸出參數(shù)返回值
這篇文章主要介紹了Sql Server 存儲(chǔ)過程調(diào)用存儲(chǔ)過程接收輸出參數(shù)返回值,需要的朋友可以參考下2017-06-06SQL恢復(fù)master數(shù)據(jù)庫(kù)方法 只有mdf文件的數(shù)據(jù)庫(kù)如何恢復(fù)
這篇文章主要為大家詳細(xì)介紹了SQL恢復(fù)master數(shù)據(jù)庫(kù)方法,還分析了只有mdf文件的數(shù)據(jù)庫(kù)如何進(jìn)行恢復(fù)的情況,也就是沒有l(wèi)og文件的數(shù)據(jù)庫(kù)文件恢復(fù)方法,感興趣的小伙伴們可以參考一下2016-05-05mybatis collection 多條件查詢的實(shí)現(xiàn)方法
這篇文章主要介紹了mybatis collection 多條件查詢的實(shí)現(xiàn)方法的相關(guān)資料,希望通過本文能幫助到大家,需要的朋友可以參考下2017-10-10sqlserver數(shù)據(jù)庫(kù)大型應(yīng)用解決方案經(jīng)驗(yàn)總結(jié)
對(duì)于一個(gè)大型的互聯(lián)網(wǎng)應(yīng)用,每天百萬級(jí)甚至上億的PV無疑對(duì)數(shù)據(jù)庫(kù)造成了相當(dāng)高的負(fù)載。對(duì)于系統(tǒng)的穩(wěn)定性和擴(kuò)展性造成了極大的問題2013-10-10海量數(shù)據(jù)庫(kù)的查詢優(yōu)化及分頁算法方案
海量數(shù)據(jù)庫(kù)的查詢優(yōu)化及分頁算法方案...2007-03-03SQL?IFNULL()函數(shù)詳細(xì)解析(最新推薦)
IFNULL()?函數(shù)用于判斷第一個(gè)表達(dá)式是否為?NULL,如果為?NULL?則返回第二個(gè)參數(shù)的值,如果不為?NULL?則返回第一個(gè)參數(shù)的值,這篇文章主要介紹了SQL?IFNULL()函數(shù)詳細(xì)解析,需要的朋友可以參考下2023-01-01非常不錯(cuò)的SQL語句學(xué)習(xí)手冊(cè)實(shí)例版
非常不錯(cuò)的SQL語句學(xué)習(xí)手冊(cè)實(shí)例版...2007-03-03深入分析MSSQL數(shù)據(jù)庫(kù)中事務(wù)隔離級(jí)別和鎖機(jī)制
事務(wù)隔離級(jí)別簡(jiǎn)單的說,就是當(dāng)激活事務(wù)時(shí),控制事務(wù)內(nèi)因SQL語句產(chǎn)生的鎖定需要保留多入,影響范圍多大,以防止多人訪問時(shí),在事務(wù)內(nèi)發(fā)生數(shù)據(jù)查詢的錯(cuò)誤。設(shè)置事務(wù)隔離級(jí)別將影響整條連接。2014-08-08sql無效字符 執(zhí)行sql語句報(bào)錯(cuò)解決方案
說起來慚愧,在執(zhí)行sql語句時(shí)總是犯一些小錯(cuò)誤,糾結(jié)半天,這不應(yīng)為一個(gè)分號(hào)的玩意折騰了好半天,需要了解的朋友可以參考下2012-11-11SQL Server Bulk Insert 只需要部分字段時(shí)的方法
上午在找Bulk Insert的資料看,還轉(zhuǎn)了一篇。不巧今天下午就用上了,我遇到的需求是導(dǎo)出表A中的N個(gè)字段,然后導(dǎo)入到表B的N個(gè)字段當(dāng)中。2011-10-10