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.

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