left join

--- 库存结存查询记录数，总数量和总重量Sql:
select COUNT(*) as recordCount,
SUM(BB.inQuantity) AS inQuantity,
SUM(BB.inWieght) AS inWieght,
SUM(BB.outQuantity) AS outQuantity,
SUM(BB.outWieght) AS outWieght,
SUM(BB.lockQuantity) AS lockQuantity,
SUM(BB.lockWeight) AS lockWeight,
SUM(BB.stockTakingQuantity) AS stockTakingQuantity,
SUM(BB.stockTakingWeight) AS stockTakingWeight,
SUM(BB.STARTNUMBER) AS startNumber,
SUM(BB.STARTWEIGHT) AS startWeight
from (SELECT A.id,
A.departmentName,
A.storeName,
A.companyName,
A.businessName,
A.stockType,
A.product,
A.manufactory,
A.material,
A.specString,
A.REFATTRIBUTES,
A.STARTNUMBER,
A.STARTWEIGHT,
A.weightUinit,
A.numberUinit,
A.locationName,
A.deptCode,
SUM(B.inQuantity) AS inQuantity,
SUM(B.inWieght) AS inWieght,
SUM(B.outQuantity) AS outQuantity,
SUM(B.outWieght) AS outWieght,
SUM(B.lockQuantity) AS lockQuantity,
SUM(B.lockWeight) AS lockWeight,
SUM(B.stockTakingQuantity) AS stockTakingQuantity,
SUM(B.stockTakingWeight) AS stockTakingWeight
FROM [color=blue]STOCKQUANTITYITEM_VIEW A[/color] LEFT JOIN [color=green]ALL_INFO_VIEW B[/color] ON A.id = B.id
[color=red] and B.dateStr <= '2014-06-30'[/color] WHERE

A.deptCode LIKE '001%'
and A.warehouseID = 1428
GROUP BY A.departmentName,
A.storeName,
A.companyName,
A.businessName,
A.stockType,
A.product,
A.manufactory,
A.material,
A.specString,
A.REFATTRIBUTES,
A.STARTNUMBER,
A.STARTWEIGHT,
A.id,
A.weightUinit,
A.numberUinit,
A.locationName,
A.deptCode) BB


红色部分放置的位置不一样造成数据的结果也会不一样，放在上面的位置上，如果左边有，右边没有，则会保留左边的内容


--- 库存结存查询记录数，总数量和总重量Sql:
select COUNT(*) as recordCount,
SUM(BB.inQuantity) AS inQuantity,
SUM(BB.inWieght) AS inWieght,
SUM(BB.outQuantity) AS outQuantity,
SUM(BB.outWieght) AS outWieght,
SUM(BB.lockQuantity) AS lockQuantity,
SUM(BB.lockWeight) AS lockWeight,
SUM(BB.stockTakingQuantity) AS stockTakingQuantity,
SUM(BB.stockTakingWeight) AS stockTakingWeight,
SUM(BB.STARTNUMBER) AS startNumber,
SUM(BB.STARTWEIGHT) AS startWeight
from (SELECT A.id,
A.departmentName,
A.storeName,
A.companyName,
A.businessName,
A.stockType,
A.product,
A.manufactory,
A.material,
A.specString,
A.REFATTRIBUTES,
A.STARTNUMBER,
A.STARTWEIGHT,
A.weightUinit,
A.numberUinit,
A.locationName,
A.deptCode,
SUM(B.inQuantity) AS inQuantity,
SUM(B.inWieght) AS inWieght,
SUM(B.outQuantity) AS outQuantity,
SUM(B.outWieght) AS outWieght,
SUM(B.lockQuantity) AS lockQuantity,
SUM(B.lockWeight) AS lockWeight,
SUM(B.stockTakingQuantity) AS stockTakingQuantity,
SUM(B.stockTakingWeight) AS stockTakingWeight
FROM[color=blue] STOCKQUANTITYITEM_VIEW A[/color] LEFT JOIN[color=green] ALL_INFO_VIEW B[/color] ON A.id = B.id

WHERE [color=red]B.dateStr <= '2014-06-30'
and[/color] A.deptCode LIKE '001%'
and A.warehouseID = 1428
GROUP BY A.departmentName,
A.storeName,
A.companyName,
A.businessName,
A.stockType,
A.product,
A.manufactory,
A.material,
A.specString,
A.REFATTRIBUTES,
A.STARTNUMBER,
A.STARTWEIGHT,
A.id,
A.weightUinit,
A.numberUinit,
A.locationName,
A.deptCode) BB
如果红色部分放在上面的位置，如果左边有，右边没有，则不会保留左边的内容