多张表的左联接查询

[zy5251zd_net]

要求：添加查询列，
rdrecords.iQuantity as 入库数量,RdRecord.dDate as 入库时间,dispatchlist.dDate as 发货时间,
sum(rdrecords.iQuantity) as 累计入库数量
这是一个视图的查询语句，需添加以上四个数据列，分别在表rdrecords收发记录子表，rdrecord收发记录主表，dispatchlist发货单主表，
条件：rdrecord.id=rdrecords.id;dispatchlist.cdlcode=so_somain.cdlcode
其中cdlcode为销售订单号，so_somain.cdlcode指销售订单主表中的销售订单号原有代码：SELECT SO_SOMain.cSOCode AS 订单号,SO_SOMain.dDate AS 受订日期 , SO_SOMain.cBusType as 业务类型 ,SO_SOMain.cSTCode as 销售类型编号,
Customer.cCCCode as 客户类编码, Customer.cCuscode AS 客户编码,Customer.cCusAbbName AS 客户简称 , Customer.cCusName as 客户,
Department.cDepName AS 部门, Person.cPersonName AS 业务员,convert(varchar(30),Ap_Order.iAmount_f) as 订单预收款原币, convert(varchar(30),
Ap_Order.iAmount) as 订单预收款本币, convert(varchar(30), Ap_Order.iRAmount_f) as 订单预收余额原币, convert(varchar(30),
Ap_Order.iRAmount) as 订单预收余额本币, SO_SOMain.cCusOAddress AS 发货地址, SO_SOMain.cexch_name AS 外币名称,
SO_SOMain.iExchRate AS 外币汇率, SO_SOMain.iTaxRate AS 税率, SO_SOMain.cMemo AS 备注, SO_SOMain.cMaker AS 制单人,
SO_SOMain.cVerifier as 审核人, dbo.SO_SOMain.cCloser AS 关闭人, SO_SOMain.cDefine1, SO_SOMain.cDefine2, SO_SOMain.cDefine3,
SO_SOMain.cDefine4, SO_SOMain.cDefine5, SO_SOMain.cDefine6, SO_SOMain.cDefine7, SO_SOMain.cDefine8, SO_SOMain.cDefine9,
SO_SOMain.cDefine10,SO_SOMain.cDefine11,SO_SOMain.cDefine12,SO_SOMain.cDefine13,SO_SOMain.cDefine14,SO_SOMain.cDefine15,
SO_SOMain.cDefine16,Inventory.cInvCCode as 存货类编码,case when Inventory.bService=1 then '是' else '否' end as 是否劳务,
Inventory.cInvCode as 存货编码,Inventory.cInvaddCode as 存货代码,Inventory.cInvName as 存货,Inventory.cInvStd as 规格,
SO_SODetails.cFree1,SO_SODetails.cFree2,SO_SODetails.dPreDate as 预发货日期,SO_SODetails.iQuantity as 数量,SO_SODetails.iNum AS 件数,
SO_SODetails.iTaxUnitPrice AS 含税单价,SO_SODetails.iUnitPrice AS 无税单价,SO_SODetails.iMoney AS 无税金额,SO_SODetails.iTax AS 税额,
SO_SODetails.iSum AS 价税合计, SO_SODetails.iDisCount AS 折扣, SO_SODetails.iNatUnitPrice AS 本币单价, SO_SODetails.iNatMoney AS 本币金额,
SO_SODetails.iNatTax AS 本币税额, SO_SODetails.iNatSum AS 本币价税合计, SO_SODetails.iNatDisCount AS 本币折扣,
SO_SODetails.iFHQuantity AS 累计发货数量, SO_SODetails.iFHNum AS 累计发货件数, SO_SODetails.iQuantity - isnull(iFHQuantity,0) as 未发货数量,
SO_SODetails.iFHMoney AS 累计发货金额, SO_SODetails.iSum-isnull(iFHMoney,0) as 未发货金额, SO_SODetails.iKPQuantity AS 累计开票数量,
SO_SODetails.iKPNum AS 累计开票件数, SO_SODetails.iKPMoney AS 累计开票金额, SA_BillSK.iExchSum AS 累计收款原币 ,
SA_BillSK.iMoneySum AS 累计收款, cccc.iFoNum as 累计出库件数,cccc.iFoQuantity as 累计出库数量,ddd.FTotalPrice as 累计出库金额,
sumPOMain.fquantity as 生产下单数量, sumPOMain.finquantity as 产成品入库数量, Convert ( varchar (20) , SO_SOMAIN.ID ) as ID,
customer.ccusdefine1,customer.ccusdefine2,customer.ccusdefine3,customer.ccusdefine4,customer.ccusdefine5,customer.ccusdefine6,
customer.ccusdefine7,customer.ccusdefine8,customer.ccusdefine9,customer.ccusdefine10,customer.ccusdefine11,customer.ccusdefine12,
customer.ccusdefine13,customer.ccusdefine14,customer.ccusdefine15,customer.ccusdefine16,SO_SODetails.cfree3,SO_SODetails.cfree4,
SO_SODetails.cfree5,SO_SODetails.cfree6,SO_SODetails.cfree7,SO_SODetails.cfree8,SO_SODetails.cfree9,SO_SODetails.cfree10,
inventory.cinvdefine1,inventory.cinvdefine2,inventory.cinvdefine3,inventory.cinvdefine4,inventory.cinvdefine5,inventory.cinvdefine6,inventory.cinvdefine7,
inventory.cinvdefine8,inventory.cinvdefine9,inventory.cinvdefine10,inventory.cinvdefine11,inventory.cinvdefine12,inventory.cinvdefine13,
inventory.cinvdefine14,inventory.cinvdefine15,inventory.cinvdefine16,SO_SODetails.cdefine22,SO_SODetails.cdefine23,SO_SODetails.cdefine24,
SO_SODetails.cdefine25,SO_SODetails.cdefine26,SO_SODetails.cdefine27,SO_SODetails.cdefine28,SO_SODetails.cdefine29,SO_SODetails.cdefine30,
SO_SODetails.cdefine31,SO_SODetails.cdefine32,SO_SODetails.cdefine33,SO_SODetails.cdefine34,SO_SODetails.cdefine35,SO_SODetails.cdefine36,
SO_SODetails.cdefine37 from Inventory
LEFT OUTER JOIN SO_SODetails ON inventory.cInvCode = SO_SODetails.cInvCode
RIGHT OUTER JOIN Customer
RIGHT OUTER JOIN SO_SOMain
LEFT OUTER JOIN Department ON SO_SOMain.cDepCode = Department.cDepCode ON Customer.cCusCode = SO_SOMain.cCusCode
LEFT OUTER JOIN Person ON SO_SOMain.cPersonCode = Person.cPersonCode ON SO_SODetails.cSOCode = SO_SOMain.cSOCode
LEFT OUTER JOIN Ap_Order ON SO_SOMain.cSOCode=Ap_Order.cOrderID AND AP_ORDER.CFLAG='AR'
LEFT JOIN sumPOMain ON sumPOMain.csosids like '%,' + cast(so_sodetails.isosid as varchar(30)) +',%' and sumPOMain.cinvcode=so_sodetails.cinvcode
LEFT JOIN SA_SumOutQuantityNum_NS cccc ON cccc.iSOsID = SO_SODetails.iSOsID
LEFT JOIN SA_SumOutPrice_NS as ddd ON ddd.iSOsID = SO_SODetails.iSOsID
LEFT OUTER JOIN SA_BillSK ON SA_BillSK.iSOsID = SO_SODetails.iSOsID

