如何得出如下两表的交叉结果集
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
