利用子查询实现逐条比较

 

( 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)

转载于:https://www.cnblogs.com/wequst/archive/2008/09/17/1292619.html