日期:2014-05-17 浏览次数:20670 次
--> 测试数据:[主表]
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 TABLE [明细表]([ID] INT,[SL] INT,[DATE] DATETIME)
INSERT [明细表]
SELECT 1,10,'2012-09-01' UNION ALL
SELECT 1,20,'2012-09-02' UNION ALL
SELECT 2,50,'2012-09-03' UNION ALL
SELECT 4,90,'2012-09-04' UNION ALL
SELECT 1,30,'2012-09-05'
--------------开始查询--------------------------
SELECT *,(select [SL] FROM [主表] WHERE [ID]=a.[ID])-(select SUM([SL]) FROM [明细表] WHERE [ID]=a.[ID] AND [DATE]<=a.[DATE])
FROM [明细表] AS a
----------------结果----------------------------
/*
ID SL DATE (无列名)
1 10 2012-09-01 00:00:00.000 90
1 20 2012-09-02 00:00:00.000 70
2 50 2012-09-03 00:00:00.000 50
4 90 2012-09-04 00:00:00.000 10
1 30 2012-09-05 00:00:00.000 40
*/
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 &n