关于浮动比例的存储过程实现

Question

建表语句如下:create table table_test(sub_id varchar(10),sub_value int,ratio numeric(19,2)) 
insert into table_test values('1000',10000,0.2)
insert into table_test values('1000',20000,0.25)
insert into table_test values('1000',30000,0.3)
insert into table_test values('1000',50000,0.35)
insert into table_test values('1000',100000,0.5)
insert into table_test values('1001',5000,0.3)
insert into table_test values('1001',10000,0.35)
insert into table_test values('1001',20000,0.4)
insert into table_test values('1001',50000,0.45)
insert into table_test values('1001',80000,0.5)需实现一个存储过程,传入参数sub_id及value(比如sub_id='1000',value=48000),
应返回10000*0.2+(20000-10000)*0.25+(30000-20000)*0.3+(48000-30000)*0.35

Answer 1

有的. 我改下create table table_test(id int,sub_id varchar(10),sub_value int,ratio numeric(19,2)) 
insert into table_test values(1,'1000',10000,0.2)
insert into table_test values(2,'1000',20000,0.25)
insert into table_test values(3,'1000',30000,0.3)
insert into table_test values(4,'1000',50000,0.35)
insert into table_test values(5,'1000',100000,0.5)
insert into table_test values(6,'1001',5000,0.3)
insert into table_test values(7,'1001',10000,0.35)
insert into table_test values(8,'1001',20000,0.4)
insert into table_test values(9,'1001',50000,0.45)
insert into table_test values(10,'1001',80000,0.5)

Answer 2

create table table_test(sub_id varchar(10),sub_value int,ratio numeric(19,2)) 
insert into table_test values('1000',10000,0.2)
insert into table_test values('1000',20000,0.25)
insert into table_test values('1000',30000,0.3)
insert into table_test values('1000',50000,0.35)
insert into table_test values('1000',100000,0.5)
insert into table_test values('1001',5000,0.3)
insert into table_test values('1001',10000,0.35)
insert into table_test values('1001',20000,0.4)
insert into table_test values('1001',50000,0.45)
insert into table_test values('1001',80000,0.5)DECLARE @sub_id VARCHAR(10);
DECLARE @sub_value INT;
SELECT @sub_id='1000',@sub_value=48000;SELECT ID=IDENTITY(INT,1,1),* INTO #T FROM table_test;SELECT 
    CASE WHEN sub_value<@sub_value THEN sub_value ELSE @sub_value END
    -ISNULL((SELECT TOP 1 sub_value
             FROM #T
             WHERE sub_id=A.sub_id
                AND ID<A.ID
             ORDER BY ID DESC),0) * ratio AS value
FROM #T AS A
WHERE sub_id=@sub_id
    AND ID<=(SELECT TOP 1 ID FROM #T 
             WHERE sub_id=A.sub_id
             ORDER BY ABS(sub_value-@sub_value))GO
DROP TABLE table_test,#T;/*
value
---------------------------------------
10000.00
17500.00
24000.00
37500.00(4 row(s) affected)*/

Answer 3

create table table_test(id int,sub_id varchar(10),sub_value int,ratio numeric(19,2)) 
insert into table_test values(1,'1000',10000,0.2)
insert into table_test values(2,'1000',20000,0.25)
insert into table_test values(3,'1000',30000,0.3)
insert into table_test values(4,'1000',50000,0.35)
insert into table_test values(5,'1000',100000,0.5)
insert into table_test values(6,'1001',5000,0.3)
insert into table_test values(7,'1001',10000,0.35)
insert into table_test values(8,'1001',20000,0.4)
insert into table_test values(9,'1001',50000,0.45)
insert into table_test values(10,'1001',80000,0.5)DECLARE @sub_id VARCHAR(10);
DECLARE @sub_value INT;
SELECT @sub_id='1000',@sub_value=48000;SELECT SUM(value) AS sum_value
FROM (
    SELECT 
        CASE WHEN sub_value<@sub_value THEN sub_value ELSE @sub_value END
          -ISNULL((SELECT TOP 1 sub_value
                   FROM table_test
                   WHERE sub_id=A.sub_id
                      AND ID<A.ID
                   ORDER BY ID DESC),0) * ratio AS value
    FROM table_test AS A
    WHERE sub_id=@sub_id
        AND ID<=(SELECT TOP 1 ID FROM table_test 
                 WHERE sub_id=A.sub_id
                 ORDER BY ABS(sub_value-@sub_value))
) AS T
GO
DROP TABLE table_test;/*
sum_value
---------------------------------------
89000.00(1 row(s) affected)
*/

