小弟求一条SQL。
主表
ID(ID) SL(总数量)
1 100
2 100
3 100
4 100

明细表
ID(ID) SL(使用数量) DATE(使用时间)
1 10 XXX
1 20 XXX
2 50 XXX
4 90 XXX
1 30 XXX


我想根据这2个表，查询出明细表每行记录后增加一列，此ID的人剩余总SL(数量)可以吗？

[解决办法]

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 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
----------------结果----------------------------
/* 
IDSLDATE(无列名)
1102012-09-01 00:00:00.00090
1202012-09-02 00:00:00.00070
2502012-09-03 00:00:00.00050
4902012-09-04 00:00:00.00010
1302012-09-05 00:00:00.00040
*/