高难度sql

Question

------------------------------------
-- Author:Flystone 
-- Version:V1.001  
-- Date:2008-07-30 12:14:00
-------------------------------------- Test Data: test
If object_id('test') is not null 
    Drop table test
Go
Create table test(id nvarchar(2))
Go
Insert into test
 select 'A1' union all
 select 'A2' union all
 select 'B1' union all
 select 'B2' 
Go
--Start
Select * from test,(select 1 as id union select 2 union select 3 as id union select 4)  a
--Result:
/*id   id          
---- ----------- 
A1   1
A2   1
B1   1
B2   1
A1   2
A2   2
B1   2
B2   2
A1   3
A2   3
B1   3
B2   3
A1   4
A2   4
B1   4
B2   4（所影响的行数为 16 行）*/
--End 

Answer 1

select 1 as id union select 2 union select 3 as id union select 4根据需要改这儿,如果数据多,那用临时表

Answer 2

declare @T table([ID] nvarchar(2))
Insert @T
select N'A1' union all
select N'A2' union all
select N'B1' union all
select N'B2'
 
select * from @T
union all
select * from @T
union all
select * from @T

Answer 3

declare @t table (c char(2))
insert into @t
select 'A1' union select 'A2' union select 'B1' union select 'B2';with s as (
select c,cast(c as varchar(1000)) as ct from @t a where left(c,1)='A'
union all
select b.c,cast(ct+','+b.c as varchar(1000))
from s a 
inner join @t b on left(b.c,1)=left(a.c,1) and charindex(b.c,a.ct)=0
union all
select b.c,cast(ct+','+b.c as varchar(1000))
from s a 
inner join @t b on left(b.c,1)=char(ascii(left(a.c,1))+1) and charindex(b.c,a.ct)=0
)
select rn,c
from (select row_number() over(order by ct) as rn,convert(xml,'<Root><v>'+replace(ct,',','</v><v>')+'</v></Root>') 
         from s a
         where len(ct)=(select count(*) from @t)*3-1
        ) b(rn,ct)
outer apply (select  c.v.value('.','char(2)') from b.ct.nodes('/Root/v') c(v)) c(c)/*
rn                   c
-------------------- ----
1                    A1
1                    A2
1                    B1
1                    B2
2                    A1
2                    A2
2                    B2
2                    B1
3                    A2
3                    A1
3                    B1
3                    B2
4                    A2
4                    A1
4                    B2
4                    B1(16 行受影响)
*/

Answer 4

sql2000的话，把上面的cte改成循环就可以了，思路不变

Answer 5

SQL 2000:if object_id('tempdb..#t') is not null drop table #t
create table #t (c char(2))
insert into #t select 'A1' union select 'A2' union select 'B1' union select 'B2' union select 'C1' union select 'C2'declare @i int,@ct varchar(1000)
set @i=0if object_id('tempdb..#') is not null drop table #
select c, cast(c as varchar(1000)) as ct into # from #t a where left(c,1)='A'while @i<>(select count(*) from #)
begin
    set @i = (select count(*) from #)    insert into #
        select b.c,cast(ct+','+b.c as varchar(1000))
        from # a 
        inner join #t b on left(b.c,1)=left(a.c,1) and charindex(b.c,a.ct)=0
        where len(ct)=(select max(len(ct)) from #)    insert into #
        select b.c,cast(ct+','+b.c as varchar(1000))
        from # a 
        inner join #t b on left(b.c,1)=char(ascii(left(a.c,1))+1) and charindex(b.c,a.ct)=0
         where len(ct)=(select max(len(ct)) from #)
endtruncate table #t
declare c cursor for 
      select ct from # where len(ct)=(select max(len(ct)) from #)
open c
fetch next from c into @ct
while @@fetch_status=0
begin
    while @ct<>''
    begin
        insert into #t  select left(@ct,2)
        set @ct = stuff(@ct,1,3,'')
    end
fetch next from c into @ct
endclose c
deallocate cselect * from #t/*
c
----
A1
A2
B1
B2
C1
C2
A1
A2
B1
B2
C2
C1
A1
A2
B2
B1
C1
C2
A1
A2
B2
B1
C2
C1
A2
A1
B1
B2
C1
C2
A2
A1
B1
B2
C2
C1
A2
A1
B2
B1
C1
C2
A2
A1
B2
B1
C2
C1
*/
SQL 2005:declare @t table (c char(2))
insert into @t
select 'A1' union select 'A2' union select 'B1' union select 'B2';with s as (
select c,cast(c as varchar(1000)) as ct from @t a where left(c,1)='A'
union all
select b.c,cast(ct+','+b.c as varchar(1000))
from s a 
inner join @t b on left(b.c,1)=left(a.c,1) and charindex(b.c,a.ct)=0
union all
select b.c,cast(ct+','+b.c as varchar(1000))
from s a 
inner join @t b on left(b.c,1)=char(ascii(left(a.c,1))+1) and charindex(b.c,a.ct)=0
)
select rn,c
from (select row_number() over(order by ct) as rn,convert(xml,'<Root><v>'+replace(ct,',','</v><v>')+'</v></Root>') 
         from s a
         where len(ct)=(select count(*) from @t)*3-1
        ) b(rn,ct)
outer apply (select  c.v.value('.','char(2)') from b.ct.nodes('/Root/v') c(v)) c(c)/*
rn                   c
-------------------- ----
1                    A1
1                    A2
1                    B1
1                    B2
2                    A1
2                    A2
2                    B2
2                    B1
3                    A2
3                    A1
3                    B1
3                    B2
4                    A2
4                    A1
4                    B2
4                    B1(16 行受影响)
*/