SQL实例进阶-学习sql server2005 step by step(六)

后面将主要讲我个人在实际开发中和网上看到的一些实例，希望拍砖。

SQL2005 利用XML合并聚合列 

 

1. 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

 

拆分： 

--SQL2000的例子
Create table T(name nvarchar(50),amount int)
insert into T
    select 'a,b,c' ,1
union all     select 'd,f',2
union all     select 'g',3
GO

SELECT
    SUBSTRING(A.name,B.number,CHARINDEX(',',A.name+',',B.number)-B.number) 
AS [name],
    amount
FROM  T as  A
    JOIN master.dbo.spt_values AS B
        ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.name)
            AND SUBSTRING(','+A.name,B.number,1)=','

/*
name                                               amount      
-------------------------------------------------- ----------- 
a                                                  1
b                                                  1
c                                                  1
d                                                  2
f                                                  2
g                                                  3
*/

Drop table T


--SQL2005的例子
create table tb(id int,value varchar(30))
insert into tb values(1,'aa,bb')
insert into tb values(2,'aaa,bbb,ccc')
go
SELECT A.id, B.value
FROM(
    SELECT id, [value] = CONVERT(xml,'<root><v>' + REPLACE([value], ',',
 '</v><v>') + '</v></root>') FROM tb
)A
OUTER APPLY(
    SELECT value = N.v.value('.', 'varchar(100)') FROM
 A.[value].nodes('/root/v') N(v)
)B

DROP TABLE tb

/*
id          value
----------- ------------------------------
1           aa
1           bb
2           aaa
2           bbb
2           ccc

(5 行受影响)
*/



 

1. 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

 

 

Create table T(id int,name varchar(10))
insert into T select 1,'A'
insert into T select 1,'B'
insert into T select 2,'C'
insert into T select 3,'D'
insert into T select 3,'E'
GO

--sql 2000 function
Create function dbo.fn_test(@id int)
returns varchar(100)
as
begin
declare @return varchar(100)
set @return=''
select @return=@return+','+rtrim(name) from T where id=@id

set @return=stuff(@return,1,1,'')
return @return
end
GO
select id,dbo.fn_test(id) as [name] from T group by id

--sql 2000 臨時表
Create proc dbo.usp_test
as
select id,convert(varchar(100),name) as [name]
into #temp from T 
order by id

declare @id int,@name varchar(100)

update #temp
set @name=case when id=@id then @name+','+rtrim(name)
            else rtrim(name)
            end,
    @id=id,
    name=@name

select A.*
from #temp A
inner join
  (
   select id,max(len(name)) as name_len
   from #temp
   group by id
   ) B
on A.id=B.id and len(A.name)=B.name_len

GO

exec usp_test

GO

--SQL2005

select A.id,B.name_together
from 
 (select distinct id from T) as A
outer apply
  (select stuff (replace(replace((select [name] from T where id=A.id for
 XML auto),'<T name="',','),'"/>',''),1,1,'') as [name_together]
   ) as B


drop table T
drop function dbo.fn_test
drop proc usp_test

 

 

 

     最后想提的一点是如果是初学者，那么还要看下sql基本函数的运用比如select datepart(week,getdate())，select charindex('a','cca') ，一些全局变量比如@@rowcount，@@IDENTITY，临时表，递归查询等，有时候这些我们在写复杂的存储过程的时候会对我们有所帮助，同时也希望我们共同进步，如果遇到看不懂的可以百度或者Google一下。

 资源下载