请教MS-SQL语法
大家好,我有以下的二个表
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
不知各位有没有比较快的方法或思路?
谢谢!
[解决办法]
select b.PN,
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
[解决办法]
上面我的算法确实有问题，看下面的算法，借助表变量
--原始表
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.Date
declare @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 行）
