要做一个查询,五个表连接,既有内连接,又有外连接,该怎么写??SELECTc.cCusAbbName单位名称,sum(CASEWHENDLS.cInvCodeLIKE'21%'THENDLS.iQuantityEND)发货数量,DLS.iTaxUnitPrice...
要做一个查询,五个表连接,既有内连接,又有外连接,该怎么写??
SELECT c.cCusAbbName 单位名称,
sum(CASE WHEN DLS.cInvCode LIKE '21%' THEN DLS.iQuantity END) 发货数量,
DLS.iTaxUnitPrice 价格,sum(DLS.iNatSum) 总金额,
SUM(CASE WHEN DLS.cInvCode LIKE '20%' THEN DLS.iNatSum END)包装桶费用/*,
SUM(CASE WHEN SPVS.cExpCode IN ('001','005','006') THEN SPVS.INatMoney END )运费,
(sum(DLS.iNatSum)-SUM(CASE WHEN DLS.cInvCode LIKE '20%' THEN DLS.iNatSum END)-SUM(CASE WHEN SPVS.cExpCode IN ('001','005','006') THEN SPVS.INatMoney END ))/sum(DLS.iQuantity) 净水价格,
SUM(ACB.iAmount) 实收货款,'' 余额, ''备注*/
FROM DISPATCHLIST DL
INNER JOIN DISPATCHLISTS DLS ON DL.DLID=DLS.DLID
INNER JOIN CUSTOMER C ON C.cCusCode=DL.cCusCode
LEFT OUTER JOIN SalePayVouch SPV ON DL.cCusCode=SPV.cCusCode
INNER JOIN SalePayVouchS SPVS ON SPVS.ID=SPV.ID
LEFT OUTER JOIN AP_CLOSEBILL ACB ON DL.cCusCode =ACB.cDwCode
group by c.cCusAbbName,DLS.iTaxUnitPrice
order by c.cCusAbbName
单位名称,发货数量,价格,总金额为内连接,其他需要左连接
LEFT OUTER JOIN SalePayVouch SPV ON DL.cCusCode=SPV.cCusCode
INNER JOIN SalePayVouchS SPVS ON SPVS.ID=SPV.ID
我想让SalePayVouch与SalePayVouchs建立内连接。但是老是不是想要的结果。
展开