日期:2014-05-17 浏览次数:20418 次
declare @A table(ID int, SL int)
insert into @A
select 1, 100 union all
select 2, 100 union all
select 3, 100 union all
select 4, 100
declare @B table(ID int, SL int, DATE varchar(10))
insert into @B
select 1, 10, '2012-6-19' union all
select 1, 20, '2012-2-11' union all
select 2, 50, '2012-3-3' union all
select 4, 90, '2012-5-28' union all
select 1, 30, '2012-9-3'
select ID,SL,DATE,ZSY=(select SL from @A where t.ID=ID)
-(select sum(SL) from @B where t.ID=ID and t.DATE>=DATE)
from @B t
order by ID,DATE
/*
ID SL DATE ZSY
----------- ----------- ---------- -----------
1 20 2012-2-11 80
1 10 2012-6-19 70
1 30 2012-9-3 40
2 50 2012-3-3 50
4 90 2012-5-28 10
*/
--> 测试数据:[主表]
IF OBJECT_ID('[主表]') IS NOT NULL DROP TABLE [主表]
GO
CREATE TABLE [主表]([ID] INT,[SL] INT)
INSERT [主表]
SELECT 1,100 UNION ALL
SELECT 2,100 UNION ALL
SELECT 3,100 UNION ALL
SELECT 4,100
--> 测试数据:[明细表]
IF OBJECT_ID('[明细表]') IS NOT NULL DROP TABLE [明细表]
GO
CREATE TABL