select
b.name,b.qty,b.supply,[剩余]=case when a.Qty>b.[sum_qty] then b.Qty else a.Qty-(b.[sum_qty]-b.Qty) end
from
a,(select *,[sum_qty]=(select sum(qty) from b where Name=t.Name and time>=t.time) from b t )b
where
a.Name=b.Name and a.qty>b.[sum_qty]-b.qty
b.name,b.qty,b.supply,[剩余]=case when a.Qty>b.[sum_qty] then b.Qty else a.Qty-(b.[sum_qty]-b.Qty) end
from
a,(select *,[sum_qty]=(select sum(qty) from b where Name=t.Name and time>=t.time) from b t )b
where
a.Name=b.Name and a.qty>b.[sum_qty]-b.qty
b.name,b.qty,b.supply,[剩余]=case when a.Qty>b.[sum_qty] then b.Qty when a.qty>b.[sum_qty]-b.qty then a.Qty-(b.[sum_qty]-b.Qty) else 0 end
from
a,(select *,[sum_qty]=(select sum(qty) from b where Name=t.Name and time>=t.time) from b t )b
where
a.Name=b.Name
其它顯示為0時,把條件放在when處
drop table bcreate table a
(
name varchar(10),
qty int
)
create table b
(
name varchar(10),
qty int,
supply varchar(20),
time datetime
)insert into a
select 'a',50 union all select 'b',100insert into b
select 'a',10,'ga','2008-1-1' union all
select 'a',10,'gb','2008-1-2' union all
select 'b',40,'gc','2008-1-3' union all
select 'b',110,'ga','2008-1-4' union all
select 'a',70,'gd','2008-1-5' union all
select 'a',10,'ga','2008-1-6' union all
select 'a',10,'ga','2008-1-7' union all
select 'a',108,'ga','2008-1-8' select
b.name,b.qty,b.supply,
[剩余]=case when a.Qty>b.[sum_qty] then 0 else b.[sum_qty]-a.Qty end
from
a,(select *,[sum_qty]=(select sum(qty) from b where Name=t.Name and time<=t.time) from b t )b
where
a.Name=b.Name and a.qty>b.[sum_qty]-b.qty
谢谢你!