怎样将两个表合并成一个表???.

[gavinzhao]

我有下面两个表需要合并成一个表,请问怎样实现:
table1:
invoiceNo goodName price currency
00123456 其它商品 200 RMB

table2:
invoiceNo costName amount
00123456 运费 150
00123456 文件费 100

查询后的结果:
query table:
invoiceNo goodName price currency costName amount
00123456 其它商品 200 RMB 运费 150
文件费 100

下面是错误的结果:
invoiceNo goodName price currency costName amount
00123456 其它商品 200 RMB 运费 150
00123456 其它商品 200 RMB 文件费 100

[97866]

呵，用游标吧

[laomaoli]

你的问题没有说清楚

[97866]

Declare @invoiceNo varChar(20),@goodName VarChar(30),
@price decimal(9,2),@currency varChar(10),
@costName varChar(30),@amount decimal(9,2),
@Swap varChar(20)
Create table #tmp(invoiceNo varChar(20),goodName VarChar(30),
price decimal(9,2),currency varChar(10),
costName varChar(30),amount decimal(9,2))
Declare Cu_A Cursor For Select a.invoiceNo,goodName,price,currency,costName,amount
from table1 a,table2 b where a.invoiceNo=b.invoiceNo
Open Cu_A
Set @Swap=''
Fetch Cu_A into @invoiceNo,@goodName,@price,@currency,@costName,@amount
while @@Fetch_Status=1
begin
if @Swap<>@InvoiceNo
begin
Insert Into #tmp
values(@invoiceNo,@goodName,@price,@currency,@costName,@amount)
Set @Swap=@InvoiceNo
end
else
Insert Into #tmp
values(Null,Null,Null,Null,@costName,@amount)
Fetch Cu_A into @invoiceNo,@goodName,@price,@currency,@costName,@amount
end
close Cu_A
deallocate Cu_A
Select * from #tmp