有如下两个表table1 itemno F1
--------------
A 100
B 70
C 200
table2itemno F2
--------------
B 60
C 900
D 10
求一条语句需要返回如下查询结果:itemno F1 F2
----------------------
A 100 0
B 70 60
C 200 900
D 0 10
--------------
A 100
B 70
C 200
table2itemno F2
--------------
B 60
C 900
D 10
求一条语句需要返回如下查询结果:itemno F1 F2
----------------------
A 100 0
B 70 60
C 200 900
D 0 10
from table1 t1,table2 t2
where t1.itemno = t2.itemno
试试看~
SQL> SELECT * FROM A;ITEMNO F1
------ -----
A 100
B 70
C 200SQL> SELECT * FROM B;ITEMNO F2
------ -----
B 60
C 900
D 10SQL>
SQL> SELECT ITEMNO,SUM(F1),SUM(F2) FROM (
2 SELECT ITEMNO,F1,0 F2 FROM A
3 UNION
4 SELECT ITEMNO,0,F2 FROM B
5 ) GROUP BY ITEMNO
6 ORDER BY ITEMNO
7 ;ITEMNO SUM(F1) SUM(F2)
------ ---------- ----------
A 100 0
B 70 60
C 200 900
D 0 10SQL>
select t0.itemno, nvl(t1.f1,0), nvl(t2.f2,0)
from
(
select itemno from hr.table1
union
select itemno from hr.table2
) t0, hr.table1 t1, hr.table2 t2
where t0.itemno=t1.itemno(+)
and t0.itemno=t2.itemno(+)
order by t0.itemno;