实在不行了，求一sql自定义函数
表结构是这样子的
ddays dfees
.003.00
12.007.00
18.005.00
26.008.00
31.003.00
ddays 字段是天数 dfees字段是单价
在0到12天（不包括12）天的时候单是3块钱
在12到18天（不包括18）天的时候单是7块钱
在18到26天（不包括26）天的时候单是5块钱
请问如何写一个sql函数实现上面的功能;
create function DayJiaGe(days decimal(18,2))
如果输入DayJiaGe(4)则返回：3
如果输入DayJiaGe(12)天则返回：3,7
如果输入DayJiaGe(19)天则返回：3,7,5
[解决办法]
假設(表结构是这样子的ddays dfees)表名是test01,創建自定義函數的代碼如下：
CREATE function [dbo].[DayJiaGe](@days decimal(18,2))
RETURNS varchar(max)
AS
BEGIN
declare @RETURN varchar(max)
;
with a1 as
(
select 'a' gr,ddays,dfees from test01 where ddays<=@days
)
,a2 as
(
SELECTa.gr,dfees=
(select RTRIM(cast(dfees as char(20)))+',' as [text()]
from a1 b where a.gr=b.gr
for xml path('')
) FROM a1 a group by a.gr
)
select @RETURN=replace(replace(dfees,'.00,',','),'0,',',') from a2
return left(@RETURN,len(@RETURN)-1)
END
[解决办法]
代碼簡化版：
create function [dbo].[DayJiaGe](@days decimal(18,2))
RETURNS varchar(max)
AS
BEGIN
declare @RETURN varchar(max)
;
with a1 as
(
select dfees from test01 where ddays<=@days
)
,a2 as
(
SELECTdfees=
(select RTRIM(cast(dfees as char(20)))+',' as [text()]
from a1 b where 1=1
for xml path('')
) FROM a1 a
)
select @RETURN=replace(replace(dfees,'.00,',','),'0,',',') from a2
return left(@RETURN,len(@RETURN)-1)
END
[解决办法]
Create function fn_Dfees(
@days decimal(18,2)
)
returns varchar(200)
as
begin
declare @str varchar(200)
set @str=stuff( (select ','+rtrim(cast(dfees as int)) from table1 where ddays<=@days order by ddays for xml path('')),1,1,'')
return @str
end
IF OBJECT_ID('T_CSDN_ONE') IS NOT NULL
BEGIN
DROP TABLE dbo.T_CSDN_ONE;
END
GO
CREATE TABLE T_CSDN_ONE
(
DDAYS INT,
DFEES DECIMAL(12,2)
)
INSERT INTO dbo.T_CSDN_ONE
( DDAYS,DFEES )
SELECT 0,3
UNION ALL
SELECT 12,7
UNION ALL
SELECT 18,5
UNION ALL
SELECT 26,8
UNION ALL
SELECT 31,3
GO
CREATE FUNCTION DayJiaGe(@SDAY INT)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @STR VARCHAR(MAX);
SET @STR='';
;WITH SP AS
(
SELECT ROW_NUMBER() OVER(ORDER BY DDAYS) AS ROWNUMBER,* FROM T_CSDN_ONE WHERE DDAYS<@SDAY
)
SELECT @STR=(CASE WHEN @STR<>'' THEN @STR+',' ELSE '' END) +CAST(DFEES AS VARCHAR(20)) FROM SP
RETURN @STR;
END
SELECT DBO.DayJiaGe(19);
