表说明:
tbGoods 货品基础信息表
tbDepotInOutAccount 出入库记录表,InQuantity表示入库,OutQuantity表示出库,CreateDate表示出入库日期我利用下列简单的分组合计语句,得出了货品的"本期入库,本期出库,期末结转(入-出)",周期为一个月.
可库存汇总帐一般还有个"上期结转"数据,请问如何修改SQL语句,得到出入库时间:tbDepotInOutAccount.CreateDate<2009-12-1之前的出入库差额数据.SELECT dbo.tbGoods.ID, dbo.tbGoods.Name, SUM(dbo.tbDepotInOutAccount.InQuantity)
AS InQty, SUM(dbo.tbDepotInOutAccount.OutQuantity) AS OutQty,
SUM(dbo.tbDepotInOutAccount.InQuantity)- SUM(dbo.tbDepotInOutAccount.OutQuantity) AS CurQty
FROM dbo.tbGoods RIGHT OUTER JOIN
dbo.tbDepotInOutAccount ON
dbo.tbGoods.ID = dbo.tbDepotInOutAccount.GoodsID
WHERE (dbo.tbDepotInOutAccount.CreateDate BETWEEN '2009-12-1' AND '2009-12-31')
GROUP BY dbo.tbGoods.ID, dbo.tbGoods.Name
多谢!
tbGoods 货品基础信息表
tbDepotInOutAccount 出入库记录表,InQuantity表示入库,OutQuantity表示出库,CreateDate表示出入库日期我利用下列简单的分组合计语句,得出了货品的"本期入库,本期出库,期末结转(入-出)",周期为一个月.
可库存汇总帐一般还有个"上期结转"数据,请问如何修改SQL语句,得到出入库时间:tbDepotInOutAccount.CreateDate<2009-12-1之前的出入库差额数据.SELECT dbo.tbGoods.ID, dbo.tbGoods.Name, SUM(dbo.tbDepotInOutAccount.InQuantity)
AS InQty, SUM(dbo.tbDepotInOutAccount.OutQuantity) AS OutQty,
SUM(dbo.tbDepotInOutAccount.InQuantity)- SUM(dbo.tbDepotInOutAccount.OutQuantity) AS CurQty
FROM dbo.tbGoods RIGHT OUTER JOIN
dbo.tbDepotInOutAccount ON
dbo.tbGoods.ID = dbo.tbDepotInOutAccount.GoodsID
WHERE (dbo.tbDepotInOutAccount.CreateDate BETWEEN '2009-12-1' AND '2009-12-31')
GROUP BY dbo.tbGoods.ID, dbo.tbGoods.Name
多谢!
就是说,默认上期结转为12-1之前的(入-出)数据,SUM(dbo.tbDepotInOutAccount.InQuantity)- SUM(dbo.tbDepotInOutAccount.OutQuantity
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281
多谢各位了.
继续等SQL高手...
AS InQty, SUM(dbo.tbDepotInOutAccount.OutQuantity) AS OutQty,
SUM(dbo.tbDepotInOutAccount.InQuantity)- SUM(dbo.tbDepotInOutAccount.OutQuantity) AS CurQty ,
(select SUM(dbo.tbDepotInOutAccount.InQuantity)- SUM(dbo.tbDepotInOutAccount.OutQuantity)from tbDepotInOutAccount where dbo.tbDepotInOutAccount.CreateDate < '2009-12-01') as colname
FROM dbo.tbGoods RIGHT OUTER JOIN
dbo.tbDepotInOutAccount ON
dbo.tbGoods.ID = dbo.tbDepotInOutAccount.GoodsID
WHERE (dbo.tbDepotInOutAccount.CreateDate BETWEEN '2009-12-1' AND '2009-12-31')
GROUP BY dbo.tbGoods.ID, dbo.tbGoods.Name
AS InQty, SUM(dbo.tbDepotInOutAccount.OutQuantity) AS OutQty,
SUM(dbo.tbDepotInOutAccount.InQuantity)- SUM(dbo.tbDepotInOutAccount.OutQuantity) AS CurQty ,
(select SUM(dbo.tbDepotInOutAccount.InQuantity)- SUM(dbo.tbDepotInOutAccount.OutQuantity)from tbDepotInOutAccount where a.goodsid = goodsid and CreateDate < '2009-12-01') as colname
FROM dbo.tbGoods RIGHT b OUTER JOIN
dbo.tbDepotInOutAccount a ON
dbo.tbGoods.ID = dbo.tbDepotInOutAccount.GoodsID
WHERE (dbo.tbDepotInOutAccount.CreateDate BETWEEN '2009-12-1' AND '2009-12-31')
GROUP BY dbo.tbGoods.ID, dbo.tbGoods.Name 加个条件
SELECT 子句错误:'.' 附近的表达式。
丢失 FROM 子句。
GROUP BY 子句错误。
无法对查询文本做语法分析。
from master..sysdatabases
select *
from csdn_test..sysobjects
SELECT b.ID, b.Name, SUM(a.InQuantity)
AS InQty, SUM(a.OutQuantity) AS OutQty,
SUM(a.InQuantity)- SUM(a.OutQuantity) AS CurQty,
(select SUM(InQuantity)- SUM(OutQuantity)
from dbo.tbDepotInOutAccount
where a.goodsid = goodsid and CreateDate < '2009-12-01') as colname
FROM dbo.tbGoods b RIGHT OUTER JOIN
dbo.tbDepotInOutAccount a ON
b.ID = a.GoodsID
WHERE (a.CreateDate BETWEEN '2009-12-1' AND '2009-12-31')
GROUP BY b.ID, b.Name
case tbDepotInOutAccount.CreateDate when '2009-12-01' then ....
不然的话你要得出上期结转 必须把所有日期的出入库数量进行加减
比如每到一个周期的时候,就统计本月结存,生成一条结转数据后再写入数据表,要用的时候直接调取就可以了,这样的好处就是比较快,但是缺点就是前期基础数据一旦改变后就郁闷了。
SUM(a.InQuantity) - SUM(a.OutQuantity) AS CurQty,
(SELECT SUM(InQuantity) - SUM(OutQuantity)
FROM dbo.tbGoods b RIGHT OUTER JOIN
dbo.tbDepotInOutAccount a ON b.ID = a.GoodsID
WHERE a.CreateDate < '2009-12-25') AS colname
FROM dbo.tbGoods b RIGHT OUTER JOIN
dbo.tbDepotInOutAccount a ON b.ID = a.GoodsID
WHERE (a.CreateDate BETWEEN '2009-12-1' AND '2009-12-31')
GROUP BY b.ID, b.Name
上述调试能过,但
colname中的数据全是一个数,是不是没有分组?