求助：如何能让两张表合起来？
合同分析.rar
(3.21 KB)
请各位高手帮助，不胜感激！
工单对应合同号:
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 编辑 ]
