大家好,我有以下的二个表
Table1
PN Quantity
----------------
A 500********************Table2
PN Quantity Date
----------------------------
A 100 2007/06/23
A 100 2007/06/24
A 100 2007/06/25
A 100 2007/06/26
A 200 2007/06/27现在我想用Table1的数量去减Table2中的每一个数量;需要得到以下的结果
******************************************************************
Table2
PN Quantity Date
----------------------------
A 0 2007/06/23
A 0 2007/06/24
A 0 2007/06/25
A 0 2007/06/26
A 100 2007/06/27不知各位有没有比较快的方法或思路?
谢谢!
Table1
PN Quantity
----------------
A 500********************Table2
PN Quantity Date
----------------------------
A 100 2007/06/23
A 100 2007/06/24
A 100 2007/06/25
A 100 2007/06/26
A 200 2007/06/27现在我想用Table1的数量去减Table2中的每一个数量;需要得到以下的结果
******************************************************************
Table2
PN Quantity Date
----------------------------
A 0 2007/06/23
A 0 2007/06/24
A 0 2007/06/25
A 0 2007/06/26
A 100 2007/06/27不知各位有没有比较快的方法或思路?
谢谢!
case when (select sum(Quantity) from Table2 where Pn=b.Pn and date<=b.Date)<a.Quantity then 0 else (select sum(Quantity) from Table2 where Pn=b.Pn and date<=b.Date)-a.Quantity end as Quantity,
b.Date
from Table1 a,Table2 b
where a.PN=b.PN
order by b.Pn,b.Date
insert into T1 select 'A', 500create table T2(PN varchar(100), Quantity int, [Date] datetime)
insert into T2
select 'A',100,'2007/06/23' union all
select 'A',100,'2007/06/24' union all
select 'A',100,'2007/06/25' union all
select 'A',100,'2007/06/26' union all
select 'A',200,'2007/06/27'
select pn, case when 剩余<0 then 0 else 剩余 end as 剩余,[date]
from (
select
A.pn,
(select sum(Quantity) from T2 where pn=A.pn and [date]<=A.[Date]) - (select sum(Quantity) from T1 where pn=A.pn) as 剩余,
[Date]
from T2 As A) AS T
drop table T1,T2
select 'a' as pn,100 as Quantity,'2007/06/23' as date union
select 'a' as pn,100 as Quantity,'2007/06/24' as date union
select 'a' as pn,100 as Quantity,'2007/06/25' as date union
select 'a' as pn,100 as Quantity,'2007/06/26' as date union
select 'a' as pn,200 as Quantity,'2007/06/27' as date )aselect
pn,
case when (select sum(Quantity) from table2 where date<=a.date and pn=a.pn)<=500 then 0
else (select sum(Quantity) from table2 where date<=a.date and pn=a.pn)-500 end as Quantity,
date
from table2 adrop table table1
drop table table2/*
a 0 2007/06/23
a 0 2007/06/24
a 0 2007/06/25
a 0 2007/06/26
a 100 2007/06/27
*/
在进行Table1的数量去减Table2中的每一个数量时,如果Table1中的量<=0时,Table2的剩余量=Table2.Quantity-Table1.Quantity,并退出语句,如下
Table1
PN Quantity
----------------
A 500********************Table2
PN Quantity Date
----------------------------
A 200 2007/06/23
A 500 2007/06/24
A 100 2007/06/25
A 100 2007/06/26
A 200 2007/06/27结果
******************************************************************
Table2
PN Quantity Date
----------------------------
A 0 2007/06/23
A 200 2007/06/24
A 100 2007/06/25
A 100 2007/06/26
A 200 2007/06/27
declare @Table1 table (
PN varchar(10),
Quantity int
)insert @Table1
---------------- 多个PN一起计算
select 'A', 500
union all
select 'B', 200 --不够扣的
union all
select 'C', 5000 --有多余的declare @Table2 table (
PN varchar(10),
Quantity int,
Date datetime
)
insert @Table2 select
----------------------------
'A', 100, '2007/06/23'
union all select
'B', 100, '2007/06/23' --打乱次序
union all select
'C', 100, '2007/06/23'
union all select
'A', 100, '2007/06/24'
union all select
'B', 100, '2007/06/24'
union all select
'C', 100, '2007/06/24'
union all select
'A', 100, '2007/06/25'
union all select
'B', 100, '2007/06/25'
union all select
'C', 100, '2007/06/25'
union all select
'A', 100, '2007/06/26'
union all select
'A', 200, '2007/06/27'
union all select
'C', 200, '2007/06/27'
union all select
'B', 200, '2007/06/27'
--开始计算
declare @Result table (
PN varchar(10),
QuantityA int,
Quantity int,
Date datetime
)insert @Result
select a.Pn,a.Quantity,b.Quantity,b.date
from @Table1 a,@Table2 b
where a.Pn=b.Pn
order by a.Pn,b.Datedeclare @Pn varchar(10)
declare @QuantityA int
declare @Quantity int--计算
update @Result
set @Quantity=case when Quantity>case when @Pn is null or @Pn<>Pn then QuantityA else @QuantityA end then case when @Pn is null or @Pn<>Pn then QuantityA else @QuantityA end else Quantity end,
@QuantityA=case when @Pn is null or @Pn<>Pn then QuantityA else @QuantityA end-@Quantity,
@Pn=Pn,
Quantity=Quantity-@Quantity--显示结果
select PN,Quantity,Date
from @Result--结果
PN Quantity Date
---------- ----------- ------------------------------------------------------
A 0 2007-06-23 00:00:00.000
A 0 2007-06-24 00:00:00.000
A 0 2007-06-25 00:00:00.000
A 0 2007-06-26 00:00:00.000
A 100 2007-06-27 00:00:00.000
B 0 2007-06-23 00:00:00.000
B 0 2007-06-24 00:00:00.000
B 100 2007-06-25 00:00:00.000
B 200 2007-06-27 00:00:00.000
C 0 2007-06-23 00:00:00.000
C 0 2007-06-24 00:00:00.000
C 0 2007-06-25 00:00:00.000
C 0 2007-06-27 00:00:00.000(所影响的行数为 13 行)