tsql:求函数从表PingFenBiaoZhun2查讲并返回"分值"

Question

表PingFenBiaoZhun2全部内容如下:
分值 类别 性别 数量
10 肺活量 男 ≥67
9 肺活量 男 67-58
8 肺活量 男 58-50
7 肺活量 男 50-46
6 肺活量 男 46-40
5 肺活量 男 40-31.9
4 肺活量 男 ≤32
10 肺活量 女 ≥49
9 肺活量 女 49-41
8 肺活量 女 41-36
7 肺活量 女 36-31
6 肺活量 女 31-26
5 肺活量 女 26-20.9
4 肺活量 女 ≤21
7 跳绳 男 ≥105
6 跳绳 男 105-100
5 跳绳 男 100-95
4 跳绳 男 95-90
3 跳绳 男 ＜90
7 跳绳 女 ≥110
6 跳绳 女 110-105
5 跳绳 女 105-100
4 跳绳 女 100-95
3 跳绳 女 ＜95
7 仰卧起坐 女 ≥33
6 仰卧起坐 女 33-27
5 仰卧起坐 女 27-23
4 仰卧起坐 女 23-15
3 仰卧起坐 女 ＜15
7 立定跳远 男 ≥222
6 立定跳远 男 222-210
5 立定跳远 男 210-202
4 立定跳远 男 202-180
3 立定跳远 男 ＜180
7 五十米 男 ≤7.6
6 五十米 男 7.6-8.1
5 五十米 男 8.1-8.6
4 五十米 男 8.6-9
3 五十米 男 ≥9
7 五十米 女 ≤9
6 五十米 女 9-9.4
5 五十米 女 9.4-9.7
4 五十米 女 9.7-10.6
3 五十米 女 ≥10.7表中"数量"列中的符号说明: "≥≤＜"都是全角,"-"是减号
"-"号的含义举例:
7.6-8.1表示x>7.6 AND x<=8.1
110-105表示x<110 AND x>=105
特别注意"五十米"类别的行是小值在减号前大值在减号后,而其它类别的行相反其它任号≥≤＜含义明显,不说了要求函数使用两个参数:@LeiBie,@XingBie,@ShuLiang
要求函数返回一个整数值,该整数值是对应@LeiBie,@XingBie,@ShuLiang的行的"分值",如无符合条件的行,返回0,如转入的参数@LeiBie,@XingBie,@ShuLiang三者中有任一个是NULL,返回0举四个例子如下:
select MyFunc '五十米','男',7.9
显示6
select MyFunc '五十米','男',7.6
显示7
select MyFunc '肺活量','女',31
显示7
select MyFunc '肺活量','女',32
显示7

Answer 1

为了让大侠们方便些,附上建表代码: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')  

Answer 2

http://topic.csdn.net/u/20090620/08/519db02b-6067-4f93-92b9-76ce16f3f1af.html?seed=995896175

Answer 3

谢谢关注,这个在我的顶楼已说明,复制如下:
"-"号的含义举例: 
7.6-8.1表示x>7.6 AND x <=8.1 
110-105表示x <110 AND x>=105 
特别注意"五十米"类别的行是小值在减号前大值在减号后,而其它类别的行相反 

Answer 4

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

Answer 5

--測試數據
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 dbo.MyFunc 
(
@class nvarchar(10)
,@sex nchar(1)
,@amount decimal(5,2)
)
returns int
as
begin
declare @score int;
select @score=分值
from PingFenBiaoZhun2
where 类别=@class 
  and 性别=@sex
  and 1=(case when charindex('≥',数量)>0 and @amount>=cast(SUBSTRING(数量,2,LEN(数量)-1) as decimal(5,2)) then 1 
  when charindex('≤',数量)>0 and @amount<=cast(SUBSTRING(数量,2,LEN(数量)-1) as decimal(5,2)) then 1
  when charindex('＞',数量)>0 and @amount>cast(SUBSTRING(数量,2,LEN(数量)-1) as decimal(5,2)) then 1
  when charindex('＜',数量)>0 and @amount<cast(SUBSTRING(数量,2,LEN(数量)-1) as decimal(5,2)) then 1
  when charindex('-',数量)>0 and @amount<=cast(RIGHT(数量,len(数量)-CHARINDEX('-',数量)) as decimal(5,2))
 and @amount>cast(left(数量,CHARINDEX('-',数量)-1) as decimal(5,2)) then 1
  else 0 end
 );

return @score;
endgo
--測試
select dbo.MyFunc(N'五十米',N'男',7.6)--刪除測試數據
drop function dbo.MyFunc
drop table PingFenBiaoZhun2

Answer 6

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
*/