-->测试数据:
IF OBJECT_ID('[dbo].[products]') IS NOT NULL
DROP TABLE PRODUCTS
GO
CREATE TABLE [dbo].[products](
[id] INT IDENTITY(1,1) NOT NULL,
[p_id] INT NOT NULL, --产品编号
[p_tm] DATETIME NULL, --时间
[p_flag] INT NULL, --1表示期初值,表示增加,表示减少
[p_num] INT NULL, --数目
[p_price] MONEY NULL, --单价
[p_money] MONEY NULL, --金额
)
GO
INSERT [products]
SELECT 1,'2010-04-01',1,50,12.3,615 UNION ALL
SELECT 1,'2010-04-02',2,10,12.3,123 UNION ALL
SELECT 1,'2010-04-05',3,10,15.3,153 UNION ALL
SELECT 1,'2010-04-06',2,5,12.3,61.5 UNION ALL
SELECT 1,'2010-04-07',2,20,12.3,246 UNION ALL
SELECT 1,'2010-04-09',2,22,12.3,270.6 UNION ALL
SELECT 1,'2010-04-10',3,7,15.7,109.7 UNION ALL
SELECT 1,'2010-04-11',3,10,15.5,155
GO
-->SQL查询如下:
;WITH T AS
(
SELECT id, p_id, p_tm
,[期初数量] = CASE p_flag WHEN 1 THEN p_num ELSE 0 END
,[期初单价] = CASE p_flag WHEN 1 THEN p_price ELSE 0 END
,[期初金额] = CASE p_flag WHEN 1 THEN p_money ELSE 0 END
,[增加数量] = CASE p_flag WHEN 2 THEN p_num ELSE 0 END
,[增加单价] = CASE p_flag WHEN 2 THEN p_price ELSE 0 END
,[增加金额] = CASE p_flag WHEN 2 THEN p_money ELSE 0 END
,[减少数量] = CASE p_flag WHEN 3 THEN p_num ELSE 0 END
,[减少单价] = CASE p_flag WHEN 3 THEN p_price ELSE 0 END
,[减少金额] = CASE p_flag WHEN 3 THEN p_money ELSE 0 END
FROM products
)
SELECT a.id, a.p_id, a.p_tm
,[期初数量] = SUM(b.期初数量 + b.增加数量 - b.减少数量) - a.增加数量 + a.减少数量
,[期初单价] = (SUM(b.期初金额 + b.增加金额 - b.减少金额) - a.增加金额 + a.减少金额)
/(SUM(b.期初数量 + b.增加数量 - b.减少数量) - a.增加数量 + a.减少数量)
,[期初金额] = SUM(b.期初金额 + b.增加金额 - b.减少金额) - a.增加金额 + a.减少金额
,[增加数量] = a.[增加数量]
,[增加单价] = a.[增加单价]
,[增加金额] = a.[增加金额]
,[减少数量] = a.[减少数量]
,[减少单价] = a.[减少单价]
,[减少金额] = a.[减少金额]
,[期末数量] = SUM(b.期初数量 + b.增加数量 - b.减少数量)
,[期末单价] = SUM(b.期初金额 + b.增加金额 - b.减少金额)
/SUM(b.期初数量 + b.增加数量 - b.减少数量)
,[期末金额] = SUM(b.期初金额 + b.增加金额 - b.减少金额)
FROM T a
JOIN T b
ON a.p_id = b.p_id AND a.p_tm >= b.p_tm
GROUP BY a.id, a.p_id, a.p_tm, a.增加数量,a.增加单价,a.增加金额,
a.减少数量, a.减少单价, a.减少金额
ORDER BY a.p_tm
/*
iid p_id p_tm 期初数量 期初单价 期初金额 增加数量 增加单价 增加金额 减少数量 减少单价 减少金额 期末数量 期末单价 期末金额
1 1 2010-04-01 00:00:00.000 50 12.30 615.00 0 0.00 0.00 0 0.00 0.00 50 12.30 615.00
2 1 2010-04-02 00:00:00.000 50 12.30 615.00 10 12.30 123.00 0 0.00 0.00 60 12.30 738.00
3 1 2010-04-05 00:00:00.000 60 12.30 738.00 0 0.00 0.00 10 15.30 153.00 50 11.70 585.00
4 1 2010-04-06 00:00:00.000 50 11.70 585.00 5 12.30 61.50 0 0.00 0.00 55 11.7545 646.50
5 1 2010-04-07 00:00:00.000 55 11.7545 646.50 20 12.30 246.00 0 0.00 0.00 75 11.90 892.50
6 1 2010-04-09 00:00:00.000 75 11.90 892.50 22 12.30 270.60 0 0.00 0.00 97 11.9907 1163.10
7 1 2010-04-10 00:00:00.000 97 11.9907 1163.10 0 0.00 0.00 7 15.70 109.70 90 11.7044 1053.40
8 1 2010-04-11 00:00:00.000 90 11.7044 1053.40 0 0.00 0.00 10 15.50 155.00 80 11.23 898.40(8 行受影响)
*/