利用子查询实现逐条比较
(1)
declare @test table(pid int,sitid int,qty money)
insert @test
select 1,2,10
union all select 2,3,20
union all select 4,5,15
union all select 1,4,16
union all select 1,8,18
select * from @test
select
a.pid,
a.sitid,
(select
sum(qty)
from @test
where pid=a.pid and sitid<a.sitid)
from @test a
结果：
(5 row(s) affected)
pid sitid qty
----------- ----------- ---------------------
1 2 10.0000
2 3 20.0000
4 5 15.0000
1 4 16.0000
1 8 18.0000
(5 row(s) affected)
pid sitid
----------- ----------- ---------------------
1 2 NULL
2 3 NULL
4 5 NULL
1 4 10.0000
1 8 26.0000
(5 row(s) affected)
(2):
加上等号后变成了不同的情况
declare @test table(pid int,sitid int,qty money)
insert @test
select 1,2,10
union all select 2,3,20
union all select 4,5,15
union all select 1,4,16
union all select 1,8,18
select * from @test
select
a.pid,
a.sitid,
(select
sum(qty)
from @test
where pid=a.pid and sitid<=a.sitid)
from @test a
结果：
(5 row(s) affected)
pid sitid qty
----------- ----------- ---------------------
1 2 10.0000
2 3 20.0000
4 5 15.0000
1 4 16.0000
1 8 18.0000
(5 row(s) affected)
pid sitid
----------- ----------- ---------------------
1 2 10.0000
2 3 20.0000
4 5 15.0000
1 4 26.0000
1 8 44.0000
(5 row(s) affected)
以上看出pid=a.pid and sitid<=a.sitid条件的真正的含义
(3):
以下是一个具体的应用：
declare @test table(pid int,sitid int,qty money)
insert @test
select 1,2,10
union all select 2,3,20
union all select 4,5,15
union all select 1,4,16
union all select 1,8,18
select * from @test
declare @c money
set @c=15
select
a.pid,
a.sitid,
case when
(@c-
isnull((select
sum(qty)
from @test
where pid=a.pid and sitid<=a.sitid),0))>=0
then a.qty
else
case when (a.qty +@c -
isnull((select
sum(qty)
from @test
where pid=a.pid and sitid<=a.sitid),0))>=0
then a.qty +@c -
isnull((select
sum(qty)
from @test
where pid=a.pid and sitid<=a.sitid),0)
else 0
end
end qty
from @test a
结果：
(5 row(s) affected)
pid sitid qty
----------- ----------- ---------------------
1 2 10.0000
2 3 20.0000
4 5 15.0000
1 4 16.0000
1 8 18.0000
(5 row(s) affected)
pid sitid qty
----------- ----------- ---------------------
1 2 10.0000
2 3 15.0000
4 5 15.0000
1 4 5.0000
1 8 .0000
(5 row(s) affected)
