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 STOCKQUANTITYITEM_VIEW A          LEFT  JOIN ALL_INFO_VIEW B ON A.id = B.id
          and B.dateStr <= '2014-06-30'         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 STOCKQUANTITYITEM_VIEW A          LEFT  JOIN ALL_INFO_VIEW B ON A.id = B.id
           
         WHERE B.dateStr <= '2014-06-30'
and           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
如果红色部分放在上面的位置，如果左边有，右边没有，则不会保留左边的内容