SQL练习题之联接

联接算法是指sqlserver用于处理联接的物理策略,它支持三种联接算法,嵌套循环(nested loops)合并(merge)以及哈希算法.

练习题:

create table dbo.arrays(
arrid varchar(10) not null primary key,
array varchar(8000) not null
)
go

insert into arrays(arrid,array)
values
('a','20,223,2544,25567,14'),
('B','30,-23433,28'),
('C','12,10,8099,12,1200,13,12,14,10,9'),
('D','-4,--6,-45678,-2')

arrid   array

A   20,223,2544,25567,14
B   30,-23433,28
C   12,10,8099,12,1200,13,12,14,10,9
D   -4,--6,-45678,-2

想要查询出的结果是:

arrid   pos   element

A        1       20

A        2       223

.......

本文摘自inside sqlserver 2008 tsql query

SELECT arrid,
array,
substring(array,n,CHARINDEX(',',array+',',n)-n) as element
,n
,ROW_NUMBER()
over(partition by arrid order by n) as rowno
from dbo.arrays
join dbo.num
on n<=DATALENGTH(array)+1
and SUBSTRING(','+array,n,1)=','

with split
as
(
select arrid, 1 as pos ,1 as startpos,
CHARINDEX(',',array+',')-1 as endpos
from dbo.arrays
where DATALENGTH(array)>0
union all
select a.arrid,
a.pos
+1,
a.endpos
+2,
CHARINDEX(',',b.array+',',a.endpos+2)-1
from split a join
dbo.arrays b
on a.arrid = b.arrid
and CHARINDEX(',',b.array+',',a.endpos+2)>0
)
select
a.arrid,
a.pos,
a.startpos,
a.endpos,
SUBSTRING(b.array,a.startpos,a.endpos-a.startpos+1) as ele,
b.array
from split a join dbo.arrays b
on a.arrid=b.arrid
order by a.arrid

 使用了两种不同的方法,第一种方法使用了数字辅助表来生成副本.第二种方法使用CTE.

第二种方法的定位点是查出字符串中的第一个元素,递归部分是查出下一个元素的值,至到找不到,.

posted on 2011-08-07 13:24  tneduts  阅读(350)  评论(5编辑  收藏  举报

导航