22,300
社区成员




with table1 as
(
select '2015/11/01' 日期, '入库' 类型, 150 数量, 11.00 单价, 1650.00 金额 union all
select '2015/11/02' 日期, '出库' 类型, -60 数量, 11.00 单价, -660.00 金额 union all
select '2015/11/03' 日期, '入库' 类型, 30 数量, 12.00 单价, 360.00 金额 union all
select '2015/11/04' 日期, '入库' 类型, 50 数量, 13.00 单价, 650.00 金额 union all
select '2015/11/05' 日期, '出库' 类型, -60 数量, 11.76 单价, -705.88 金额
)
select 类型, 数量, 单价, 金额
, round((select case when SUM(数量) <> 0 then SUM(金额)/SUM(数量) end from table1 b where b.日期<=a.日期), 2) 加权平均成本
, (select SUM(数量) from table1 b where b.日期<=a.日期) 结余数量
, (select SUM(金额) from table1 b where b.日期<=a.日期) 结余金额
from table1 a
create table #T (
ID INT Identity,
类型 nvarchar(10),
数量 int,
单价 money,
金额 money
)
insert into #T
select N'入库', 150, 11.00, 1650.00
union all
select N'出库', -60, 11.00, -660.00
union all
select N'入库', 30, 12.00, 360.00
union all
select N'入库' , 50, 13.00 , 650.00
union all
select N'出库', -60, 11.76 , -705.88
SELECT * FROM #T t
CROSS APPLY
(
SELECT CAST(SUM(金额)/SUM(数量) AS DECIMAL(18,2)) AS [加权平均成本], SUM(数量) AS 结余数量, SUM(金额) AS 结余金额
FROM #T WHERE t.id>=id
) cat
create table #a (
类型 varchar(10),
数量 int,
单价 dec(18,2),
金额 dec(18,2)
)
insert into #a
select '入库', 150, 11.00, 1650.00
union all
select '出库', -60, 11.00, -660.00
union all
select '入库', 30, 12.00, 360.00
union all
select '入库' , 50, 13.00 , 650.00
union all
select '出库', -60, 11.76 , -705.88
select sum(金额)/sum(数量) as'加权平均成本' , sum(数量) as '结余数量',sum(金额) as '结余金额' from #a
加权平均成本 结余数量 结余金额
------------------ ---------------------------------------
11.764727 110 1294.12
(1 行受影响)