有一个DriveCoefficient表,里面有两个字段分别为:
DCName,DCCoefficient
2000 1.2
1500 1.1
3000 1.3
意思是:输入一个值将他分段计算。
假如有一个字段的值为2500的时候,就这样计算:2000*1.2+(3000-2000)*1.1 。
DCName,DCCoefficient这个两个字段的值是无限的
也可以使用qq联系602707155,谢谢大虾们哦
DCName,DCCoefficient
2000 1.2
1500 1.1
3000 1.3
意思是:输入一个值将他分段计算。
假如有一个字段的值为2500的时候,就这样计算:2000*1.2+(3000-2000)*1.1 。
DCName,DCCoefficient这个两个字段的值是无限的
也可以使用qq联系602707155,谢谢大虾们哦
解决方案 »
- Sqlserver2008数据库文件如何在未安装数据库的机器上使用?
- 连接远程SQLSERVER服务器,开启事务报错!!!
- 启动不了服务管理器
- 自定义函数问题...
- 用osql,怎么加参数才能不让他在命令窗口中显示处理结果呢
- SQL默认值 能否 关联 当前输入的值?
- 关于连接sql2000报sql2005的错误
- 请教个位仁兄,这条SQL语句与inner join有什么区别?
- 【求助】期末考试压轴题,一个班的幸福就全教给您了!!!
- 引用完整性约束权限的问题
- 一个视图中包含多个视图和多个表,在提取数据的时候非常慢,里面的视图和表已经不能再优化。请问还有什么好的方法啊,高手指点,不惜发言啊
- 求救,想把个出来的数据都放在一张表里,作为报表的数据
Drop table DriveCoefficient
GO
create table DriveCoefficient(
DCName numeric(8,2),
DCCoefficient numeric(8,2))
GOinsert into DriveCoefficient
select 2000, 1.2 union all
select 1500, 1.1 union all
select 3000, 1.3
GOIF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udf_GetSumWithCoefficient]') AND type in (N'FN', N'IF', N'TF'))
DROP function [dbo].udf_GetSumWithCoefficient
GOcreate function udf_GetSumWithCoefficient(@input int)
returns int
as
BEGIN
DECLARE @DCName numeric(8,2)
DECLARE @DCCoefficient numeric(8,2)
DECLARE @Sum numeric(8,2) SET @Sum = 0.0 DECLARE db_cursor CURSOR FOR
SELECT DCName, DCCoefficient
FROM DriveCoefficient
where DCName <= @input
union
SELECT DCName, DCCoefficient
FROM DriveCoefficient
where DCName = (select min(DCName) from DriveCoefficient)
order by DCName desc OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DCName, @DCCoefficient WHILE @@FETCH_STATUS = 0
BEGIN
if @DCName <= @input
begin
set @Sum = @Sum + @DCName * @DCCoefficient
set @input = @input - @DCName
end
FETCH NEXT FROM db_cursor INTO @DCName, @DCCoefficient
END if @DCName > @input and @input > 0
set @Sum = @Sum + @input * @DCCoefficient CLOSE db_cursor
DEALLOCATE db_cursor return @Sum
END
GO
select dbo.udf_GetSumWithCoefficient(500)
select dbo.udf_GetSumWithCoefficient(1750)
select dbo.udf_GetSumWithCoefficient(2500)
select dbo.udf_GetSumWithCoefficient(3000)
select dbo.udf_GetSumWithCoefficient(3500)
godrop function udf_GetSumWithCoefficient
drop table DriveCoefficient
go
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DriveCoefficient]') AND type in (N'U'))
Drop table DriveCoefficient
GO
create table DriveCoefficient(
DCName numeric(8,2),
DCCoefficient numeric(8,2))
GOinsert into DriveCoefficient
select 1500, 1.1 union all
select 2000, 1.2 union all
select 3000, 1.3
GOIF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udf_GetSumWithCoefficient]') AND type in (N'FN', N'IF', N'TF'))
DROP function [dbo].udf_GetSumWithCoefficient
GOcreate function udf_GetSumWithCoefficient(@input int)
returns int
as
BEGIN
DECLARE @DCName numeric(8,2)
DECLARE @DCCoefficient numeric(8,2)
DECLARE @DCStep numeric(8,2)
DECLARE @Sum numeric(8,2) SET @Sum = 0.0 DECLARE db_cursor CURSOR FOR
SELECT
[next].DCName,
ISNULL([next].DCName, 0) - [current].DCName,
[next].DCCoefficient
FROM
DriveCoefficient AS [current]
inner JOIN
DriveCoefficient AS [next]
ON [next].DCName = (SELECT MIN(DCName) FROM DriveCoefficient WHERE DCName > [current].DCName)
--order by [next].DCName
union
SELECT DCName, DCName, DCCoefficient
FROM DriveCoefficient
where DCName = (select min(DCName) from DriveCoefficient)
order by 1 OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DCName, @DCStep, @DCCoefficient WHILE @@FETCH_STATUS = 0
BEGIN
if @DCStep <= @input
begin
set @Sum = @Sum + @DCStep * @DCCoefficient
set @input = @input - @DCStep
end
else
begin
set @Sum = @Sum + @input * @DCCoefficient
set @input = 0
end FETCH NEXT FROM db_cursor INTO @DCName, @DCStep, @DCCoefficient
END if @input > 0
set @Sum = @Sum + @input * @DCCoefficient CLOSE db_cursor
DEALLOCATE db_cursor return @Sum
END
GO
select dbo.udf_GetSumWithCoefficient(500)
select dbo.udf_GetSumWithCoefficient(1750)
select dbo.udf_GetSumWithCoefficient(2500)
select dbo.udf_GetSumWithCoefficient(3000)
select dbo.udf_GetSumWithCoefficient(3500)
godrop function udf_GetSumWithCoefficient
drop table DriveCoefficient
go
insert into DriveCoefficient
select 1500, 1.1 union all 0~1500 系数为1.1
select 2000, 1.2 union all 1500~2000 系数为1.2
select 3000, 1.3 2000~3000 系数为1.3
3000~ 系数为1.3之后: DCName的意思是区间的低限
insert into DriveCoefficient
select 0, 1 union all 0~1500 系数为1.1
select 1500, 1.1 union all 1500~2000 系数为1.1
select 2000, 1.2 union all 2000~3000 系数为1.2
select 3000, 1.3 3000~ 系数为1.3
GO已经提交更新版本.测试结果正确.