一个sql，请教各位高手(马上给分）！谢谢啦~~~~~

Question

SQL> select * from test3 ;ID       VALUE                                                                                      
---- ---------                                                                                      
1           11                                                                                      
2           22                                                                                      
3           32                                                                                      
4           42                                                                                      SQL> select * from test4 ;ID       VALUE                                                                                      
---- ---------                                                                                      
1           45                                                                                      
2           35                                                                                      SQL> select a.id,a.value,nvl(b.value,0) value1 
       from test3 a,test4 b 
      where a.id = b.id(+) ;ID       VALUE    VALUE1                                                                            
---- --------- ---------                                                                            
1           11        45                                                                            
2           22        35                                                                            
3           32         0                                                                            
4           42         0

Answer 1

try:
  select a.id,a.a,nvl(b.b,0) from a,b
  where a.id = b.id(+)

Answer 2

select nvl(a.mateid,b.mateid) mateid, 
nvl(a.matename,b.matename) matename, 
nvl(a.matemodel,b.matemodel) matemodel, 
nvl(a.mateunit,b.mateunit) mateunit, 
nvl(a.planqty,0) planqty, 
nvl(b.factqty,0) factqty 
from 
aa a 
full outer join 
bb b 
on a.mateid =b.mateid and 
a.matename = b.matename and 
a.matemodel = b.matemodel and 
a.mateunit = b.mateunit