[-晴天]

好复杂...
看得眼都花了.

[AcHerat]

比较复杂啊！left join时如果上下表都和中间表有关系注意添加left join中间表的顺序，其他楼主按条件把表放上去，查字段吧！

[--小F--]

[Quote=引用 1 楼 qianjin036a 的回复:]
好复杂...
看得眼都花了.
[/Quote]

[zy5251zd_net]

其实具体内容不需要知道，只需要把它切割成
select 列名 from 表名 from Inventory
LEFT OUTER JOIN SO_SODetails ON inventory.cInvCode = SO_SODetails.cInvCode
RIGHT OUTER JOIN Customer
RIGHT OUTER JOIN SO_SOMain
LEFT OUTER JOIN Department ON SO_SOMain.cDepCode = Department.cDepCode ON Customer.cCusCode = SO_SOMain.cCusCode
LEFT OUTER JOIN Person ON SO_SOMain.cPersonCode = Person.cPersonCode ON SO_SODetails.cSOCode = SO_SOMain.cSOCode
LEFT OUTER JOIN Ap_Order ON SO_SOMain.cSOCode=Ap_Order.cOrderID AND AP_ORDER.CFLAG='AR'
LEFT JOIN sumPOMain ON sumPOMain.csosids like '%,' + cast(so_sodetails.isosid as varchar(30)) +',%' and sumPOMain.cinvcode=so_sodetails.cinvcode
LEFT JOIN SA_SumOutQuantityNum_NS cccc ON cccc.iSOsID = SO_SODetails.iSOsID
LEFT JOIN SA_SumOutPrice_NS as ddd ON ddd.iSOsID = SO_SODetails.iSOsID
LEFT OUTER JOIN SA_BillSK ON SA_BillSK.iSOsID = SO_SODetails.iSOsID
这样就不会眼花了，最主要是后面的连接语句我不知道该如何下手，还请指教