22,209
社区成员
发帖
与我相关
我的任务
分享
if object_id('tempdb..#')is not null drop table #
create table #(库存日期 datetime, 本期入库 float, 本期出库 float)
insert # select '2008-09-04', 25000.00, 18350.00
insert # select '2008-09-05', 68264.00, 32080.00
insert # select '2008-09-07', 47767.00, 17666.00
insert # select '2008-09-08', 49509.70, 20129.00
insert # select '2008-09-09', 14849.00, 10226.00
insert # select '2008-09-11', 25364.00, 21125.00
SELECT
库存日期,
上期数量 = isnull((select sum (本期入库 - 本期出库) from # where 库存日期< a.库存日期 ),0),
本期入库,
本期出库,
本期结存 = isnull((select sum (本期入库 - 本期出库) from # where 库存日期< a.库存日期 ),0)+本期入库 - 本期出库
from # a
------------------------------------
-- Author: happyflsytone
-- Date:2008-10-19 10:44:57
------------------------------------
-- Test Data: ta
IF OBJECT_ID('ta') IS NOT NULL
DROP TABLE ta
Go
CREATE TABLE ta(库存日期 SMALLDATETIME,本期入库 NUMERIC(7,2),本期出库 NUMERIC(7,2))
Go
INSERT INTO ta
SELECT '2008-09-04',25000.00,18350.00 UNION ALL
SELECT '2008-09-05',68264.00,32080.00 UNION ALL
SELECT '2008-09-07',47767.00,17666.00 UNION ALL
SELECT '2008-09-08',49509.70,20129.00 UNION ALL
SELECT '2008-09-09',14849.00,10226.00 UNION ALL
SELECT '2008-09-11',25364.00,21125.00
GO
--Start
SELECT
库存日期,
上期数量 = isnull((select sum (本期入库 - 本期出库) from ta where 库存日期< a.库存日期 ),0),
本期入库,
本期出库,
本期结存 = isnull((select sum (本期入库 - 本期出库) from ta where 库存日期< a.库存日期 ),0)+本期入库 - 本期出库
from ta a
--Result:
/*
库存日期 上期数量 本期入库 本期出库 本期结存
----------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
2008-09-04 00:00:00 0.00 25000.00 18350.00 6650.00
2008-09-05 00:00:00 6650.00 68264.00 32080.00 42834.00
2008-09-07 00:00:00 42834.00 47767.00 17666.00 72935.00
2008-09-08 00:00:00 72935.00 49509.70 20129.00 102315.70
2008-09-09 00:00:00 102315.70 14849.00 10226.00 106938.70
2008-09-11 00:00:00 106938.70 25364.00 21125.00 111177.70
*/
--End
Select a.[库存日期],a.[本期入库],a.[本期出库],Sum(b.[本期入库])-Sum(b.[本期出库]) As [本期结存]
From @1 a
Inner Join @1 b On b.[库存日期]<=a.[库存日期]
Group By a.[库存日期],a.[本期入库],a.[本期出库]
Use Test
Go
--> --> (Andy)生成测试数据 2008-10-19
Set Nocount On
declare @1 table([库存日期] Datetime,[本期入库] decimal(18,2),[本期出库] decimal(18,2))
Insert @1
select '2008-09-04',25000.00,18350.00 union all
select '2008-09-05',68264.00,32080.00 union all
select '2008-09-07',47767.00,17666.00 union all
select '2008-09-08',49509.70,20129.00 union all
select '2008-09-09',14849.00,10226.00 union all
select '2008-09-11',25364.00,21125.00
Select a.[库存日期],a.[本期入库],a.[本期出库],Sum(b.[本期入库])-Sum(b.[本期出库]) As [本期结存]
From @1 a
Left Outer Join @1 b On b.[库存日期]<=a.[库存日期]
Group By a.[库存日期],a.[本期入库],a.[本期出库]
/*
库存日期 本期入库 本期出库 本期结存
----------------------- ---------------------------------------
2008-09-04 00:00:00.000 25000.00 18350.00 6650.00
2008-09-05 00:00:00.000 68264.00 32080.00 42834.00
2008-09-07 00:00:00.000 47767.00 17666.00 72935.00
2008-09-08 00:00:00.000 49509.70 20129.00 102315.70
2008-09-09 00:00:00.000 14849.00 10226.00 106938.70
2008-09-11 00:00:00.000 25364.00 21125.00 111177.70
*/
--> liangCK小梁 于2008-10-19
--> 生成测试数据: #T
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (库存日期 DATETIME,本期入库 NUMERIC(7,2),本期出库 NUMERIC(7,2))
INSERT INTO #T
SELECT '2008-09-04',25000.00,18350.00 UNION ALL
SELECT '2008-09-05',68264.00,32080.00 UNION ALL
SELECT '2008-09-07',47767.00,17666.00 UNION ALL
SELECT '2008-09-08',49509.70,20129.00 UNION ALL
SELECT '2008-09-09',14849.00,10226.00 UNION ALL
SELECT '2008-09-11',25364.00,21125.00
--SQL查询如下:
SELECT CONVERT(VARCHAR(10),a.库存日期,120) AS 库存日期,
ISNULL(b.上期数量,0) AS 上期数量,
a.本期入库,a.本期出库,
本期结存=ISNULL(b.上期数量,0)+a.本期入库-a.本期出库
FROM #T AS a
CROSS APPLY
(
SELECT SUM(本期入库-本期出库) AS 上期数量
FROM #T
WHERE 库存日期<a.库存日期
) AS b
/*
库存日期 上期数量 本期入库 本期出库 本期结存
---------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
2008-09-04 0.00 25000.00 18350.00 6650.00
2008-09-05 6650.00 68264.00 32080.00 42834.00
2008-09-07 42834.00 47767.00 17666.00 72935.00
2008-09-08 72935.00 49509.70 20129.00 102315.70
2008-09-09 102315.70 14849.00 10226.00 106938.70
2008-09-11 106938.70 25364.00 21125.00 111177.70
(6 行受影响)
*/