Answer 4

create table table_test(id int,sub_id varchar(10),sub_value int,ratio numeric(19,2)) 
insert into table_test values(1,'1000',10000,0.2)
insert into table_test values(2,'1000',20000,0.25)
insert into table_test values(3,'1000',30000,0.3)
insert into table_test values(4,'1000',50000,0.35)
insert into table_test values(5,'1000',100000,0.5)
insert into table_test values(6,'1001',5000,0.3)
insert into table_test values(7,'1001',10000,0.35)
insert into table_test values(8,'1001',20000,0.4)
insert into table_test values(9,'1001',50000,0.45)
insert into table_test values(10,'1001',80000,0.5)DECLARE @sub_id VARCHAR(10);
DECLARE @sub_value INT;
SELECT @sub_id='1000',@sub_value=48000;SELECT SUM(value) AS sum_value
FROM (
    SELECT *,
        (CASE WHEN sub_value<@sub_value THEN sub_value ELSE @sub_value END 
          -ISNULL((SELECT TOP 1 sub_value
                   FROM table_test
                   WHERE sub_id=A.sub_id
                      AND ID<A.ID
                   ORDER BY ID DESC),0)) * ratio AS value
    FROM table_test AS A
    WHERE sub_id=@sub_id
        AND ID<=(SELECT TOP 1 ID FROM table_test 
                 WHERE sub_id=A.sub_id
                 ORDER BY ABS(sub_value-@sub_value))
) AS TGO
DROP TABLE table_test;/*
sum_value
---------------------------------------
13800.00(1 row(s) affected)
*/

Answer 5

create table table_test(id int,sub_id varchar(10),sub_value int,ratio numeric(19,2)) 
insert into table_test values(1,'1000',10000,0.2)
insert into table_test values(2,'1000',20000,0.25)
insert into table_test values(3,'1000',30000,0.3)
insert into table_test values(4,'1000',50000,0.35)
insert into table_test values(5,'1000',100000,0.5)
insert into table_test values(6,'1001',5000,0.3)
insert into table_test values(7,'1001',10000,0.35)
insert into table_test values(8,'1001',20000,0.4)
insert into table_test values(9,'1001',50000,0.45)
insert into table_test values(10,'1001',80000,0.5)GO
create proc up_retvalue
@sub_id varchar(10),
@sub_value int
as
declare @s varchar(1000);
with a as
(select *,ROW_NUMBER() over(order by sub_id,sub_value) row from 
(select sub_id ,sub_value ,ratio  from table_test where sub_id =@sub_id and sub_value <@sub_value union all
select top 1 sub_id ,@sub_value ,ratio  from table_test where sub_id =@sub_id and sub_value >=@sub_value order by sub_value) tb)
select @s=isnull(@s+'+','')+'('+cast(a.sub_value as varchar(10))+'-'+cast(isnull(b.sub_value,0) as varchar(10))+')*'+cast(a.ratio as varchar(10))
 from a left join a b on a.row =b.row +1
exec ('select '''+ @s+'''表达式,'+@s +' 结果')
GOexec up_retvalue '1000',48000
/*
表达式                                                                     结果
----------------------------------------------------------------------- ---------------------------------------
(10000-0)*0.20+(20000-10000)*0.25+(30000-20000)*0.30+(48000-30000)*0.35 13800.00
*/
exec up_retvalue '1001',58000
/*
表达式                                                                                      结果
---------------------------------------------------------------------------------------- ---------------------------------------
(5000-0)*0.30+(10000-5000)*0.35+(20000-10000)*0.40+(50000-20000)*0.45+(58000-50000)*0.50 24750.00
*/

Answer 6

小梁写得就是快，等我写好了，楼主都结贴了，不过既然写就贴出来献一下丑
当是一下吧declare @id int,@value int
set @id=1000
set @value=48000
declare @ret dec(19,2)
set @ret=0
select 
  @ret=@ret
      +((case when 
                sub_value=(select min(sub_value) from table_test where sub_id=@id and sub_value>@value) 
              then @value 
             else sub_value end
          )-isnull((select sub_value from table_test where id=t.id-1),0)
        )
       *ratio
from 
  table_test t
where 
  sub_id=@id 
and 
  sub_value<=(select min(sub_value) from table_test where sub_id=@id and sub_value>@value)select @ret/*--------------------- 
13800.00（所影响的行数为 1 行）
**/