小弟求一条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
*/
