关于交叉表的问题?

[Novelty]

表结构
A表
A1001 2005-5-1 10
A1001 2005-5-2 10
A1001 2005-5-3 10
A1002 2005-5-1 10
A1002 2005-5-2 10
A1002 2005-5-3 10
B表
A1001 2005-4-1 100
A1002 2005-4-5 100

需要得到这样一个表
A1001 2005-5-1 10 A1001 2005-4-1 100
A1001 2005-5-2 10 A1002 2005-4-5 100
A1001 2005-5-3 10
A1002 2005-5-1 10
A1002 2005-5-2 10
A1002 2005-5-3 10

如何实现?

[talantlee]

select * from a,b
是不是這樣得要求？

[zjcxc]

select *,id=identity(int) into #1 from A表
select *,id=identity(int) into #2 from B表
select * from #1 a full join #2 b on a.id=b.id
drop table #1,#2

[zjcxc]

--如果两个表的col1+col2可以保证唯一(假设你的字段为col1,col2,col3)
select *
from A表 a full join B表 b
on(select count(*) from A表 where col1=a.col1 and col2<=a.col2)
=(select count(*) from B表 where col1=b.col1 and col2<=b.col2)

[Novelty]

晚上测试一下,如果可以马上给分.