如何得出如下两表的交叉结果集
A表

A B
1 3
2 2


B表

C D
1 a
2 b
3 c


想得到以下结果
C D A
1 a 1
2 b 1
3 c 1
1 a 2
2 b 2

即通过A表每记录的B值决定调用B表的记录条数

[解决办法]
declare @a table(A int, B int)
insert @a select 1, 3
insert @a select 2, 2


declare @b table(C int, D nvarchar(5))
insert @b select 1, 'a '
insert @b select 2, 'b '
insert @b select 3, 'c '


select b.* ,a.a from @a a join @b b on a.a=1
union all
select b.* ,a.a from @a a join @b b on a.a=2 and b.c!> a.a


(1 row(s) affected)


(1 row(s) affected)


(1 row(s) affected)


(1 row(s) affected)


(1 row(s) affected)

C D a
----------- ----- -----------
1 a 1
2 b 1
3 c 1
1 a 2
2 b 2

(5 row(s) affected)


[解决办法]
create table t1(A int, B int)
insert t1 select 1, 3
insert t1 select 2, 2
insert t1 select 3, 4

create table t2(C int, D nvarchar(5))
insert t2 select 1, 'a '
insert t2 select 2, 'b '
insert t2 select 3, 'c '
insert t2 select 4, 'd '

create table t3(c varchar(10),d varchar(10),a int)

declare @col1 int,@col2 int,@sql varchar(400)
declare cur cursor for select * from t1
open cur
fetch next from cur into @col1,@col2
while @@fetch_status=0
begin
set @sql= 'insert t3 select top '+cast(@col2 as varchar)+ ' *, '+cast(@col1 as varchar)
+ ' from t2 '

exec(@sql)
fetch next from cur into @col1,@col2
end
deallocate cur

select * from t3

drop table t1,t2,t3