IF OBJECT_ID('test') is not null drop table testCREATE TABLE test (FDATE DATETIME,FNumber VARCHAR(40),FQty INT)INSERT test SELECT '2009-01-07','001',200IF OBJECT_ID('tb') is not null drop table tbCREATE TABLE tb (FDATE DATETIME,FNumber VARCHAR(40),FRQty INT)INSERT tb SELECT '2009-01-06','001',20 UNION ALL SELECT '2009-01-05','001',10 UNION ALL SELECT '2009-01-04','001',30 UNION ALL SELECT '2009-01-03','001',20 IF OBJECT_ID('ta') is not null drop table taCREATE TABLE ta (FDATE DATETIME,FNumber VARCHAR(40),FSQty INT)INSERT ta SELECT '2009-01-06','001',10 UNION ALL SELECT '2009-01-05','001',20 UNION ALL SELECT '2009-01-04','001',30 UNION ALL SELECT '2009-01-03','001',50 IF OBJECT_ID('f_FQty') IS NOT NULL DROP FUNCTION f_FQtyCREATE FUNCTION f_FQty(@FNumber VARCHAR(40),@FDATE DATETIME) RETURNS TABLE AS RETURN (SELECT [产品]=a.FNumber,[库存]=a.FQty-b.FRQty+c.FSQty FROM test a LEFT OUTER JOIN (SELECT a.FNumber,[FRQty]=SUM(a.FRQty) FROM tb a WHERE FDate>=@FDATE AND a.FNumber=@FNumber GROUP BY a.FNumber) b ON a.FNumber=b.FNumber LEFT OUTER JOIN (SELECT a.FNumber,[FSQty]=SUM(a.FSQty) FROM ta a WHERE FDate>=@FDATE AND a.FNumber=@FNumber GROUP BY a.FNumber) c ON a.FNumber=c.FNumber )SELECT * FROM f_FQty('001','2009-01-06')
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281
日期,产品ID,库存 2009-1-7, 001,200表二
日期,产品ID,收到数量
2009-1-6,001,20
2009-1-5,001,10
2009-1-4,001,30
2009-1-3,001,20表二
日期,产品ID,支出数量 2009-1-6,001,10
2009-1-5,001,20
2009-1-4,001,30
2009-1-3,001,50
现在知道最后一天库存
怎样推出2009-12-3到2009-12-6每天的库存
。(根据收入,支出明细)
IF OBJECT_ID('test') is not null drop table testCREATE TABLE test
(FDATE DATETIME,FNumber VARCHAR(40),FQty INT)INSERT test
SELECT '2009-01-07','001',200IF OBJECT_ID('tb') is not null drop table tbCREATE TABLE tb
(FDATE DATETIME,FNumber VARCHAR(40),FRQty INT)INSERT tb
SELECT '2009-01-06','001',20 UNION ALL
SELECT '2009-01-05','001',10 UNION ALL
SELECT '2009-01-04','001',30 UNION ALL
SELECT '2009-01-03','001',20 IF OBJECT_ID('ta') is not null drop table taCREATE TABLE ta
(FDATE DATETIME,FNumber VARCHAR(40),FSQty INT)INSERT ta
SELECT '2009-01-06','001',10 UNION ALL
SELECT '2009-01-05','001',20 UNION ALL
SELECT '2009-01-04','001',30 UNION ALL
SELECT '2009-01-03','001',50 IF OBJECT_ID('f_FQty') IS NOT NULL DROP FUNCTION f_FQtyCREATE FUNCTION f_FQty(@FNumber VARCHAR(40),@FDATE DATETIME)
RETURNS TABLE
AS
RETURN
(SELECT [产品]=a.FNumber,[库存]=a.FQty-b.FRQty+c.FSQty
FROM test a
LEFT OUTER JOIN (SELECT a.FNumber,[FRQty]=SUM(a.FRQty) FROM tb a WHERE FDate>=@FDATE AND a.FNumber=@FNumber GROUP BY a.FNumber) b ON a.FNumber=b.FNumber
LEFT OUTER JOIN (SELECT a.FNumber,[FSQty]=SUM(a.FSQty) FROM ta a WHERE FDate>=@FDATE AND a.FNumber=@FNumber GROUP BY a.FNumber) c ON a.FNumber=c.FNumber
)SELECT * FROM f_FQty('001','2009-01-06')