[sql server] 问题总结8- 中间变量

按编号统计成绩.如下数据,已知前四列数据,按序号顺序,求最后三列.怎样写效果最好.


/*
序号        编号          指标       分数       求分差     求可用分    求结果

1           A1            60          50          -10          -10      不达标
2           A1            65          90           25           25    达标
3           A1            70          50          -20           5    达标
4           A1            90          80          -10          -5    不达标
5           B1            90          60          -30          -30    不达标
6           B1            70          80          10           10    达标
7           B1            70          65          -5            5    达标
8           B1            90          70          -20          -15       不达标

*/

说明:可用分为正可以累积到下期扣减.

 

 

 

上个月的数据不达标不可以带到下个月. 但上个月超额完成的成绩,超过部分是可以带到下个月来算.

这样才有激励效果.

 

 

 

-----------------------------------------------------------------

 

解决思路： 在表的生成过程中用到了 上一个行的数据，  这些数据需要一个变量来保存。 每一行一个值，所有行下来也就是要一个数组，这里利用了一个字符串代替数组。然后分割字符串。

 

 

/***准备***/
declare @a table ( 序号 varchar(50), 编号 varchar(50), 指标 int, 分数 int)
insert into @a
          select  1,'A1',60,50
union all select 2,'A1',65,90
union all select 3,'A1',70,50
union all select 4,'A1',82,80
union all select 5,'B1',90,60
union all select 6,'B1',70,80
union all select 7,'B1',70,65
union all select 8,'B1',80,70

/***方法***/
create   function   f_split(@c   varchar(2000)) 

 returns   @t   table(id int,col   varchar(50),col1 varchar(100)) 
as  

    begin 
declare @acol varchar(400)
  declare @bm int
 declare @col varchar(50)
 declare @col1 varchar(100)
   
      while(charindex(',',@c)<>0)  
        begin  
           set @acol  = (substring(@c,1,charindex(',',@c)-1)) 
          
             set @bm  = cast((substring(@acol,1,charindex('_',@acol)-1))  as int)  
              set   @acol   =   stuff(@acol,1,charindex('_',@c),'')

              set @col  = (substring(@acol,1,charindex('_',@acol)-1))
              set   @acol   =   stuff(@acol,1,charindex('_',@c),'')

              set @col1  = substring(@acol,(charindex('_',@acol)+1),len(@acol)) 
             insert   @t(id,col,col1)   values   (@bm,@col,@col1) 

          set   @c   =   stuff(@c,1,charindex(',',@c),'') 

        end  
     
       return  
    end  
go  

/***执行***/
declare @numc int
declare @allnum varchar(1000)
declare @xh varchar(20) 
set @allnum=''
set @numc =0
select
  @numc =(case  when  @numc >= 0 and @xh=a.编号  then  @numc+(分数-指标)  else 分数-指标 end),
 @allnum =@allnum+cast(序号 as varchar(30))+'_'+ cast(@numc as varchar(30))+'_'+(case when @numc >=0 then '达标' else '不达标' end)+ ',',
 @xh=a.编号
  from @a as a

 select a.*,(a.分数-a.指标)[求差分],col [可用分],col1 [结果] from @a as a  left join  dbo.f_split(@allnum) as c on a.序号=c.id

 

/***结果***/
1 A1 60 50 -10 -10 不达标
2 A1 65 90 25 25 达标
3 A1 70 50 -20 5 达标
4 A1 82 80 -2 3 达标
5 B1 90 60 -30 -30 不达标
6 B1 70 80 10 10 达标
7 B1 70 65 -5 5 达标
8 B1 80 70 -10 -5 不达标