求助：如何能让两张表合起来？

请各位高手帮助，不胜感激！
工单对应合同号:            
select * from v6TSTF.FSO            
得到结果：            
    合同号    工单号    … … …
    H001    1001   
    H001    1002   
    H002    1003   
    H002    1004   
    H002    1005   

工单差异分析：            
with ord(vord) as (values (1001))            
, fso_ith as (select sord,sprod,sqreq,tprod, avg(tpric) tpric, avg(tscst) tscst, sum(tqty) tqty from ord, v6tstf.ith_a,v6tstf.fso where tid='TH' and ttype in ('I','J') and tref=vord and sord=tref group by sord,sprod,sqreq,tprod)            
, mbm as (select sord,bprod,bchld,sum(bqreq) bqreq from ord, v6tstf.mbm,v6tstf.fso where bid='BM' and sord=vord and bprod=sprod group by sord,bprod,bchld)            
, cmf as (select  mbm.*, cfcst from v6tstf.v_cmf0002,mbm where bchld=cfprod)            
            
select fso_ith.*, cmf.*, from cmf full join fso_ith on sprod=bprod and tprod=bchld                                                
得到结果：                                                

    SORD（工单号）    SPROD（成品代码）    SQREQ    TPROD(材料代码)    TPRIC    TSCST    TQTY    SORD_1    BPROD    BCHLD    BQREQ    CFCST
    1001     GPA81020011      2.00      01636000602      -        901.11      -2.00      101,129.00      GPA81020047      01636000602      1.00      831.79
    1001     GPA81020011      2.00      01644000502      419.75      419.74      -16.00      101,129.00      GPA81020047      01644000502      8.00      349.75
    1001     GPA81020011      2.00      01644002802      -        361.40      -6.00      101,129.00      GPA81020047      01644002802      3.00      349.72
                                                
希望得到的结果：                                                
合同号    SORD（工单号）    SPROD（成品代码）    SQREQ    TPROD    TPRIC    TSCST    TQTY    SORD_1    BPROD    BCHLD    BQREQ    CFCST
H001    1001     GPA81020011      2.00      01636000602      -        901.11      -2.00      101,129.00      GPA81020047      01636000602      1.00      831.79
H001    1001     GPA81020011      2.00      01644000502      419.75      419.74      -16.00      101,129.00      GPA81020047      01644000502      8.00      349.75
H001    1001     GPA81020011      2.00      01644002802      -        361.40      -6.00      101,129.00      GPA81020047      01644002802      3.00      349.72
H001    1002                                            
H001    1002                                            
H001    1002                                            
H002    1003                                            
H002    1003                                            
H002    1003                                            
H002    1004                                            
H002    1004                                            
H002    1004                                            
H002    1005                                            
H002    1005                                            
H002    1005                                            


[ 本帖最后由 bigfire88 于 2010-5-28 07:21 编辑 ]