SQL实例进阶-学习sql server2005 step by step(六)
后面将主要讲我个人在实际开发中和网上看到的一些实例,希望拍砖。
SQL2005 利用XML合并聚合列
- 1. 合并
在平常的sql處理中,經常會碰到如下的情形,
有一個table T (id int, name varchar(10))
图片1
需要呈現的結果是,按照id相同的,把name值串起來,結果如下:
图片2
由於sql server并沒有提供字串的合并函數,所以需要特別處理,在SQL2000中,處理的方式一般有三種:cursor合并,function合并,臨時表合并。
A.Cursor合并:我們不提倡在普通的sql statement中使用cursor,所以一般不考慮這種方式。
B. Function合并:這在SQL2000中,是最常用的方式。、
图片3
C. 臨時表合并 :此種方式一般被使用在store procedure中,由於function本身的performance并不高,所以當遇到table數據較大時,常采用此種方式。
图片4
到了SQL2005 ,由於提供了outer apply 和對XML的良好支持,可以利用XML來簡單的處理合并字串的方式。
先來看一下,簡單的FOR XML出來的結果:
图片5
顯然上述結果是很吸引人的,因為它將本來3條記錄的結果,合并成1條了。如果再將上面的字串結果,利用replace,stuff等sql提供的函數來處理一下,是不是更吸引人呢?
图片6
上述結果,達到了合并字串的目的(只針對id=1), 接下來,就需要用到outer apply 來針對table里所有的id,一次全部合并name欄位了。
图片7
拆分:
代码
1 --SQL2000的例子
2 Create table T(name nvarchar(50),amount int)
3 insert into T
4 select 'a,b,c' ,1
5 union all select 'd,f',2
6 union all select 'g',3
7 GO
8
9 SELECT
10 SUBSTRING(A.name,B.number,CHARINDEX(',',A.name+',',B.number)-B.number)AS [name],
11 amount
12 FROM T as A
13 JOIN master.dbo.spt_values AS B
14 ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.name)
15 AND SUBSTRING(','+A.name,B.number,1)=','
16
17 /*
18 name amount
19 -------------------------------------------------- -----------
20 a 1
21 b 1
22 c 1
23 d 2
24 f 2
25 g 3
26 */
27
28 Drop table T
29
30
31 --SQL2005的例子
32 create table tb(id int,value varchar(30))
33 insert into tb values(1,'aa,bb')
34 insert into tb values(2,'aaa,bbb,ccc')
35 go
36 SELECT A.id, B.value
37 FROM(
38 SELECT id, [value] = CONVERT(xml,'<root><v>' + REPLACE([value], ',','</v><v>') + '</v></root>') FROM tb
39 )A
40 OUTER APPLY(
41 SELECT value = N.v.value('.', 'varchar(100)') FROMA.[value].nodes('/root/v') N(v)
42 )B
43
44 DROP TABLE tb
45
46 /*
47 id value
48 ----------- ------------------------------
49 1 aa
50 1 bb
51 2 aaa
52 2 bbb
53 2 ccc
54
55 (5 行受影响)
56 */
57
- 2. 聚合
下面再來看又一個常見的例子,假如有一個table:Tb
图片8
目的是找出每個ID的最大值。(注意是橫向比較),結果:
图片9
同樣的,sql server并沒有提供橫向比較的函數,如果用 case when a>b then…的方式顯然是不予考慮的。
那么在SQl2000中,我們一般采用MAX() 函數的特性,將table行轉列后再求出最大值。
图片10
當然在SQL2005中,由於本身提供了行轉列的函數pivot / unpivot, 所以也方便了很多.(需要注意的是unpivot是不會考慮null的情況的,需要另外union)
图片11
下面我們也可以利用XML的特性,來達到此種目的。
我們先來看看,將Tb內容指定 節點’r’為根目錄后形成的結果。
是不是感覺已經接近結果了,接下來,我們只要尋找每個節點里的MAX就可以了。(不要忘了,SQl2005是支持XML格式,可以直接對XML進行操作)
p.s. 如下將for xml path(‘r’)再轉一次xml是因為本身返回的是xml結構,而不是xml類型。
图片12
明白了上述XML的特性后,針對此問題,還可以有一個取巧的方式,即不使用 for xml的方式,而是手動拼湊字串行程xml結構,再查詢。
图片13
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 Create table T(id int,name varchar(10))
2 insert into T select 1,'A'
3 insert into T select 1,'B'
4 insert into T select 2,'C'
5 insert into T select 3,'D'
6 insert into T select 3,'E'
7 GO
8
9 --sql 2000 function
10 Create function dbo.fn_test(@id int)
11 returns varchar(100)
12 as
13 begin
14 declare @return varchar(100)
15 set @return=''
16 select @return=@return+','+rtrim(name) from T where id=@id
17
18 set @return=stuff(@return,1,1,'')
19 return @return
20 end
21 GO
22 select id,dbo.fn_test(id) as [name] from T group by id
23
24 --sql 2000 臨時表
25 Create proc dbo.usp_test
26 as
27 select id,convert(varchar(100),name) as [name]
28 into #temp from T
29 order by id
30
31 declare @id int,@name varchar(100)
32
33 update #temp
34 set @name=case when id=@id then @name+','+rtrim(name)
35 else rtrim(name)
36 end,
37 @id=id,
38 name=@name
39
40 select A.*
41 from #temp A
42 inner join
43 (
44 select id,max(len(name)) as name_len
45 from #temp
46 group by id
47 ) B
48 on A.id=B.id and len(A.name)=B.name_len
49
50 GO
51
52 exec usp_test
53
54 GO
55
56 --SQL2005
57
58 select A.id,B.name_together
59 from
60 (select distinct id from T) as A
61 outer apply
62 (select stuff (replace(replace((select [name] from T where id=A.id forXML auto),'<T name="',','),'"/>',''),1,1,'') as [name_together]
63 ) as B
64
65
66 drop table T
67 drop function dbo.fn_test
68 drop proc usp_test
69
最后想提的一点是如果是初学者,那么还要看下sql基本函数的运用比如select datepart(week,getdate()),select charindex('a','cca') ,一些全局变量比如@@rowcount,@@IDENTITY,临时表,递归查询等,有时候这些我们在写复杂的存储过程的时候会对我们有所帮助,同时也希望我们共同进步,如果遇到看不懂的可以百度或者Google一下。