求个SQL 函数的写法

[tsw13]

要求如下：
1、 函数原型：
CREATE FUNCTION fGetRuleMoney(@InputMoney Money, @RuleMode VarChar(512), @Par1 VarChar(20), @Par2 VarChar(20)) RETURNS Money
2、 参数描述
@InputMoney输入计算金额
@RuleMode 规则模式 例如: 500送20,1000送50,2000送150
@Par1 预留
@Par2 预留
3、 计算流程
输入计算金额
按照计算规则(最大化取整)的定义返回对应的金额
4、 例如：
Select dbo.fGetRuleMoney(1200, '500送20,1000送50,2000送150', '', '') 为50
Select dbo.fGetRuleMoney(3600, '500送20,1000送50,2000送150', '', '') 为220
请问该怎么写。

[快溜]

函数里切割字符串在比较吧

[--小F--]

晕 还要提取数字 拆分字符串。

[Mr_Nice]

体力活，字符串拆分，递归比对。

[tsw13]

谁能提供一个简单的参考例子啊？

[kevin_li125]

CREATE FUNCTION fGetRuleMoney
(
@InputMoney Money,
@RuleMode VarChar(10),
@Par1 VarChar(20),
@Par2 VarChar(20)
)

RETURNS Money
--建立把规则模式用种类代替，不同种类，设置不同的计算值
/*
A:500送20,1000送50,2000送150
*/
BEGIN
DECLARE @R1 NUMERIC(20,8),@R2 NUMERIC(20,8),@R3 NUMERIC(20,8)
DECLARE @S1 NUMERIC(20,8),@S2 NUMERIC(20,8),@S3 NUMERIC(20,8)
DECLARE @RTMoney Money

IF @RuleMode='A'
BEGIN
SET @R1=500
SET @R2=1000
SET @R3=2000

SET @S1=20
SET @S2=50
SET @S3=150
END

SET @RTMoney = Floor(@InputMoney/@R3)*@S3
+ Floor((@InputMoney%@R3)/@R2)*@S2
+ Floor(((@InputMoney%@R3)%@R2)/@R1)*@S1

Return(@RTMoney)
END



--示例：
SELECT A=DBO.fGetRuleMoney(3600,'A','','')

/*
A
220.00
*/

[中国风]

CREATE FUNCTION fGetRuleMoney(
	@InputMoney Money, 
	@RuleMode nVarChar(512), 
	@Par1 nVarChar(20)--分隔符
) RETURNS MONEY
AS
BEGIN 

DECLARE @Amount MONEY

SET @Amount=0


DECLARE @T TABLE(Col1 INT,Col2 INT,ID int)
INSERT INTO @T
	SELECT 
		Col1=LEFT(Col,CHARINDEX(N'送',Col)-1),Col2=SUBSTRING(Col,CHARINDEX(N'送',Col)+1,LEN(Col)),ID=ROW_NUMBER()OVER(ORDER BY LEFT(Col,CHARINDEX(N'送',Col)-1)*1 desc)
	FROM 
	(
	SELECT Col=substring(@RuleMode,b.number,charindex(',',@RuleMode+',',b.number)-b.number) FROM master.dbo.spt_values AS b
	WHERE b.type='P' AND charindex(',',','+@RuleMode,b.number)=b.number --也可用 substring(','+@RuleMode,b.number,1)=','
	)t

WHILE EXISTS(SELECT * FROM @T WHERE Col1<=@InputMoney)
BEGIN
	SELECT TOP 1 @Amount=@Amount+Col2  FROM @T WHERE Col1<=@InputMoney ORDER BY ID
	SET @InputMoney=@InputMoney-ISNULL((SELECT MAX(Col1)  FROM @T WHERE Col1<=@InputMoney),0)
END
RETURN @Amount
END
go
Select dbo.fGetRuleMoney(3600, N'500送20,1000送50,2000送150', ',') --为220
/*
220.00
*/

Select dbo.fGetRuleMoney(1200, '500送20,1000送50,2000送150', ', ') --为50
/*
50.00
*/

[中国风]

规则模式--不確定時用循環

[tsw13]

感谢5楼6楼两位！！

[tsw13]

后续问题，请热心高手帮忙
问题描述如下：
函数修改成如下：
ALTER FUNCTION [dbo].[fGetRuleMoneyz](
@InputMoney Money,
@RuleMode nVarChar(512),
@Par1 nVarChar(20)--分隔符
) RETURNS MONEY
AS
BEGIN

DECLARE @Amount MONEY

SET @Amount=0


DECLARE @T TABLE(NUM INT,Col1 INT,Col2 INT,ID int)
INSERT INTO @T
select * from
( SELECT
NUM=IsNumeric(SUBSTRING(Col,CHARINDEX(N'送',Col)+1,LEN(Col))),Col1=LEFT(Col,CHARINDEX(N'送',Col)-1),Col2=SUBSTRING(Col,CHARINDEX(N'送',Col)+1,LEN(Col)),ID=ROW_NUMBER()OVER(ORDER BY LEFT(Col,CHARINDEX(N'送',Col)-1)*1 desc)
FROM
(
SELECT Col=substring(@RuleMode,b.number,charindex(',',@RuleMode+',',b.number)-b.number) FROM master.dbo.spt_values AS b
WHERE b.type='P' AND charindex(',',','+@RuleMode,b.number)=b.number --也可用 substring(','+@RuleMode,b.number,1)=','
)tt
)t
where t.NUM='1'
order by t.ID
WHILE EXISTS(SELECT * FROM @T WHERE Col1<=@InputMoney and NUM='1' )
BEGIN
SELECT TOP 1 @Amount=@Amount+Col2 FROM @T WHERE Col1<=@InputMoney and NUM='1' ORDER BY ID
SET @InputMoney=@InputMoney-ISNULL((SELECT MAX(Col1) FROM @T WHERE Col1<=@InputMoney and NUM='1'),0)
END
RETURN @Amount
END

Select dbo.fGetRuleMoney(3600, '500送20,1000送50,2000送150,3000送3-00', '', '')
出来的结果为20

为什么出来的不是220 类似3000送3-00的规则不采用而只采用500送20,1000送50,2000送150
且
select * from
( SELECT
NUM=IsNumeric(SUBSTRING(Col,CHARINDEX(N'送',Col)+1,LEN(Col))),Col1=LEFT(Col,CHARINDEX(N'送',Col)-1),Col2=SUBSTRING(Col,CHARINDEX(N'送',Col)+1,LEN(Col)),ID=ROW_NUMBER()OVER(ORDER BY LEFT(Col,CHARINDEX(N'送',Col)-1)*1 desc)
FROM
(
SELECT Col=substring('500送20,1000送50,2000送150,3000送3-88',b.number,charindex(',','500送20,1000送50,2000送150,3000送3-88'+',',b.number)-b.number) FROM master.dbo.spt_values AS b
WHERE b.type='P' AND charindex(',',','+'500送20,1000送50,2000送150,3000送3-88',b.number)=b.number
)tt
)t
where t.NUM='1'
出来的数据为：
NUM Col1 Col2 ID
1 2000 150 2
1 1000 50 3
1 500 20 4