SQL实时库存数量结存

SELECT dbo.t_StockBase.物料编号

           ,dbo.t_StockBase.物料名称

           ,dbo.t_StockBalance.期初辅助数量

           ,dbo.t_StockBalance.期初数量

           , isnull(a.入库辅助数量,0)as 入库辅助数量

           ,isnull(a.入库数量,0) as 入库数量

           ,isnull(b.出库辅助数量,0)as 出库辅助数量

           ,isnull(b.出库数量,0)as 出库数量

FROM  dbo.t_StockBase

  LEFT  JOIN   dbo.t_StockBalance

    ON   dbo.t_StockBase.物料编号 = dbo.t_StockBalance.物料代码 

  LEFT  JOIN (SELECT t_StockInEntry

                                .物料编码

                                ,SUM(ISNULL(dbo.t_StockInEntry.辅助数量,0)) as 入库辅助数量

                                ,SUM(ISNULL(dbo.t_StockInEntry.数量,0)) as 入库数量
                      FROM t_StockInEntry

                       inner join t_StockInMain

                           on  t_StockInEntry.入库单编号=t_StockInMain.入库单编号 
                     WHERE t_StockInMain.入库日期 between '2010-01-01'and '2010-01-31'

                                 AND t_StockInMain.FYOrNDel<>1

                      GROUP BY  t_StockInEntry.物料编码

                                       ,t_StockInMain.入库日期)as a

           on dbo.t_StockBase.物料编号 = a.物料编码

  LEFT JOIN (SELECT t_StockOutEntry.物料编码

                             ,SUM(ISNULL(dbo.t_StockOutEntry.辅助数量,0)) as 出库辅助数量

                             ,SUM(ISNULL(dbo.t_StockOutEntry.数量,0)) as 出库数量
                    FROM t_StockOutEntry

                   inner join t_StockOutMain

                           ON  t_StockOutEntry.出库单编号=t_StockOutMain.出库单编号 
                   WHERE t_StockOutMain.出库日期 between '2010-01-01'and '2010-01-31'

                           AND t_StockOutMain.FYOrNDel<>1

                   group by t_StockOutEntry.物料编码

                                 ,t_StockOutMain.出库日期 ) as b
             ON dbo.t_StockBase.物料编号 = b.物料编码