outter aplly ,cross apply,cross join

outter aplly ,cross apply,cross join 都可以在左边生成结果，再在右边调用表值函数（结果集函数或者结果集）来进行处理

 

 if object_id('[T]') is not null drop table [T]
go
create table [T]([col1] varchar(5),[col2] varchar(100),[col3] datetime,[col4] varchar(100))
insert [T]
select 'Silly','11,2,3,4','2009-11-10','A,B,C,D'
--------------开始查询--------------------------
select
    a.COl1,b.Col2,a.col3,b.col4
from
    (select Col1,COl2=convert(xml,'<root><v>'+replace(COl2,',','</v><v>')+'</v></root>'),col3,
                 COl4=convert(xml,'<root><v>'+replace(COl4,',','</v><v>')+'</v></root>') from T)a
cross apply
    (select Col2=C.v.value('.','nvarchar(100)'),Col4=C.v.value('.','nvarchar(100)')
                 from a.COl2.nodes('/root/v') as C(v))b
select col2.value('.','nvarchar(100)') from (select Col1,COl2=convert(xml,'<root><v>'+replace(COl2,',','</v><v>')+'</v></root>'),col3,
                 COl4=convert(xml,'<root><v>'+replace(COl4,',','</v><v>')+'</v></root>') from T)a

if(object_id('fun1') > 0)
  drop function fun1
go
create function fun1(@id int)
returns @temptable table([newid] int)
as
begin
    insert into @temptable
    select @id union all select @id+1 union all select @id + 2
    return
end

select * from
(
  select 1 as id union all select 2 union all select 3
) a cross apply dbo.fun1(a.id)