请问一个SQL语句问题

[liliah]

请问一个SQL语句问题，先谢谢.有2个表。table1 table2内容如下：

table1
material mmamount
a 10
b 20
c 30
table2
material fiamount
b 20
c 30
d 40
e 0

要求结果是：
mmamount fiamount
a 10 null
b 20 20
c 30 31
d null 41
e null 0

[playwarcraft]

不會...還能出來31,41的說...

[chuifengde]

不懂

[xiaoku]

select isnull(a.material,b.material) , mmamount , fiamount
from table1 a full join table2 on a.material=b.material


LZ要这个吧？
难道你输错了？

[playwarcraft]

select case when A.material is null then B.material else A.material end as material
,A.mmamount,B.fiamount from table1 A full join table2 B on A.material=B.material

--這樣只能出來如下結果,至於31,41怎麼來,就......

material mmamount fiamount
-------- ----------- -----------
a 10 NULL
b 20 20
c 30 30
d NULL 40
e NULL 0

[jdzhangjun]

SELECT ISNULL(dbo.Table_1.material, dbo.Table_2.material) AS Expr2, dbo.Table_1.mmamount, dbo.Table_2.fiamount
FROM dbo.Table_1 FULL OUTER JOIN
dbo.Table_2 ON dbo.Table_1.material = dbo.Table_2.material

[cuckoojosh]

怀疑你的要求结果是不是：
mmamount fiamount
a 10 null
b 20 20
c 30 30
d null 40
e null 0

SELECT TABLE1.material AS material, TABLE1.mmamount AS mmamount, TABLE2.fiamount AS FROM TABLE1 LEFT JOIN TABLE2 ON TABLE1.material = TABLE2.material
UNION
SELECT TABLE2.material AS material, TABLE1.mmamount AS mmamount, TABLE2.fiamount AS FROM TABLE2 LEFT JOIN TABLE1 ON TABLE2.material = TABLE1.material

[xiaoku]

31 41 不懂！



c 30 31
d null 41
e null 0

[WangZWang]

select material=isNULL(a.material,b.material),a.mmamount,b.fiamount
from table1 as a full join table2 as b on a.material=b.material

[ilon_chen]

select a.*,b.fiamount from table1 a full join table2 b on a.material=b.material


不过31,41是出不了！

[liliah]

----大家不要笑话，不小心输入错误啦。这儿朋友真热心哪。这么快有这么多人帮忙，重新写一下：
有2个表。table1 table2内容如下：

table1
material mmamount
a 10
b 20
c 30
table2
material fiamount
b 20
c 31
d 41
e 0

要求结果是：
mmamount fiamount
a 10 null
b 20 20
c 30 31
d null 41
e null 0

[jetdw]

呵呵
楼主的问题解决了吧
接点分:)

[buduanxuexi]

full out join一下就可以了