本人在制作一程序时,遇到如下一问题,希望高手们帮帮忙,呵呵!
问题如下:
本人创建一积分表(score),此积分表中设置有几个优惠套餐如下
id score_num money_num
1 1 1
2 33 30
3 57 50
4 122 100
解析:score_num为积分数,money_num为金额,
1:积分为1元1积分
2:充值30元可得积分33个
3:充值50元可得积分57个
4:充值100元可得积分122个
现本人通过前台程序得到对应的金额数,然后想通过后台的SQL语句转换对应的积分数,其中积分数按相应的套餐进行换算(按金额高低换算)
例如:1:前台数据为20,对应的就会得到数据20(即20个1元1积分的套餐)
2:前台数据为30,对应的就会得到数据33(即1个30元33积分的套餐)
3:前台数据为40,对应的就会得到数据43(即1个30元33积分的套餐,再加10个1元1积分的套餐)
4:前台数据为50,对应的就会得到数据57(即1个50元57积分的套餐)
5:前台数据为90,对应的就会得到数据100(即1个50元57积分的套餐,1个30元33积分的套餐,10个1元1积分的套餐)
其它同理推算,请高手们指点!
问题如下:
本人创建一积分表(score),此积分表中设置有几个优惠套餐如下
id score_num money_num
1 1 1
2 33 30
3 57 50
4 122 100
解析:score_num为积分数,money_num为金额,
1:积分为1元1积分
2:充值30元可得积分33个
3:充值50元可得积分57个
4:充值100元可得积分122个
现本人通过前台程序得到对应的金额数,然后想通过后台的SQL语句转换对应的积分数,其中积分数按相应的套餐进行换算(按金额高低换算)
例如:1:前台数据为20,对应的就会得到数据20(即20个1元1积分的套餐)
2:前台数据为30,对应的就会得到数据33(即1个30元33积分的套餐)
3:前台数据为40,对应的就会得到数据43(即1个30元33积分的套餐,再加10个1元1积分的套餐)
4:前台数据为50,对应的就会得到数据57(即1个50元57积分的套餐)
5:前台数据为90,对应的就会得到数据100(即1个50元57积分的套餐,1个30元33积分的套餐,10个1元1积分的套餐)
其它同理推算,请高手们指点!
1:积分为1元1积分
2:充值30元可得积分33个
3:充值50元可得积分57个
4:充值100元可得积分122个
*/DECLARE @n INT,@m INT;
SELECT @n = 90;SELECT
@m = @n / 100 * 122,
@n = @n - @n / 100 * 120,
@m = @m + @n / 50 * 57,
@n = @n - @n / 50 * 50,
@m = @m + @n / 30 * 33,
@n = @n - @n / 30 * 30,
@m = @m + @n / 1 * 1,
@n = @n - @n / 1 * 1;SELECT @m--自己计算.
jf=
case when num between 0 and 29 then num*1
when num between 30 and 49 then 33+(num-30)*1
when num between 50 and 79 then 57+(num-50)*1
when num between 80 and 99 then 57+33+(num-80)*1
else 122 end
from tb
set @i=95
select
case when (@i%100)<30 then @i%100+@i/100*122
when (@i%100)>=30 and (@i%100)<50 then (@i%100-30)+33+@i/100*122
when (@i%100)>=50 and (@i%100)<80 then (@i%100-30)+57+@i/100*122
when (@i%100)>=80 and (@i%100)<100 then (@i%100-80)+33+57+@i/100*122 end
1:积分为1元1积分
2:充值30元可得积分33个
3:充值50元可得积分57个
4:充值100元可得积分122个
*/DECLARE @n INT,@m INT;
SELECT @n = 90;SELECT
@m = @n / 100 * 122,
@n = @n - @n / 100 * 100,
@m = @m + @n / 50 * 57,
@n = @n - @n / 50 * 50,
@m = @m + @n / 30 * 33,
@n = @n - @n / 30 * 30,
@m = @m + @n / 1 * 1,
@n = @n - @n / 1 * 1;SELECT @m
id score_num money_num
1 1 1
2 33 30
3 57 50
4 122 100
5 250 200
那如上的语句又得改了,这样的移植性不好,有没有办法从我的表中读内容,并按我的积分表的相关内容进行写这个程序呢?谢谢
INSERT INTO tb(score_num,money_num) SELECT 1,1
UNION ALL SELECT 30,33
UNION ALL SELECT 50,57
UNION ALL SELECT 100,122GO
--计算积分函数
CREATE FUNCTION dbo.CalcNumber(@score_num INT)
RETURNS INT
AS
BEGIN
DECLARE @num INT;
SET @num = 0; WITH Liang AS
(
SELECT TOP(1) id,score_num,money_num,
total=CAST(@score_num/score_num*money_num AS INT),
div=CAST(@score_num-@score_num/score_num*score_num AS INT),
level = 1
FROM tb
WHERE score_num <= @score_num ORDER BY score_num DESC
UNION ALL
SELECT
A.id,A.score_num,A.money_num,
B.total+B.div/A.score_num * A.money_num,
div-(B.div/A.score_num*A.score_num),
level + 1
FROM tb AS A
JOIN Liang AS B
ON A.id = B.id - 1 AND div >= 0
)
SELECT @num = total FROM Liang;
RETURN @num;
END
GO--测试CREATE TABLE test(id int,score_num int);
INSERT test SELECT 1,200
UNION ALL SELECT 2,90
UNION ALL SELECT 3,350
UNION ALL SELECT 4,10SELECT *,dbo.CalcNumber(score_num) AS 积分
FROM test AS A
GO
--删除测试
DROP TABLE tb,test;
DROP FUNCTION dbo.CalcNumber/*
id score_num 积分
----------- ----------- -----------
1 200 244
2 90 100
3 350 423
4 10 10(4 行受影响)
*/
用游标按照Score_Num倒序计算返回的Money,
按照函数结果返回即可