有一个表如下
线路 表达式 价格
京广 0<@x<=5 7
京广 5<@x<=10 6
京广 10<@x<=20 5
京广 >20 4
京沪 0<@x<=5 5
京沪 5<@x<=10 4
京沪 10<@x<=20 3
京沪 >20 2现在想建立一个函数,输入两个参数,一个是线路参数,另一个是一个数值,想得到的结果是该数值的价格。
请高人指点。超级感谢
线路 表达式 价格
京广 0<@x<=5 7
京广 5<@x<=10 6
京广 10<@x<=20 5
京广 >20 4
京沪 0<@x<=5 5
京沪 5<@x<=10 4
京沪 10<@x<=20 3
京沪 >20 2现在想建立一个函数,输入两个参数,一个是线路参数,另一个是一个数值,想得到的结果是该数值的价格。
请高人指点。超级感谢
线路 表达式 价格
京广 0 <@x <=5 7
京广 5 <@x <=10 6
京广 10 <@x <=20 5
京广 >20 4
京沪 0 <@x <=5 5
京沪 5 <@x <=10 4
京沪 10 <@x <=20 3
京沪 >20 2 把上表转为
线路 阈值 价格
京广 0 7
京广 5 6
京广 10 5
京广 20 4
京沪 0 5
京沪 5 4
京沪 10 3
京沪 20 2
就很好处理了
京广 0 <@x<=5 7
京广 5 <@x<=10 6
京广 10 <@x<=20 5
京广 @x>20 4 京沪 0 <@x<5 5
京沪 5 <=@x<10 4
京沪 10 <=@x<=20 3
京沪 @x>20 2 京哈 0 <@x<5 5
京哈 5 <=@x<10 4
京哈 10 <=@x<=20 3
京哈 @x>20 2 象上面这个样子的,两端的值比较难解决。我原来也是用固定的阀值来解决,但对于等于的处理太难了:(
insert into tb values('京广' , '0 <@x <=5' , 7 )
insert into tb values('京广' , '5 <@x <=10' , 6 )
insert into tb values('京广' , '10 <@x <=20' , 5 )
insert into tb values('京广' , '>20' , 4 )
insert into tb values('京沪' , '0 <@x <=5' , 5 )
insert into tb values('京沪' , '5 <@x <=10' , 4 )
insert into tb values('京沪' , '10 <@x <=20' , 3 )
insert into tb values('京沪' , '>20' , 2 )
go
declare @线路 as varchar(10)
set @线路 = '京广'
declare @x as int
set @x = 10select * from tb where 线路 = @线路 and charindex('<',表达式) > 0 and charindex('<=',表达式) > 0 and @x > cast(left(表达式,charindex('<',表达式)-1) as int) and @x <= cast(substring(表达式 , charindex('<=',表达式) + 2 , len(表达式)) as int)
union all
select * from tb where 线路 = @线路 and charindex('>',表达式) > 0 and @x > cast(substring(表达式 , charindex('>',表达式) + 1 , len(表达式)) as int)
/*
线路 表达式 价格
---------- -------------------- -----------
京广 5 <@x <=10 6
*/set @x = 21select * from tb where 线路 = @线路 and charindex('<',表达式) > 0 and charindex('<=',表达式) > 0 and @x > cast(left(表达式,charindex('<',表达式)-1) as int) and @x <= cast(substring(表达式 , charindex('<=',表达式) + 2 , len(表达式)) as int)
union all
select * from tb where 线路 = @线路 and charindex('>',表达式) > 0 and @x > cast(substring(表达式 , charindex('>',表达式) + 1 , len(表达式)) as int)
/*
线路 表达式 价格
---------- -------------------- -----------
京广 >20 4(所影响的行数为 1 行)
*/drop table tb
insert into tb values('京广' , '0 <@x <=5' , 7 )
insert into tb values('京广' , '5 <@x <=10' , 6 )
insert into tb values('京广' , '10 <@x <=20' , 5 )
insert into tb values('京广' , '>20' , 4 )
insert into tb values('京沪' , '0 <@x <=5' , 5 )
insert into tb values('京沪' , '5 <@x <=10' , 4 )
insert into tb values('京沪' , '10 <@x <=20' , 3 )
insert into tb values('京沪' , '>20' , 2 )
gocreate proc my_proc @线路 as varchar(10) , @x as int ,@价格 int output
as
begin
select @价格 = 价格 from
(
select * from tb where 线路 = @线路 and charindex('<',表达式) > 0 and charindex('<=',表达式) > 0 and @x > cast(left(表达式,charindex('<',表达式)-1) as int) and @x <= cast(substring(表达式 , charindex('<=',表达式) + 2 , len(表达式)) as int)
union all
select * from tb where 线路 = @线路 and charindex('>',表达式) > 0 and @x > cast(substring(表达式 , charindex('>',表达式) + 1 , len(表达式)) as int)
) t
end
godeclare @价格 as int
exec my_proc '京沪' , 10 , @价格 output
print @价格
/*
4
*/exec my_proc '京广' , 15 , @价格 output
print @价格
/*
5
*/drop table tb
drop proc my_proc
京广 0 <@x <=5 7
京广 5 <@x <=10 6
京广 10 <@x <=20 5
京广 @x>20 4 京沪 0 <@x <5 5
京沪 5 <=@x <10 4
京沪 10 <=@x <=20 3
京沪 @x>20 2 京哈 0 <@x <3 5
京哈 3 <=@x <10 4
京哈 10 <=@x <=30 3
京哈 @x>30 2 这个问题的难点在于阀值不确定,而且等于号也是不规律的。
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(
LINE VARCHAR(50),
FUNC VARCHAR(50),
PRICE NUMERIC(19,6)
)
INSERT INTO TB
SELECT '京广','0 <@x <=5',7 UNION ALL
SELECT '京广','5 <@x <=10', 6 UNION ALL
SELECT '京广','10 <@x <=20', 5 UNION ALL
SELECT '京广','>20', 4 UNION ALL
SELECT '京沪','0 <@x <=5', 5 UNION ALL
SELECT '京沪','5 <@x <=10', 4 UNION ALL
SELECT '京沪','10 <@x <=20', 3UNION ALL
SELECT '京沪','>20', 2 DECLARE @LINE VARCHAR(50),@FUNC INT
SELECT @LINE='京沪',@FUNC=6 --这里输入参数SELECT TOP 1 LINE,FUNC,PRICE FROM (
SELECT *
,CAST(REVERSE(LEFT(REVERSE(FUNC),PATINDEX('%[^0-9]%',REVERSE(FUNC))-1)) AS INT) 'COL1'
,CHARINDEX('>',FUNC) 'COL2'
FROM TB) T
WHERE LINE=@LINE AND ((COL2=0 AND COL1>=@FUNC) OR (COL2=1) AND COL1<@FUNC)
ORDER BY COL1 ASC/*
京沪 5 <@x <=10 4.000000
*/
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(
LINE VARCHAR(50),
FUNC VARCHAR(50),
PRICE NUMERIC(19,6)
)
INSERT INTO TB
SELECT '京广','0 <@x <=5',7 UNION ALL
SELECT '京广','5 <@x <=10', 6 UNION ALL
SELECT '京广','10 <@x <=20', 5 UNION ALL
SELECT '京广','>20', 4 UNION ALL
SELECT '京沪','0 <@x <=5.8', 5 UNION ALL
SELECT '京沪','5.8 <@x <=10', 4 UNION ALL
SELECT '京沪','10 <@x <=20', 3UNION ALL
SELECT '京沪','>20', 2 DECLARE @LINE VARCHAR(50),@FUNC NUMERIC
SELECT @LINE='京沪',@FUNC=6 --这里输入参数SELECT TOP 1 LINE,FUNC,PRICE FROM (
SELECT *
,CAST(REVERSE(LEFT(REVERSE(FUNC),PATINDEX('%[^0-9.]%',REVERSE(FUNC))-1)) AS NUMERIC(19,6)) 'COL1'
,CHARINDEX('>',FUNC) 'COL2'
FROM TB) T
WHERE LINE=@LINE AND ((COL2=0 AND COL1>=@FUNC) OR (COL2=1) AND COL1<@FUNC)
ORDER BY COL1 ASC
/*
京沪 5.8 <@x <=10 4.000000
*/
create table tb(线路 varchar(10), 表达式 varchar(20), 价格 int)
insert into tb values('京广' , '0 <@x <=5' , 7 )
insert into tb values('京广' , '5 <@x <=10' , 6 )
insert into tb values('京广' , '10 <@x <=20' , 5 )
insert into tb values('京广' , '>20' , 4 )
insert into tb values('京沪' , '0 <@x <=5' , 5 )
insert into tb values('京沪' , '5 <@x <=10' , 4 )
insert into tb values('京沪' , '10 <@x <=20' , 3 )
insert into tb values('京沪' , '>20' , 2 ) --SQL查询
declare @线路 varchar(10), @x int
set @线路 = '京广'
set @x = 20
select 价格 from
(
select *,
case when charindex('>', 表达式) > 0 then substring(表达式, 2, len(表达式)-1)
else substring(表达式, 1, charindex('<', 表达式)-1) end as min,
case when charindex('>', 表达式) > 0 then 99999 --足够大的数
else substring(表达式, charindex('<=', 表达式)+2, len(表达式) - charindex('<=', 表达式)+2 ) end as max
from tb
) a
where a.线路 = @线路 and a.min < @x and a.max >= @x
/*
价格
-----------
5(所影响的行数为 1 行)
*/
京广 0 <@x <=5 7
京广 5 <@x <=10 6
京广 10 <@x <=20 5
京广 @x>20 4 京沪 0 <@x <5 5
京沪 5 <=@x <10 4
京沪 10 <=@x <=20 3
京沪 @x>20 2 京哈 0 <@x <3 5
京哈 3 <=@x <10 4
京哈 10 <=@x <=30 3
京哈 @x>30 2 等于出现得不规律?线路 低值 是否包含 价格
京广 0 0 7
京广 5 0 6
京广 10 0 5
京广 20 0 4 京沪 0 0 5
京沪 5 1 4
京沪 10 1 3
京沪 20 0 2 京哈 0 0 5
京哈 3 1 4
京哈 10 1 3
京哈 30 0 2
DECLARE @LINE VARCHAR(50),@FUNC NUMERIC
改成
DECLARE @LINE VARCHAR(50),@FUNC NUMERIC(19,6)
就可以了
这个问题是定义参数的时候没指定长度啊,你把
DECLARE @LINE VARCHAR(50),@FUNC NUMERIC
改成
DECLARE @LINE VARCHAR(50),@FUNC NUMERIC(19,6)
就可以了
[/Quote]老大,您帅呆了。崇拜!!!!!
我的分还不够,我会慢慢赚分还您的。太谢谢了
[/Quote]INSERT INTO TB
SELECT '京津','0 <@x <3', 5 UNION ALL
SELECT '京津','3 <=@x <=10', 4 UNION ALL
SELECT '京津','10 <@x <20', 3UNION ALL
SELECT '京津','>=20', 2 最后一个如果是大于等于的话,取最后这个值,会出错。
例如上面的例子,取20的话,读出来的是3,而实际应该是2.