求救:如何写这条SQL语句?

[wclluck]

有test1和test2两个表,我想转变成test3,不知如何写SQL语句,请教各位大虾

表test1:
id modelcost modcompany
1 2 1
2 5 2

表test2:
id stonecost stocompany
1 2 1
3 4 2

表test3
id modelcost modcompany stonecost stocompany
1 2 1 2 1
2 5 2 0 0
3 0 0 4 2

[lizhaogui]

select isnull(a.[id],b.[id]) as [id],
isnull(a.modelcost,0) as modelcost,
isnull(a.modcompany,0) as modcompany,
isnull(b.stonecost,0) as stonecost,
isnull(b.stocompany,0) as stocompany
from table1 a
full join table2 b
on a.[id]=b.[id]
order by [id]

[maxsolomon]

select isnull(a.[id],b.[id]) as [id],
isnull(a.modelcost,0) as modelcost,
isnull(a.modcompany,0) as modcompany,
isnull(b.stonecost,0) as stonecost,
isnull(b.stocompany,0) as stocompany
from table1 a
full join table2 b
on a.[id]=b.[id]
order by [id]

[wclluck]

在下先谢谢各位了，我先试一下马上结分

[jasengod2003]

select ISNULL(t1.id,t2.id) as id,
isnull(t1.modelcost,0) as modelcost,
isnull(t1.modcompany,0) as modcompany,
isnull(t2.stonecost,0) as stonecost,
isnull(t2.stocompany,0) as stocompany into test3
from test1 t1 FULL JOIN test2 t2 on t1.id=t2.id
楼主试试吧，应该没问题啦！！！！！！

[wclluck]

谢谢各位，问题已解决，散分!!

[lizhaogui]

slect a.*,b.* from table1 a,table2 b
where a.id=b.id(+)
union
(slect a.*,b.* from table1 a,table2 b
where a.id(+)=b.id);

[lizhaogui]

字段中加一个判断题条件NULL（字段名，‘0’）

[leeboyan]

select isnull(a.id,b.id)as id,isnull(modelcost,0)as modelcost,isnull(modcompany,0)as modcompany,isnull(stonecost,0)as stonecost,isnull(stocompany,0)as stocompany into test3 from test1a left join test2 b on a.id=b.id

[leeboyan]

--上面test1和a中间没加空格
select isnull(a.id,b.id)as id,isnull(modelcost,0)as modelcost,isnull(modcompany,0)as modcompany,isnull(stonecost,0)as stonecost,isnull(stocompany,0)as stocompany into test3 from test1 a left join test2 b on a.id=b.id

[软侠]

看了樓上幾位代碼，我覺得都沒有精確理解樓主的意思，代碼都是錯誤的，應該如下寫才正確：
select ISNULL(t1.id,t2.id) id,isnull(t1.modelcost,0) modelcost,isnull(t1.modcompany,0) modcompany,isnull(t2.stonecost,0) stonecost,isnull(t2.stocompany,0) stocompany into test3 from test1 t1 FULL JOIN test2 t2 on t1.id=t2.id

必須要用到全連接才能達到樓主的要求，樓主可以把我上面的代碼拷進查詢分析器中直接運行，結果一定是樓主想要的正確結果

[子陌红尘]

select
id = (case when a.id is null then b.id else a.id end),
modelcost = isnull(a.modelcost ,0),
modcompany = isnull(a.modcompany,0),
stonecost = isnull(b.stonecost ,0),
stocompany = isnull(b.stocompany,0)
from
test1 a
full outer join
test2 b
on
a.id = b.id

[wgsasd311]

来晚也：)

[samfeng_2003]

select isnull(a.[id],b.[id]) as [id],
isnull(a.modelcost,0) as modelcost,
isnull(a.modcompany,0) as modcompany,
isnull(b.stonecost,0) as stonecost,
isnull(b.stocompany,0) as stocompany
from @s1 a
full join @s2 b
on a.[id]=b.[id]
order by [id]

[leeboyan]

对，的确用full join，好久不来抢楼没感觉了