create table PingFenBiaoZhun2(分值 int,类别 varchar(10),性别 varchar(10),数量 varchar(20)) insert into PingFenBiaoZhun2 select 10,rtrim('肺活量'),'男',rtrim('≥67') union all select 9 ,rtrim('肺活量'),'男',rtrim('67-58') union all select 8 ,rtrim('肺活量'),'男',rtrim('58-50') union all select 7 ,rtrim('肺活量'),'男',rtrim('50-46') union all select 6 ,rtrim('肺活量'),'男',rtrim('46-40') union all select 5 ,rtrim('肺活量'),'男',rtrim('40-31.9') union all select 4 ,rtrim('肺活量'),'男',rtrim('≤32') union all select 10,rtrim('肺活量'),'女',rtrim('≥49') union all select 9 ,rtrim('肺活量'),'女',rtrim('49-41') union all select 8 ,rtrim('肺活量'),'女',rtrim('41-36') union all select 7 ,rtrim('肺活量'),'女',rtrim('36-31') union all select 6 ,rtrim('肺活量'),'女',rtrim('31-26') union all select 5 ,rtrim('肺活量'),'女',rtrim('26-20.9') union all select 4 ,rtrim('肺活量'),'女',rtrim('≤21') union all select 7 ,rtrim('跳绳'),'男',rtrim('≥105') union all select 6 ,rtrim('跳绳'),'男',rtrim('105-100') union all select 5 ,rtrim('跳绳'),'男',rtrim('100-95') union all select 4 ,rtrim('跳绳'),'男',rtrim('95-90') union all select 3 ,rtrim('跳绳'),'男',rtrim('<90') union all select 7 ,rtrim('跳绳'),'女',rtrim('≥110') union all select 6 ,rtrim('跳绳'),'女',rtrim('110-105') union all select 5 ,rtrim('跳绳'),'女',rtrim('105-100') union all select 4 ,rtrim('跳绳'),'女',rtrim('100-95') union all select 3 ,rtrim('跳绳'),'女',rtrim('<95') union all select 7 ,rtrim('仰卧起坐'),'女',rtrim('≥33') union all select 6 ,rtrim('仰卧起坐'),'女',rtrim('33-27') union all select 5 ,rtrim('仰卧起坐'),'女',rtrim('27-23') union all select 4 ,rtrim('仰卧起坐'),'女',rtrim('23-15') union all select 3 ,rtrim('仰卧起坐'),'女',rtrim('<15') union all select 7 ,rtrim('立定跳远'),'男',rtrim('≥222') union all select 6 ,rtrim('立定跳远'),'男',rtrim('222-210') union all select 5 ,rtrim('立定跳远'),'男',rtrim('210-202') union all select 4 ,rtrim('立定跳远'),'男',rtrim('202-180') union all select 3 ,rtrim('立定跳远'),'男',rtrim('<180') union all select 7 ,rtrim('五十米'),'男',rtrim('≤7.6') union all select 6 ,rtrim('五十米'),'男',rtrim('7.6-8.1') union all select 5 ,rtrim('五十米'),'男',rtrim('8.1-8.6') union all select 4 ,rtrim('五十米'),'男',rtrim('8.6-9') union all select 3 ,rtrim('五十米'),'男',rtrim('≥9') union all select 7 ,rtrim('五十米'),'女',rtrim('≤9') union all select 6 ,rtrim('五十米'),'女',rtrim('9-9.4') union all select 5 ,rtrim('五十米'),'女',rtrim('9.4-9.7') union all select 4 ,rtrim('五十米'),'女',rtrim('9.7-10.6') union all select 3 ,rtrim('五十米'),'女',rtrim('≥10.7') --->查询 declare @LeiBie varchar(20),@XingBie varchar(10),@ShuLiang dec(18,2) set @LeiBie='五十米' set @XingBie='男' set @ShuLiang=7.9 select 分值 from ( select 分值, 类别, 性别, 数量1=left(数量,charindex('-',数量)-1), 数量2=right(数量,len(数量)-charindex('-',数量)) from ( select 分值, 类别, 性别, 数量= case when charindex('≥',数量)>0 then replace(数量,'≥','')+'-999999' when charindex('≤',数量)>0 then '0-'+replace(数量,'≤','') when charindex('<',数量)>0 then '0-'+ltrim(cast(replace(数量,'<','') as dec(18,0))-0.01) else 数量 end from PingFenBiaoZhun2 ) t1 ) t2 where 类别=@LeiBie and 性别=@XingBie and (@ShuLiang between 数量1 and 数量2 or @ShuLiang between 数量2 and 数量1)--->结果 /** 分值 ----------- 6(所影响的行数为 1 行)**/
7.6-8.1表示x>7.6 AND x <=8.1 110-105表示x <110 AND x>=105 这个开区间闭区间能不能统一一下?否则还要多一层比较麻烦的判断我都默认成闭区间了。
--->查询 declare @LeiBie varchar(20),@XingBie varchar(10),@ShuLiang dec(18,2) set @LeiBie='五十米' set @XingBie='男' set @ShuLiang=7.6select 分值 from ( select 分值, 类别, 性别, 数量1=left(数量,charindex('-',数量)-1), 数量2=right(数量,len(数量)-charindex('-',数量)) from ( select 分值, 类别, 性别, 数量= case when charindex('≥',数量)>0 then replace(数量,'≥','')+'-999999' when charindex('≤',数量)>0 then '0-'+replace(数量,'≤','') when charindex('<',数量)>0 then '0-'+ltrim(cast(replace(数量,'<','') as dec(18,0))-0.01) else 数量 end from PingFenBiaoZhun2 ) t1 ) t2 where 类别=@LeiBie and 性别=@XingBie and (@ShuLiang>数量1 and @ShuLiang<=数量2 or @ShuLiang>=数量2 and @ShuLiang<数量1)--->结果 /** 分值 ----------- 7(所影响的行数为 1 行)**/
DECLARE @TB TABLE([分值] INT, [类别] NVARCHAR(4), [性别] NVARCHAR(1), [数量] NVARCHAR(8)) INSERT @TB SELECT 10, N'肺活量', N'男', N'≥67' UNION ALL SELECT 9, N'肺活量', N'男', N'67-58' UNION ALL SELECT 8, N'肺活量', N'男', N'58-50' UNION ALL SELECT 7, N'肺活量', N'男', N'50-46' UNION ALL SELECT 6, N'肺活量', N'男', N'46-40' UNION ALL SELECT 5, N'肺活量', N'男', N'40-31.9' UNION ALL SELECT 4, N'肺活量', N'男', N'≤32' UNION ALL SELECT 10, N'肺活量', N'女', N'≥49' UNION ALL SELECT 9, N'肺活量', N'女', N'49-41' UNION ALL SELECT 8, N'肺活量', N'女', N'41-36' UNION ALL SELECT 7, N'肺活量', N'女', N'36-31' UNION ALL SELECT 6, N'肺活量', N'女', N'31-26' UNION ALL SELECT 5, N'肺活量', N'女', N'26-20.9' UNION ALL SELECT 4, N'肺活量', N'女', N'≤21' UNION ALL SELECT 7, N'跳绳', N'男', N'≥105' UNION ALL SELECT 6, N'跳绳', N'男', N'105-100' UNION ALL SELECT 5, N'跳绳', N'男', N'100-95' UNION ALL SELECT 4, N'跳绳', N'男', N'95-90' UNION ALL SELECT 3, N'跳绳', N'男', N'<90' UNION ALL SELECT 7, N'跳绳', N'女', N'≥110' UNION ALL SELECT 6, N'跳绳', N'女', N'110-105' UNION ALL SELECT 5, N'跳绳', N'女', N'105-100' UNION ALL SELECT 4, N'跳绳', N'女', N'100-95' UNION ALL SELECT 3, N'跳绳', N'女', N'<95' UNION ALL SELECT 7, N'仰卧起坐', N'女', N'≥33' UNION ALL SELECT 6, N'仰卧起坐', N'女', N'33-27' UNION ALL SELECT 5, N'仰卧起坐', N'女', N'27-23' UNION ALL SELECT 4, N'仰卧起坐', N'女', N'23-15' UNION ALL SELECT 3, N'仰卧起坐', N'女', N'<15' UNION ALL SELECT 7, N'立定跳远', N'男', N'≥222' UNION ALL SELECT 6, N'立定跳远', N'男', N'222-210' UNION ALL SELECT 5, N'立定跳远', N'男', N'210-202' UNION ALL SELECT 4, N'立定跳远', N'男', N'202-180' UNION ALL SELECT 3, N'立定跳远', N'男', N'<180' UNION ALL SELECT 7, N'五十米', N'男', N'≤7.6' UNION ALL SELECT 6, N'五十米', N'男', N'7.6-8.1' UNION ALL SELECT 5, N'五十米', N'男', N'8.1-8.6' UNION ALL SELECT 4, N'五十米', N'男', N'8.6-9' UNION ALL SELECT 3, N'五十米', N'男', N'≥9' UNION ALL SELECT 7, N'五十米', N'女', N'≤9' UNION ALL SELECT 6, N'五十米', N'女', N'9-9.4' UNION ALL SELECT 5, N'五十米', N'女', N'9.4-9.7' UNION ALL SELECT 4, N'五十米', N'女', N'9.7-10.6' UNION ALL SELECT 3, N'五十米', N'女', N'≥10.7'DECLARE @LeiBie NVARCHAR(4), @XingBie NVARCHAR(1), @ShuLiang DECIMAL(5,1) SET @LeiBie=N'肺活量' SET @XingBie=N'女' SET @ShuLiang=32 SELECT [分值] FROM ( SELECT *,CASE WHEN CHARINDEX(N'<', [数量])>0 THEN CAST(0 AS DECIMAL(5,1)) WHEN CHARINDEX(N'≤', [数量])>0 THEN 0 WHEN CHARINDEX(N'≥', [数量])>0 THEN REPLACE([数量], N'≥', '') WHEN CHARINDEX(N'-', [数量])>0 THEN CASE WHEN CAST(LEFT([数量], CHARINDEX(N'-', [数量])-1) AS DECIMAL(5,1))- CAST(STUFF([数量], 1, CHARINDEX(N'-', [数量]), '') AS DECIMAL(5,1))>0 THEN CAST(STUFF([数量], 1, CHARINDEX(N'-', [数量]), '') AS DECIMAL(5,1)) ELSE CAST(LEFT([数量], CHARINDEX(N'-', [数量])-1) AS DECIMAL(5,1))+0.1 END END AS MINVAL, CASE WHEN CHARINDEX(N'<', [数量])>0 THEN CAST(REPLACE([数量], N'<', '') AS DECIMAL(5,1))-0.1 WHEN CHARINDEX(N'≤', [数量])>0 THEN REPLACE([数量], N'≤', '') WHEN CHARINDEX(N'≥', [数量])>0 THEN 10000 WHEN CHARINDEX(N'-', [数量])>0 THEN CASE WHEN CAST(LEFT([数量], CHARINDEX(N'-', [数量])-1) AS DECIMAL(5,1))- CAST(STUFF([数量], 1, CHARINDEX(N'-', [数量]), '') AS DECIMAL(5,1))>0 THEN CAST(LEFT([数量], CHARINDEX(N'-', [数量])-1) AS DECIMAL(5,1))-0.1 ELSE CAST(STUFF([数量], 1, CHARINDEX(N'-', [数量]), '') AS DECIMAL(5,1)) END END AS MAXVAL FROM @TB) T WHERE [类别]=@LeiBie AND [性别]=@XingBie AND @ShuLiang BETWEEN MINVAL AND MAXVAL /* 分值 ----------- 7 */
create table PingFenBiaoZhun2(分值 int,类别 varchar(10),性别 varchar(10),数量 varchar(20)) insert into PingFenBiaoZhun2 select 10,rtrim('肺活量'),'男',rtrim('≥67') union all select 9 ,rtrim('肺活量'),'男',rtrim('67-58') union all select 8 ,rtrim('肺活量'),'男',rtrim('58-50') union all select 7 ,rtrim('肺活量'),'男',rtrim('50-46') union all select 6 ,rtrim('肺活量'),'男',rtrim('46-40') union all select 5 ,rtrim('肺活量'),'男',rtrim('40-31.9') union all select 4 ,rtrim('肺活量'),'男',rtrim('≤32') union all select 10,rtrim('肺活量'),'女',rtrim('≥49') union all select 9 ,rtrim('肺活量'),'女',rtrim('49-41') union all select 8 ,rtrim('肺活量'),'女',rtrim('41-36') union all select 7 ,rtrim('肺活量'),'女',rtrim('36-31') union all select 6 ,rtrim('肺活量'),'女',rtrim('31-26') union all select 5 ,rtrim('肺活量'),'女',rtrim('26-20.9') union all select 4 ,rtrim('肺活量'),'女',rtrim('≤21') union all select 7 ,rtrim('跳绳'),'男',rtrim('≥105') union all select 6 ,rtrim('跳绳'),'男',rtrim('105-100') union all select 5 ,rtrim('跳绳'),'男',rtrim('100-95') union all select 4 ,rtrim('跳绳'),'男',rtrim('95-90') union all select 3 ,rtrim('跳绳'),'男',rtrim('<90') union all select 7 ,rtrim('跳绳'),'女',rtrim('≥110') union all select 6 ,rtrim('跳绳'),'女',rtrim('110-105') union all select 5 ,rtrim('跳绳'),'女',rtrim('105-100') union all select 4 ,rtrim('跳绳'),'女',rtrim('100-95') union all select 3 ,rtrim('跳绳'),'女',rtrim('<95') union all select 7 ,rtrim('仰卧起坐'),'女',rtrim('≥33') union all select 6 ,rtrim('仰卧起坐'),'女',rtrim('33-27') union all select 5 ,rtrim('仰卧起坐'),'女',rtrim('27-23') union all select 4 ,rtrim('仰卧起坐'),'女',rtrim('23-15') union all select 3 ,rtrim('仰卧起坐'),'女',rtrim('<15') union all select 7 ,rtrim('立定跳远'),'男',rtrim('≥222') union all select 6 ,rtrim('立定跳远'),'男',rtrim('222-210') union all select 5 ,rtrim('立定跳远'),'男',rtrim('210-202') union all select 4 ,rtrim('立定跳远'),'男',rtrim('202-180') union all select 3 ,rtrim('立定跳远'),'男',rtrim('<180') union all select 7 ,rtrim('五十米'),'男',rtrim('≤7.6') union all select 6 ,rtrim('五十米'),'男',rtrim('7.6-8.1') union all select 5 ,rtrim('五十米'),'男',rtrim('8.1-8.6') union all select 4 ,rtrim('五十米'),'男',rtrim('8.6-9') union all select 3 ,rtrim('五十米'),'男',rtrim('≥9') union all select 7 ,rtrim('五十米'),'女',rtrim('≤9') union all select 6 ,rtrim('五十米'),'女',rtrim('9-9.4') union all select 5 ,rtrim('五十米'),'女',rtrim('9.4-9.7') union all select 4 ,rtrim('五十米'),'女',rtrim('9.7-10.6') union all select 3 ,rtrim('五十米'),'女',rtrim('≥10.7') go create function f_getScore(@LeiBie varchar(10),@XingBie varchar(10),@ShuLiang varchar(20)) returns int as begin declare @ret int set @ret=0 select @ret=分值 from PingFenBiaoZhun2 where 类别=@LeiBie and 性别=@XingBie and case when charindex('-',数量)>0 and @Shuliang>=left(数量,charindex('-',数量)-1) and @Shuliang< stuff(数量,1,charindex('-',数量),'') then 1 when charindex('≥',数量)>0 and @Shuliang>=stuff(数量,1,1,'') then 1 when charindex('≤',数量)>0 and @Shuliang<=stuff(数量,1,1,'') then 1 when charindex('<',数量)>0 and @Shuliang< stuff(数量,1,1,'') then 1 else 0 end = 1
return @ret end go select dbo.f_getScore('五十米', '女', 10.7) /* ------ 3 */ go drop function f_getScore drop table PingFenBiaoZhun2 go
谢李侠 我执行如下 select dbo.f_getScore('肺活量', '男', 57) 得到错误的结果:0 应该是8啊 因为有 union all select 8 ,rtrim('肺活量'),'男',rtrim('58-50')
create function f_getScore(@LeiBie varchar(10),@XingBie varchar(10),@ShuLiang varchar(20)) returns int as begin declare @ret int set @ret=0select @ret=分值 from ( select 分值, 类别, 性别, 数量1=left(数量,charindex('-',数量)-1), 数量2=right(数量,len(数量)-charindex('-',数量)) from ( select 分值, 类别, 性别, 数量= case when charindex('≥',数量)>0 then replace(数量,'≥','')+'-999999' when charindex('≤',数量)>0 then '0-'+replace(数量,'≤','') when charindex('<',数量)>0 then '0-'+ltrim(cast(replace(数量,'<','') as dec(18,0))-0.01) else 数量 end from PingFenBiaoZhun2 ) t1 ) t2 where 类别=@LeiBie and 性别=@XingBie and (@ShuLiang>数量1 and @ShuLiang<=数量2 or @ShuLiang>=数量2 and @ShuLiang<数量1)
return @ret end goselect dbo.f_getScore('肺活量', '男', 57) /**
----------- 8(所影响的行数为 1 行)**/
谢谢回复 目前没发现问题 Plz wait until I think I have tested every details. :)
CREATE TABLE TB([分值] INT, [类别] NVARCHAR(4), [性别] NVARCHAR(1), [数量] NVARCHAR(8)) INSERT TB SELECT 10, N'肺活量', N'男', N'≥67' UNION ALL SELECT 9, N'肺活量', N'男', N'67-58' UNION ALL SELECT 8, N'肺活量', N'男', N'58-50' UNION ALL SELECT 7, N'肺活量', N'男', N'50-46' UNION ALL SELECT 6, N'肺活量', N'男', N'46-40' UNION ALL SELECT 5, N'肺活量', N'男', N'40-31.9' UNION ALL SELECT 4, N'肺活量', N'男', N'≤32' UNION ALL SELECT 10, N'肺活量', N'女', N'≥49' UNION ALL SELECT 9, N'肺活量', N'女', N'49-41' UNION ALL SELECT 8, N'肺活量', N'女', N'41-36' UNION ALL SELECT 7, N'肺活量', N'女', N'36-31' UNION ALL SELECT 6, N'肺活量', N'女', N'31-26' UNION ALL SELECT 5, N'肺活量', N'女', N'26-20.9' UNION ALL SELECT 4, N'肺活量', N'女', N'≤21' UNION ALL SELECT 7, N'跳绳', N'男', N'≥105' UNION ALL SELECT 6, N'跳绳', N'男', N'105-100' UNION ALL SELECT 5, N'跳绳', N'男', N'100-95' UNION ALL SELECT 4, N'跳绳', N'男', N'95-90' UNION ALL SELECT 3, N'跳绳', N'男', N'<90' UNION ALL SELECT 7, N'跳绳', N'女', N'≥110' UNION ALL SELECT 6, N'跳绳', N'女', N'110-105' UNION ALL SELECT 5, N'跳绳', N'女', N'105-100' UNION ALL SELECT 4, N'跳绳', N'女', N'100-95' UNION ALL SELECT 3, N'跳绳', N'女', N'<95' UNION ALL SELECT 7, N'仰卧起坐', N'女', N'≥33' UNION ALL SELECT 6, N'仰卧起坐', N'女', N'33-27' UNION ALL SELECT 5, N'仰卧起坐', N'女', N'27-23' UNION ALL SELECT 4, N'仰卧起坐', N'女', N'23-15' UNION ALL SELECT 3, N'仰卧起坐', N'女', N'<15' UNION ALL SELECT 7, N'立定跳远', N'男', N'≥222' UNION ALL SELECT 6, N'立定跳远', N'男', N'222-210' UNION ALL SELECT 5, N'立定跳远', N'男', N'210-202' UNION ALL SELECT 4, N'立定跳远', N'男', N'202-180' UNION ALL SELECT 3, N'立定跳远', N'男', N'<180' UNION ALL SELECT 7, N'五十米', N'男', N'≤7.6' UNION ALL SELECT 6, N'五十米', N'男', N'7.6-8.1' UNION ALL SELECT 5, N'五十米', N'男', N'8.1-8.6' UNION ALL SELECT 4, N'五十米', N'男', N'8.6-9' UNION ALL SELECT 3, N'五十米', N'男', N'≥9' UNION ALL SELECT 7, N'五十米', N'女', N'≤9' UNION ALL SELECT 6, N'五十米', N'女', N'9-9.4' UNION ALL SELECT 5, N'五十米', N'女', N'9.4-9.7' UNION ALL SELECT 4, N'五十米', N'女', N'9.7-10.6' UNION ALL SELECT 3, N'五十米', N'女', N'≥10.7' GOCREATE FUNCTION f_getScore(@LeiBie NVARCHAR(4), @XingBie NVARCHAR(1), @ShuLiang DECIMAL(5,1)) RETURNS DECIMAL(5,1) AS BEGIN DECLARE @RESULT DECIMAL(5,1)SELECT @RESULT=[分值] FROM ( SELECT *,CASE WHEN CHARINDEX(N'<', [数量])>0 THEN CAST(0 AS DECIMAL(5,1)) WHEN CHARINDEX(N'≤', [数量])>0 THEN 0 WHEN CHARINDEX(N'≥', [数量])>0 THEN REPLACE([数量], N'≥', '') WHEN CHARINDEX(N'-', [数量])>0 THEN CASE WHEN CAST(LEFT([数量], CHARINDEX(N'-', [数量])-1) AS DECIMAL(5,1))- CAST(STUFF([数量], 1, CHARINDEX(N'-', [数量]), '') AS DECIMAL(5,1))>0 THEN CAST(STUFF([数量], 1, CHARINDEX(N'-', [数量]), '') AS DECIMAL(5,1)) ELSE CAST(LEFT([数量], CHARINDEX(N'-', [数量])-1) AS DECIMAL(5,1))+0.1 END END AS MINVAL, CASE WHEN CHARINDEX(N'<', [数量])>0 THEN CAST(REPLACE([数量], N'<', '') AS DECIMAL(5,1))-0.1 WHEN CHARINDEX(N'≤', [数量])>0 THEN REPLACE([数量], N'≤', '') WHEN CHARINDEX(N'≥', [数量])>0 THEN 10000 WHEN CHARINDEX(N'-', [数量])>0 THEN CASE WHEN CAST(LEFT([数量], CHARINDEX(N'-', [数量])-1) AS DECIMAL(5,1))- CAST(STUFF([数量], 1, CHARINDEX(N'-', [数量]), '') AS DECIMAL(5,1))>0 THEN CAST(LEFT([数量], CHARINDEX(N'-', [数量])-1) AS DECIMAL(5,1))-0.1 ELSE CAST(STUFF([数量], 1, CHARINDEX(N'-', [数量]), '') AS DECIMAL(5,1)) END END AS MAXVAL FROM TB) T WHERE [类别]=@LeiBie AND [性别]=@XingBie AND @ShuLiang BETWEEN MINVAL AND MAXVAL RETURN @RESULT END GO SELECT dbo.f_getScore(N'跳绳', N'男', 105 )DROP FUNCTION f_getScore DROP TABLE TB /* 7.0 */
110-105表示x <110 AND x>=105 按照你的算法,这种形式的前面一个应该不是包含的,所以这两个测试结果应该都是0 前面那个没问题 后面那个类型没转换好,再改了一下 create function f_getScore(@LeiBie varchar(10),@XingBie varchar(10),@ShuLiang varchar(20)) returns int as begin declare @ret int set @ret=0select @ret=分值 from ( select 分值, 类别, 性别, 数量1=cast(left(数量,charindex('-',数量)-1) as dec(18,2)), 数量2=cast(right(数量,len(数量)-charindex('-',数量)) as dec(18,2)) from ( select 分值, 类别, 性别, 数量= case when charindex('≥',数量)>0 then replace(数量,'≥','')+'-999999' when charindex('≤',数量)>0 then '0-'+replace(数量,'≤','') when charindex('<',数量)>0 then '0-'+ltrim(cast(replace(数量,'<','') as dec(18,0))-0.01) else 数量 end from PingFenBiaoZhun2 ) t1 ) t2 where 类别=@LeiBie and 性别=@XingBie and (@ShuLiang>数量1 and @ShuLiang<=数量2 or @ShuLiang>=数量2 and @ShuLiang<数量1)
return @ret end go
不好意思,搞错了一个“≥”的判断,再改一个-->函数 create function f_getScore(@LeiBie varchar(10),@XingBie varchar(10),@ShuLiang varchar(20)) returns int as begin declare @ret int set @ret=0select @ret=分值 from ( select 分值, 类别, 性别, 数量1=cast(left(数量,charindex('-',数量)-1) as dec(18,2)), 数量2=cast(right(数量,len(数量)-charindex('-',数量)) as dec(18,2)) from ( select 分值, 类别, 性别, 数量= case when charindex('≥',数量)>0 then ltrim(cast(replace(数量,'≥','') as dec(18,2))-0.01)+'-999999' when charindex('≤',数量)>0 then '0-'+replace(数量,'≤','') when charindex('<',数量)>0 then '0-'+ltrim(cast(replace(数量,'<','') as dec(18,0))-0.01) else 数量 end from PingFenBiaoZhun2 ) t1 ) t2 where 类别=@LeiBie and 性别=@XingBie and (@ShuLiang>数量1 and @ShuLiang<=数量2 or @ShuLiang>=数量2 and @ShuLiang<数量1)
return @ret end go select dbo.f_getScore('立定跳远', '男',222) /**
----------- 7(所影响的行数为 1 行)**/
select dbo.f_getScore('跳绳', '男',105) /**
----------- 7(所影响的行数为 1 行)**/--drop function f_getscore
try:create function f_getScore(@LeiBie varchar(10),@XingBie varchar(10),@ShuLiang varchar(20)) returns int as begin declare @ret int set @ret=0 select @ret=分值 from PingFenBiaoZhun2 where 类别=@LeiBie and 性别=@XingBie and case when charindex('-',数量)>0 and @Shuliang<left(数量,charindex('-',数量)-1) and @Shuliang>=stuff(数量,1,charindex('-',数量),'') then 1 when charindex('≥',数量)>0 and @Shuliang>=stuff(数量,1,1,'') then 1 when charindex('≤',数量)>0 and @Shuliang<=stuff(数量,1,1,'') then 1 when charindex('<',数量)>0 and @Shuliang< stuff(数量,1,1,'') then 1 else 0 end = 1
return @ret end go
就这么几条记录,建议楼主把数据表重新设计一下,数据重新填一下,查起来效率要高多了! 程序设计,简洁为上! create table PingFenBiaoZhun2(分值 int,类别 varchar(10),性别 varchar(10),minvalue decimal(8,2),maxvalue decimal(8,2)) insert into PingFenBiaoZhun2 select 10,rtrim('肺活量'),'男',67,10000 union all select 9 ,rtrim('肺活量'),'男',58,66.99 union all select 8 ,rtrim('肺活量'),'男',50,57.99 union all select 7 ,rtrim('肺活量'),'男',46,49.99 union all select 6 ,rtrim('肺活量'),'男',40,45.99 union all select 5 ,rtrim('肺活量'),'男',32.01,39.99 union all select 4 ,rtrim('肺活量'),'男',0,32 union all select 10,rtrim('肺活量'),'女',49,10000 union all select 9 ,rtrim('肺活量'),'女',41,48.99 union all select 8 ,rtrim('肺活量'),'女',36,40.99 union all select 7 ,rtrim('肺活量'),'女',31,35.99 union all select 6 ,rtrim('肺活量'),'女',26,30.99 union all select 5 ,rtrim('肺活量'),'女',21.01,25.99 union all select 4 ,rtrim('肺活量'),'女',0,21 union all select 7 ,rtrim('跳绳'),'男',105,10000 union all select 6 ,rtrim('跳绳'),'男',100,104.99 union all select 5 ,rtrim('跳绳'),'男',95,99.99 union all select 4 ,rtrim('跳绳'),'男',90.01,94.99 union all select 3 ,rtrim('跳绳'),'男',0,90 union all select 7 ,rtrim('跳绳'),'女',110,100000 union all select 6 ,rtrim('跳绳'),'女',105,109.99 union all select 5 ,rtrim('跳绳'),'女',100,104.99 union all select 4 ,rtrim('跳绳'),'女',95,99.99 union all select 3 ,rtrim('跳绳'),'女',0,94.99 union all select 7 ,rtrim('仰卧起坐'),'女',33,10000 union all select 6 ,rtrim('仰卧起坐'),'女',27,32.99 union all select 5 ,rtrim('仰卧起坐'),'女',23,26.99 union all select 4 ,rtrim('仰卧起坐'),'女',15,22.99 union all select 3 ,rtrim('仰卧起坐'),'女',0,14.99 union all select 7 ,rtrim('立定跳远'),'男',222,10000 union all select 6 ,rtrim('立定跳远'),'男',210,221.99 union all select 5 ,rtrim('立定跳远'),'男',180,209.99 union all select 4 ,rtrim('立定跳远'),'男',0,179.99 union all select 3 ,rtrim('立定跳远'),'男',36,41 union all select 7 ,rtrim('五十米'),'男',0,7.6 union all select 6 ,rtrim('五十米'),'男',7.61,8.1 union all select 5 ,rtrim('五十米'),'男',8.11,8.6 union all select 4 ,rtrim('五十米'),'男',8.61,8.99 union all select 3 ,rtrim('五十米'),'男',9,10000 union all select 7 ,rtrim('五十米'),'女',0,9 union all select 6 ,rtrim('五十米'),'女',9.01,9.4 union all select 5 ,rtrim('五十米'),'女',9.41,9.7 union all select 4 ,rtrim('五十米'),'女',9.71,10.69 union all select 3 ,rtrim('五十米'),'女',10.7,10000 go create function f_getScore(@LeiBie varchar(10),@XingBie varchar(10),@ShuLiang varchar(20)) returns int as begin declare @ret int select @ret=分值 from PingFenBiaoZhun2 where 类别=@LeiBie and 性别=@XingBie and @ShuLiang>=minValue and @shuliang<=maxValue return @ret end goselect dbo.f_getScore('五十米', '女', 10.7) go drop function f_getScore drop table PingFenBiaoZhun2 /* ----------- 3 */ 只要能干好活,我们不需要为技术的技术.
union all select 9 ,rtrim('肺活量'),'男',rtrim('67-58')
union all select 8 ,rtrim('肺活量'),'男',rtrim('58-50')
union all select 7 ,rtrim('肺活量'),'男',rtrim('50-46')
union all select 6 ,rtrim('肺活量'),'男',rtrim('46-40')
union all select 5 ,rtrim('肺活量'),'男',rtrim('40-31.9')
union all select 4 ,rtrim('肺活量'),'男',rtrim('≤32')
union all select 10,rtrim('肺活量'),'女',rtrim('≥49')
union all select 9 ,rtrim('肺活量'),'女',rtrim('49-41')
union all select 8 ,rtrim('肺活量'),'女',rtrim('41-36')
union all select 7 ,rtrim('肺活量'),'女',rtrim('36-31')
union all select 6 ,rtrim('肺活量'),'女',rtrim('31-26')
union all select 5 ,rtrim('肺活量'),'女',rtrim('26-20.9')
union all select 4 ,rtrim('肺活量'),'女',rtrim('≤21')
union all select 7 ,rtrim('跳绳'),'男',rtrim('≥105')
union all select 6 ,rtrim('跳绳'),'男',rtrim('105-100')
union all select 5 ,rtrim('跳绳'),'男',rtrim('100-95')
union all select 4 ,rtrim('跳绳'),'男',rtrim('95-90')
union all select 3 ,rtrim('跳绳'),'男',rtrim('<90')
union all select 7 ,rtrim('跳绳'),'女',rtrim('≥110')
union all select 6 ,rtrim('跳绳'),'女',rtrim('110-105')
union all select 5 ,rtrim('跳绳'),'女',rtrim('105-100')
union all select 4 ,rtrim('跳绳'),'女',rtrim('100-95')
union all select 3 ,rtrim('跳绳'),'女',rtrim('<95')
union all select 7 ,rtrim('仰卧起坐'),'女',rtrim('≥33')
union all select 6 ,rtrim('仰卧起坐'),'女',rtrim('33-27')
union all select 5 ,rtrim('仰卧起坐'),'女',rtrim('27-23')
union all select 4 ,rtrim('仰卧起坐'),'女',rtrim('23-15')
union all select 3 ,rtrim('仰卧起坐'),'女',rtrim('<15')
union all select 7 ,rtrim('立定跳远'),'男',rtrim('≥222')
union all select 6 ,rtrim('立定跳远'),'男',rtrim('222-210')
union all select 5 ,rtrim('立定跳远'),'男',rtrim('210-202')
union all select 4 ,rtrim('立定跳远'),'男',rtrim('202-180')
union all select 3 ,rtrim('立定跳远'),'男',rtrim('<180')
union all select 7 ,rtrim('五十米'),'男',rtrim('≤7.6')
union all select 6 ,rtrim('五十米'),'男',rtrim('7.6-8.1')
union all select 5 ,rtrim('五十米'),'男',rtrim('8.1-8.6')
union all select 4 ,rtrim('五十米'),'男',rtrim('8.6-9')
union all select 3 ,rtrim('五十米'),'男',rtrim('≥9')
union all select 7 ,rtrim('五十米'),'女',rtrim('≤9')
union all select 6 ,rtrim('五十米'),'女',rtrim('9-9.4')
union all select 5 ,rtrim('五十米'),'女',rtrim('9.4-9.7')
union all select 4 ,rtrim('五十米'),'女',rtrim('9.7-10.6')
union all select 3 ,rtrim('五十米'),'女',rtrim('≥10.7') --->查询
declare @LeiBie varchar(20),@XingBie varchar(10),@ShuLiang dec(18,2)
set @LeiBie='五十米'
set @XingBie='男'
set @ShuLiang=7.9 select
分值
from
(
select
分值,
类别,
性别,
数量1=left(数量,charindex('-',数量)-1),
数量2=right(数量,len(数量)-charindex('-',数量))
from
(
select
分值,
类别,
性别,
数量= case
when charindex('≥',数量)>0 then replace(数量,'≥','')+'-999999'
when charindex('≤',数量)>0 then '0-'+replace(数量,'≤','')
when charindex('<',数量)>0 then '0-'+ltrim(cast(replace(数量,'<','') as dec(18,0))-0.01)
else 数量
end
from
PingFenBiaoZhun2
) t1
) t2
where
类别=@LeiBie
and
性别=@XingBie
and
(@ShuLiang between 数量1 and 数量2 or @ShuLiang between 数量2 and 数量1)--->结果
/**
分值
-----------
6(所影响的行数为 1 行)**/
110-105表示x <110 AND x>=105 这个开区间闭区间能不能统一一下?否则还要多一层比较麻烦的判断我都默认成闭区间了。
declare @LeiBie varchar(20),@XingBie varchar(10),@ShuLiang dec(18,2)
set @LeiBie='五十米'
set @XingBie='男'
set @ShuLiang=7.6select
分值
from
(
select
分值,
类别,
性别,
数量1=left(数量,charindex('-',数量)-1),
数量2=right(数量,len(数量)-charindex('-',数量))
from
(
select
分值,
类别,
性别,
数量= case
when charindex('≥',数量)>0 then replace(数量,'≥','')+'-999999'
when charindex('≤',数量)>0 then '0-'+replace(数量,'≤','')
when charindex('<',数量)>0 then '0-'+ltrim(cast(replace(数量,'<','') as dec(18,0))-0.01)
else 数量
end
from
PingFenBiaoZhun2
) t1
) t2
where
类别=@LeiBie
and
性别=@XingBie
and
(@ShuLiang>数量1 and @ShuLiang<=数量2 or @ShuLiang>=数量2 and @ShuLiang<数量1)--->结果
/**
分值
-----------
7(所影响的行数为 1 行)**/
INSERT @TB
SELECT 10, N'肺活量', N'男', N'≥67' UNION ALL
SELECT 9, N'肺活量', N'男', N'67-58' UNION ALL
SELECT 8, N'肺活量', N'男', N'58-50' UNION ALL
SELECT 7, N'肺活量', N'男', N'50-46' UNION ALL
SELECT 6, N'肺活量', N'男', N'46-40' UNION ALL
SELECT 5, N'肺活量', N'男', N'40-31.9' UNION ALL
SELECT 4, N'肺活量', N'男', N'≤32' UNION ALL
SELECT 10, N'肺活量', N'女', N'≥49' UNION ALL
SELECT 9, N'肺活量', N'女', N'49-41' UNION ALL
SELECT 8, N'肺活量', N'女', N'41-36' UNION ALL
SELECT 7, N'肺活量', N'女', N'36-31' UNION ALL
SELECT 6, N'肺活量', N'女', N'31-26' UNION ALL
SELECT 5, N'肺活量', N'女', N'26-20.9' UNION ALL
SELECT 4, N'肺活量', N'女', N'≤21' UNION ALL
SELECT 7, N'跳绳', N'男', N'≥105' UNION ALL
SELECT 6, N'跳绳', N'男', N'105-100' UNION ALL
SELECT 5, N'跳绳', N'男', N'100-95' UNION ALL
SELECT 4, N'跳绳', N'男', N'95-90' UNION ALL
SELECT 3, N'跳绳', N'男', N'<90' UNION ALL
SELECT 7, N'跳绳', N'女', N'≥110' UNION ALL
SELECT 6, N'跳绳', N'女', N'110-105' UNION ALL
SELECT 5, N'跳绳', N'女', N'105-100' UNION ALL
SELECT 4, N'跳绳', N'女', N'100-95' UNION ALL
SELECT 3, N'跳绳', N'女', N'<95' UNION ALL
SELECT 7, N'仰卧起坐', N'女', N'≥33' UNION ALL
SELECT 6, N'仰卧起坐', N'女', N'33-27' UNION ALL
SELECT 5, N'仰卧起坐', N'女', N'27-23' UNION ALL
SELECT 4, N'仰卧起坐', N'女', N'23-15' UNION ALL
SELECT 3, N'仰卧起坐', N'女', N'<15' UNION ALL
SELECT 7, N'立定跳远', N'男', N'≥222' UNION ALL
SELECT 6, N'立定跳远', N'男', N'222-210' UNION ALL
SELECT 5, N'立定跳远', N'男', N'210-202' UNION ALL
SELECT 4, N'立定跳远', N'男', N'202-180' UNION ALL
SELECT 3, N'立定跳远', N'男', N'<180' UNION ALL
SELECT 7, N'五十米', N'男', N'≤7.6' UNION ALL
SELECT 6, N'五十米', N'男', N'7.6-8.1' UNION ALL
SELECT 5, N'五十米', N'男', N'8.1-8.6' UNION ALL
SELECT 4, N'五十米', N'男', N'8.6-9' UNION ALL
SELECT 3, N'五十米', N'男', N'≥9' UNION ALL
SELECT 7, N'五十米', N'女', N'≤9' UNION ALL
SELECT 6, N'五十米', N'女', N'9-9.4' UNION ALL
SELECT 5, N'五十米', N'女', N'9.4-9.7' UNION ALL
SELECT 4, N'五十米', N'女', N'9.7-10.6' UNION ALL
SELECT 3, N'五十米', N'女', N'≥10.7'DECLARE @LeiBie NVARCHAR(4), @XingBie NVARCHAR(1), @ShuLiang DECIMAL(5,1)
SET @LeiBie=N'肺活量'
SET @XingBie=N'女'
SET @ShuLiang=32 SELECT [分值]
FROM (
SELECT *,CASE WHEN CHARINDEX(N'<', [数量])>0 THEN CAST(0 AS DECIMAL(5,1))
WHEN CHARINDEX(N'≤', [数量])>0 THEN 0
WHEN CHARINDEX(N'≥', [数量])>0 THEN REPLACE([数量], N'≥', '')
WHEN CHARINDEX(N'-', [数量])>0 THEN CASE WHEN CAST(LEFT([数量], CHARINDEX(N'-', [数量])-1) AS DECIMAL(5,1))- CAST(STUFF([数量], 1, CHARINDEX(N'-', [数量]), '') AS DECIMAL(5,1))>0
THEN CAST(STUFF([数量], 1, CHARINDEX(N'-', [数量]), '') AS DECIMAL(5,1))
ELSE CAST(LEFT([数量], CHARINDEX(N'-', [数量])-1) AS DECIMAL(5,1))+0.1
END
END AS MINVAL,
CASE WHEN CHARINDEX(N'<', [数量])>0 THEN CAST(REPLACE([数量], N'<', '') AS DECIMAL(5,1))-0.1
WHEN CHARINDEX(N'≤', [数量])>0 THEN REPLACE([数量], N'≤', '')
WHEN CHARINDEX(N'≥', [数量])>0 THEN 10000
WHEN CHARINDEX(N'-', [数量])>0 THEN CASE WHEN CAST(LEFT([数量], CHARINDEX(N'-', [数量])-1) AS DECIMAL(5,1))- CAST(STUFF([数量], 1, CHARINDEX(N'-', [数量]), '') AS DECIMAL(5,1))>0
THEN CAST(LEFT([数量], CHARINDEX(N'-', [数量])-1) AS DECIMAL(5,1))-0.1
ELSE CAST(STUFF([数量], 1, CHARINDEX(N'-', [数量]), '') AS DECIMAL(5,1))
END
END AS MAXVAL
FROM @TB) T
WHERE [类别]=@LeiBie AND [性别]=@XingBie AND @ShuLiang BETWEEN MINVAL AND MAXVAL
/*
分值
-----------
7
*/
union all select 9 ,rtrim('肺活量'),'男',rtrim('67-58')
union all select 8 ,rtrim('肺活量'),'男',rtrim('58-50')
union all select 7 ,rtrim('肺活量'),'男',rtrim('50-46')
union all select 6 ,rtrim('肺活量'),'男',rtrim('46-40')
union all select 5 ,rtrim('肺活量'),'男',rtrim('40-31.9')
union all select 4 ,rtrim('肺活量'),'男',rtrim('≤32')
union all select 10,rtrim('肺活量'),'女',rtrim('≥49')
union all select 9 ,rtrim('肺活量'),'女',rtrim('49-41')
union all select 8 ,rtrim('肺活量'),'女',rtrim('41-36')
union all select 7 ,rtrim('肺活量'),'女',rtrim('36-31')
union all select 6 ,rtrim('肺活量'),'女',rtrim('31-26')
union all select 5 ,rtrim('肺活量'),'女',rtrim('26-20.9')
union all select 4 ,rtrim('肺活量'),'女',rtrim('≤21')
union all select 7 ,rtrim('跳绳'),'男',rtrim('≥105')
union all select 6 ,rtrim('跳绳'),'男',rtrim('105-100')
union all select 5 ,rtrim('跳绳'),'男',rtrim('100-95')
union all select 4 ,rtrim('跳绳'),'男',rtrim('95-90')
union all select 3 ,rtrim('跳绳'),'男',rtrim('<90')
union all select 7 ,rtrim('跳绳'),'女',rtrim('≥110')
union all select 6 ,rtrim('跳绳'),'女',rtrim('110-105')
union all select 5 ,rtrim('跳绳'),'女',rtrim('105-100')
union all select 4 ,rtrim('跳绳'),'女',rtrim('100-95')
union all select 3 ,rtrim('跳绳'),'女',rtrim('<95')
union all select 7 ,rtrim('仰卧起坐'),'女',rtrim('≥33')
union all select 6 ,rtrim('仰卧起坐'),'女',rtrim('33-27')
union all select 5 ,rtrim('仰卧起坐'),'女',rtrim('27-23')
union all select 4 ,rtrim('仰卧起坐'),'女',rtrim('23-15')
union all select 3 ,rtrim('仰卧起坐'),'女',rtrim('<15')
union all select 7 ,rtrim('立定跳远'),'男',rtrim('≥222')
union all select 6 ,rtrim('立定跳远'),'男',rtrim('222-210')
union all select 5 ,rtrim('立定跳远'),'男',rtrim('210-202')
union all select 4 ,rtrim('立定跳远'),'男',rtrim('202-180')
union all select 3 ,rtrim('立定跳远'),'男',rtrim('<180')
union all select 7 ,rtrim('五十米'),'男',rtrim('≤7.6')
union all select 6 ,rtrim('五十米'),'男',rtrim('7.6-8.1')
union all select 5 ,rtrim('五十米'),'男',rtrim('8.1-8.6')
union all select 4 ,rtrim('五十米'),'男',rtrim('8.6-9')
union all select 3 ,rtrim('五十米'),'男',rtrim('≥9')
union all select 7 ,rtrim('五十米'),'女',rtrim('≤9')
union all select 6 ,rtrim('五十米'),'女',rtrim('9-9.4')
union all select 5 ,rtrim('五十米'),'女',rtrim('9.4-9.7')
union all select 4 ,rtrim('五十米'),'女',rtrim('9.7-10.6')
union all select 3 ,rtrim('五十米'),'女',rtrim('≥10.7')
go
create function f_getScore(@LeiBie varchar(10),@XingBie varchar(10),@ShuLiang varchar(20))
returns int
as
begin
declare @ret int
set @ret=0 select
@ret=分值
from
PingFenBiaoZhun2
where
类别=@LeiBie and 性别=@XingBie
and
case when charindex('-',数量)>0 and @Shuliang>=left(数量,charindex('-',数量)-1) and @Shuliang< stuff(数量,1,charindex('-',数量),'') then 1
when charindex('≥',数量)>0 and @Shuliang>=stuff(数量,1,1,'') then 1
when charindex('≤',数量)>0 and @Shuliang<=stuff(数量,1,1,'') then 1
when charindex('<',数量)>0 and @Shuliang< stuff(数量,1,1,'') then 1
else 0
end = 1
return @ret
end
go
select dbo.f_getScore('五十米', '女', 10.7)
/*
------
3
*/
go
drop function f_getScore
drop table PingFenBiaoZhun2
go
我执行如下
select dbo.f_getScore('肺活量', '男', 57)
得到错误的结果:0
应该是8啊
因为有
union all select 8 ,rtrim('肺活量'),'男',rtrim('58-50')
returns int
as
begin
declare @ret int
set @ret=0select
@ret=分值
from
(
select
分值,
类别,
性别,
数量1=left(数量,charindex('-',数量)-1),
数量2=right(数量,len(数量)-charindex('-',数量))
from
(
select
分值,
类别,
性别,
数量= case
when charindex('≥',数量)>0 then replace(数量,'≥','')+'-999999'
when charindex('≤',数量)>0 then '0-'+replace(数量,'≤','')
when charindex('<',数量)>0 then '0-'+ltrim(cast(replace(数量,'<','') as dec(18,0))-0.01)
else 数量
end
from
PingFenBiaoZhun2
) t1
) t2
where
类别=@LeiBie
and
性别=@XingBie
and
(@ShuLiang>数量1 and @ShuLiang<=数量2 or @ShuLiang>=数量2 and @ShuLiang<数量1)
return @ret end
goselect dbo.f_getScore('肺活量', '男', 57)
/**
-----------
8(所影响的行数为 1 行)**/
目前没发现问题
Plz wait until I think I have tested every details. :)
应显示7分却是0
select dbo.f_getScore('跳绳', '男',105)
应显示7分却是3
INSERT TB
SELECT 10, N'肺活量', N'男', N'≥67' UNION ALL
SELECT 9, N'肺活量', N'男', N'67-58' UNION ALL
SELECT 8, N'肺活量', N'男', N'58-50' UNION ALL
SELECT 7, N'肺活量', N'男', N'50-46' UNION ALL
SELECT 6, N'肺活量', N'男', N'46-40' UNION ALL
SELECT 5, N'肺活量', N'男', N'40-31.9' UNION ALL
SELECT 4, N'肺活量', N'男', N'≤32' UNION ALL
SELECT 10, N'肺活量', N'女', N'≥49' UNION ALL
SELECT 9, N'肺活量', N'女', N'49-41' UNION ALL
SELECT 8, N'肺活量', N'女', N'41-36' UNION ALL
SELECT 7, N'肺活量', N'女', N'36-31' UNION ALL
SELECT 6, N'肺活量', N'女', N'31-26' UNION ALL
SELECT 5, N'肺活量', N'女', N'26-20.9' UNION ALL
SELECT 4, N'肺活量', N'女', N'≤21' UNION ALL
SELECT 7, N'跳绳', N'男', N'≥105' UNION ALL
SELECT 6, N'跳绳', N'男', N'105-100' UNION ALL
SELECT 5, N'跳绳', N'男', N'100-95' UNION ALL
SELECT 4, N'跳绳', N'男', N'95-90' UNION ALL
SELECT 3, N'跳绳', N'男', N'<90' UNION ALL
SELECT 7, N'跳绳', N'女', N'≥110' UNION ALL
SELECT 6, N'跳绳', N'女', N'110-105' UNION ALL
SELECT 5, N'跳绳', N'女', N'105-100' UNION ALL
SELECT 4, N'跳绳', N'女', N'100-95' UNION ALL
SELECT 3, N'跳绳', N'女', N'<95' UNION ALL
SELECT 7, N'仰卧起坐', N'女', N'≥33' UNION ALL
SELECT 6, N'仰卧起坐', N'女', N'33-27' UNION ALL
SELECT 5, N'仰卧起坐', N'女', N'27-23' UNION ALL
SELECT 4, N'仰卧起坐', N'女', N'23-15' UNION ALL
SELECT 3, N'仰卧起坐', N'女', N'<15' UNION ALL
SELECT 7, N'立定跳远', N'男', N'≥222' UNION ALL
SELECT 6, N'立定跳远', N'男', N'222-210' UNION ALL
SELECT 5, N'立定跳远', N'男', N'210-202' UNION ALL
SELECT 4, N'立定跳远', N'男', N'202-180' UNION ALL
SELECT 3, N'立定跳远', N'男', N'<180' UNION ALL
SELECT 7, N'五十米', N'男', N'≤7.6' UNION ALL
SELECT 6, N'五十米', N'男', N'7.6-8.1' UNION ALL
SELECT 5, N'五十米', N'男', N'8.1-8.6' UNION ALL
SELECT 4, N'五十米', N'男', N'8.6-9' UNION ALL
SELECT 3, N'五十米', N'男', N'≥9' UNION ALL
SELECT 7, N'五十米', N'女', N'≤9' UNION ALL
SELECT 6, N'五十米', N'女', N'9-9.4' UNION ALL
SELECT 5, N'五十米', N'女', N'9.4-9.7' UNION ALL
SELECT 4, N'五十米', N'女', N'9.7-10.6' UNION ALL
SELECT 3, N'五十米', N'女', N'≥10.7'
GOCREATE FUNCTION f_getScore(@LeiBie NVARCHAR(4), @XingBie NVARCHAR(1), @ShuLiang DECIMAL(5,1))
RETURNS DECIMAL(5,1)
AS
BEGIN
DECLARE @RESULT DECIMAL(5,1)SELECT @RESULT=[分值]
FROM (
SELECT *,CASE WHEN CHARINDEX(N'<', [数量])>0 THEN CAST(0 AS DECIMAL(5,1))
WHEN CHARINDEX(N'≤', [数量])>0 THEN 0
WHEN CHARINDEX(N'≥', [数量])>0 THEN REPLACE([数量], N'≥', '')
WHEN CHARINDEX(N'-', [数量])>0 THEN CASE WHEN CAST(LEFT([数量], CHARINDEX(N'-', [数量])-1) AS DECIMAL(5,1))- CAST(STUFF([数量], 1, CHARINDEX(N'-', [数量]), '') AS DECIMAL(5,1))>0
THEN CAST(STUFF([数量], 1, CHARINDEX(N'-', [数量]), '') AS DECIMAL(5,1))
ELSE CAST(LEFT([数量], CHARINDEX(N'-', [数量])-1) AS DECIMAL(5,1))+0.1
END
END AS MINVAL,
CASE WHEN CHARINDEX(N'<', [数量])>0 THEN CAST(REPLACE([数量], N'<', '') AS DECIMAL(5,1))-0.1
WHEN CHARINDEX(N'≤', [数量])>0 THEN REPLACE([数量], N'≤', '')
WHEN CHARINDEX(N'≥', [数量])>0 THEN 10000
WHEN CHARINDEX(N'-', [数量])>0 THEN CASE WHEN CAST(LEFT([数量], CHARINDEX(N'-', [数量])-1) AS DECIMAL(5,1))- CAST(STUFF([数量], 1, CHARINDEX(N'-', [数量]), '') AS DECIMAL(5,1))>0
THEN CAST(LEFT([数量], CHARINDEX(N'-', [数量])-1) AS DECIMAL(5,1))-0.1
ELSE CAST(STUFF([数量], 1, CHARINDEX(N'-', [数量]), '') AS DECIMAL(5,1))
END
END AS MAXVAL
FROM TB) T
WHERE [类别]=@LeiBie AND [性别]=@XingBie AND @ShuLiang BETWEEN MINVAL AND MAXVAL
RETURN @RESULT
END
GO
SELECT dbo.f_getScore(N'跳绳', N'男', 105 )DROP FUNCTION f_getScore
DROP TABLE TB
/*
7.0
*/
前面那个没问题
后面那个类型没转换好,再改了一下
create function f_getScore(@LeiBie varchar(10),@XingBie varchar(10),@ShuLiang varchar(20))
returns int
as
begin
declare @ret int
set @ret=0select
@ret=分值
from
(
select
分值,
类别,
性别,
数量1=cast(left(数量,charindex('-',数量)-1) as dec(18,2)),
数量2=cast(right(数量,len(数量)-charindex('-',数量)) as dec(18,2))
from
(
select
分值,
类别,
性别,
数量= case
when charindex('≥',数量)>0 then replace(数量,'≥','')+'-999999'
when charindex('≤',数量)>0 then '0-'+replace(数量,'≤','')
when charindex('<',数量)>0 then '0-'+ltrim(cast(replace(数量,'<','') as dec(18,0))-0.01)
else 数量
end
from
PingFenBiaoZhun2
) t1
) t2
where
类别=@LeiBie
and
性别=@XingBie
and
(@ShuLiang>数量1 and @ShuLiang<=数量2 or @ShuLiang>=数量2 and @ShuLiang<数量1)
return @ret end
go
create function f_getScore(@LeiBie varchar(10),@XingBie varchar(10),@ShuLiang varchar(20))
returns int
as
begin
declare @ret int
set @ret=0select
@ret=分值
from
(
select
分值,
类别,
性别,
数量1=cast(left(数量,charindex('-',数量)-1) as dec(18,2)),
数量2=cast(right(数量,len(数量)-charindex('-',数量)) as dec(18,2))
from
(
select
分值,
类别,
性别,
数量= case
when charindex('≥',数量)>0 then ltrim(cast(replace(数量,'≥','') as dec(18,2))-0.01)+'-999999'
when charindex('≤',数量)>0 then '0-'+replace(数量,'≤','')
when charindex('<',数量)>0 then '0-'+ltrim(cast(replace(数量,'<','') as dec(18,0))-0.01)
else 数量
end
from
PingFenBiaoZhun2
) t1
) t2
where
类别=@LeiBie
and
性别=@XingBie
and
(@ShuLiang>数量1 and @ShuLiang<=数量2 or @ShuLiang>=数量2 and @ShuLiang<数量1)
return @ret end
go
select dbo.f_getScore('立定跳远', '男',222)
/**
-----------
7(所影响的行数为 1 行)**/
select dbo.f_getScore('跳绳', '男',105)
/**
-----------
7(所影响的行数为 1 行)**/--drop function f_getscore
returns int
as
begin
declare @ret int
set @ret=0 select
@ret=分值
from
PingFenBiaoZhun2
where
类别=@LeiBie and 性别=@XingBie
and
case when charindex('-',数量)>0 and @Shuliang<left(数量,charindex('-',数量)-1) and @Shuliang>=stuff(数量,1,charindex('-',数量),'') then 1
when charindex('≥',数量)>0 and @Shuliang>=stuff(数量,1,1,'') then 1
when charindex('≤',数量)>0 and @Shuliang<=stuff(数量,1,1,'') then 1
when charindex('<',数量)>0 and @Shuliang< stuff(数量,1,1,'') then 1
else 0
end = 1
return @ret
end
go
程序设计,简洁为上!
create table PingFenBiaoZhun2(分值 int,类别 varchar(10),性别 varchar(10),minvalue decimal(8,2),maxvalue decimal(8,2)) insert into PingFenBiaoZhun2 select 10,rtrim('肺活量'),'男',67,10000
union all select 9 ,rtrim('肺活量'),'男',58,66.99
union all select 8 ,rtrim('肺活量'),'男',50,57.99
union all select 7 ,rtrim('肺活量'),'男',46,49.99
union all select 6 ,rtrim('肺活量'),'男',40,45.99
union all select 5 ,rtrim('肺活量'),'男',32.01,39.99
union all select 4 ,rtrim('肺活量'),'男',0,32
union all select 10,rtrim('肺活量'),'女',49,10000
union all select 9 ,rtrim('肺活量'),'女',41,48.99
union all select 8 ,rtrim('肺活量'),'女',36,40.99
union all select 7 ,rtrim('肺活量'),'女',31,35.99
union all select 6 ,rtrim('肺活量'),'女',26,30.99
union all select 5 ,rtrim('肺活量'),'女',21.01,25.99
union all select 4 ,rtrim('肺活量'),'女',0,21
union all select 7 ,rtrim('跳绳'),'男',105,10000
union all select 6 ,rtrim('跳绳'),'男',100,104.99
union all select 5 ,rtrim('跳绳'),'男',95,99.99
union all select 4 ,rtrim('跳绳'),'男',90.01,94.99
union all select 3 ,rtrim('跳绳'),'男',0,90
union all select 7 ,rtrim('跳绳'),'女',110,100000
union all select 6 ,rtrim('跳绳'),'女',105,109.99
union all select 5 ,rtrim('跳绳'),'女',100,104.99
union all select 4 ,rtrim('跳绳'),'女',95,99.99
union all select 3 ,rtrim('跳绳'),'女',0,94.99
union all select 7 ,rtrim('仰卧起坐'),'女',33,10000
union all select 6 ,rtrim('仰卧起坐'),'女',27,32.99
union all select 5 ,rtrim('仰卧起坐'),'女',23,26.99
union all select 4 ,rtrim('仰卧起坐'),'女',15,22.99
union all select 3 ,rtrim('仰卧起坐'),'女',0,14.99
union all select 7 ,rtrim('立定跳远'),'男',222,10000
union all select 6 ,rtrim('立定跳远'),'男',210,221.99
union all select 5 ,rtrim('立定跳远'),'男',180,209.99
union all select 4 ,rtrim('立定跳远'),'男',0,179.99
union all select 3 ,rtrim('立定跳远'),'男',36,41
union all select 7 ,rtrim('五十米'),'男',0,7.6
union all select 6 ,rtrim('五十米'),'男',7.61,8.1
union all select 5 ,rtrim('五十米'),'男',8.11,8.6
union all select 4 ,rtrim('五十米'),'男',8.61,8.99
union all select 3 ,rtrim('五十米'),'男',9,10000
union all select 7 ,rtrim('五十米'),'女',0,9
union all select 6 ,rtrim('五十米'),'女',9.01,9.4
union all select 5 ,rtrim('五十米'),'女',9.41,9.7
union all select 4 ,rtrim('五十米'),'女',9.71,10.69
union all select 3 ,rtrim('五十米'),'女',10.7,10000
go
create function f_getScore(@LeiBie varchar(10),@XingBie varchar(10),@ShuLiang varchar(20))
returns int
as
begin
declare @ret int select @ret=分值 from PingFenBiaoZhun2 where 类别=@LeiBie and 性别=@XingBie and @ShuLiang>=minValue and @shuliang<=maxValue return @ret
end
goselect dbo.f_getScore('五十米', '女', 10.7)
go
drop function f_getScore
drop table PingFenBiaoZhun2
/*
-----------
3
*/
只要能干好活,我们不需要为技术的技术.