7个表联动查询想请教一下!

[zhang123shan]

Checkresult_one '一检不良品
PlanCode ColorType SizeCode wj fz mc sc ps zt fs wg
1004110008 31 13 8 0 0 0 0 0 0 0
1004110008 31 11 1 0 0 0 0 0 0 1
1004110008 51 9 2 4 0 0 0 0 0 0
1004120001 A F 1
1004120002 BK F 1


CHECKRESULT_TWO ‘二检不良品
PlanCode ColorType SizeCode wj fz mc ps fs
1004110008 31 11 1 0 0 0 0
1004110008 51 9 0 1 0 0 0
1004120001 A F 1
1004120002 BK F 1


checkpinresult ’检针不良品
PlanCode coloretype sizecode quantity
1004110008 51 9 1
1004110008 49 11 1
1004120003 WH F 1


Checkresult_two_back ‘不良品返修
Plancode colortype sizecode quantity
1004110008 31 13 8
1004110008 31 11 3
1004110008 51 9 6
1004120001 A F 1
1004120002 BK F 1


boxdata ’工厂入货数
plancode boxnumber colortype sizecode quantity
1004110008 1 31 13 22
1004110008 2 31 11 20
1004110008 3 51 9 22
1004120001 1 A F 10
1004120002 1 BK F 10
1003120003 1 WH F 10

boxcheckdata ‘实入货数
plancode boxnumber colortype sizecode quantity
1004110008 1 31 13 22
1004110008 2 31 11 20
1004110008 3 51 9 21
1004120001 1 A F 10
1004120002 1 BK F 11
1003120003 1 WH F 10

Checkplan ’计划
plancode clientname factoryname worktype workname worknumber workcontent date
1004110008 倉敷紡績㈱川原様 倉紡 MW11-0371 上衣 390 0 2010/4/1
1004120001 倉敷紡績㈱川原様 倉紡 MW17-0302 上衣 2180 10 2010/4/10
1004120002 ㈱ブルーメイト杉原様 禾弘 17054K 裤子 106 2 2010/4/3
1003120003 ㈱ギャレット福武様 盛鑫 2173618 帽子 30 0 2010/3/3

[永生天地]

比较麻烦，不过还不知道要怎么做

[zhang123shan]

想得到如下结果用什么方法简单点，我不想一个表一个表的处理
客户名 工厂 作业 品番 入库数 异差 实入数 一次检品良品 一次不良品数 wj fz mc sc ps zt fs wg 再检数 再检良品 检针不良 最终良品
倉敷紡績㈱川原様 倉紡 检品 MW11-0371 64 -1 63 47 16 11 4 1 18 17 2 60

-------------------------------‘
条件说明
最终良品=实入数-（一检不良+二检不良+检针不良）+检品返修
CHECKPLAN中的workcontent值为0是为检品1时为检针，3时为检品检针，2时为抽检）

[zhang123shan]

[Quote=引用 1 楼 xys_777 的回复:]
比较麻烦，不过还不知道要怎么做
[/Quote]
是的，我自己写的执行效率不高，方法比较笨
所以来请教大家

[shizheyangde]

不明白什么意思，等高手

[zhang123shan]

select
checkplan.plancode,checkplan.clientname,checkplan.factoryname,
tab_codereport.ReportName,
checkplan.worktype,workcontent,
boxdata.Plsu,boxcheckdata.quantity-boxdata.Plsu as yc ,boxcheckdata.quantity,
c.wj,c.fz,c.mc,c.ps,c.zt,c.fs,c.wg,
(isnull(checkresult_two_back.fanxu,0)+isnull(checkresult_three_back.fanxu2,0)) FanXu,
(isnull(checkresult_two_back.fanxu,0)+isnull(checkresult_three_back.fanxu2,0))-(isnull(checkresult_two.quantity,0)+isnull(checkresult_three.quantity,0)) Lp,
(isnull(checkresult_two.quantity,0)+isnull(checkresult_three.quantity,0)) as zajian,
isnull(checkpinresult.quantity,0) as JianZhen
from checkplan
left join(select ('SH'+LEFT(tab_codereport.CODEREPORT,2)+'-'+left(RIGHT(tab_codereport.codereport,6),2)+'-'+right(tab_codereport.codereport,4)) ReportName,createtime,plancode from tab_codereport
group by plancode,codereport,createtime)TAB_codereport
on tab_codereport.plancode=checkplan.plancode
left join (select plancode,sum(quantity)PLsu from boxdata group by plancode) boxdata----boxdata
on boxdata.plancode=checkplan.plancode
left join(select plancode,sum(wj) wj,sum(fz) fz ,sum(mc) mc ,sum(ps) ps,sum(zt) zt, sum(fs) fs,sum(wg) wg from checkresult_one group by plancode)c--/*checkresule_one*/
on c.plancode=checkplan.plancode
left join (select plancode,sum(quantity) quantity from boxcheckdata group by plancode) boxcheckdata-- boxcheckdata
on boxcheckdata.plancode=checkplan.plancode
left join (select checkresult_two_back.plancode,sum(checkresult_two_back.quantity) Fanxu from
checkresult_two_back group by checkresult_two_back.plancode)checkresult_two_back
on checkresult_two_back.plancode=checkplan.plancode
left join (select checkresult_three_back.plancode,sum(checkresult_three_back.quantity) Fanxu2 from
checkresult_three_back group by checkresult_three_back.plancode)checkresult_three_back
on checkresult_three_back.plancode=checkplan.plancode
left join(select plancode,sum(wj+fz+mc+ps+fs)quantity from checkresult_two group by plancode)checkresult_two
on checkresult_two.plancode=checkplan.plancode
left join(select plancode,sum(wj+fz+mc)quantity from checkresult_three group by plancode )checkresult_three
on checkresult_three.plancode=checkplan.plancode
left join (select plancode,sum(quantity)quantity from checkpinresult group by plancode)checkpinresult
on checkpinresult.plancode=checkplan.plancode

where checkplan.plancode='1003230011'
--自己写完了！就是有点烦
----------------------------------------------------------------------------------------
结果：

1003230011 タキヒョー㈱212課石川様 美梭 SH10-04-0003 35010 0 2476 16 2492 27 148 14 1 0 86 74 346 310 36 5

[dichun]

看了半天，觉得，可以通过前台应用程序，选择每个表关联，拉控件，绑定一下，不需要谢任何代码；

[dawugui]

你这七个表看懂了就天亮了,帮顶.