select A.userID, A.Score, (select max(Level) from Leveltbl where Score < A.Score) Level from [user] A
select userID,Score ,level=(case score <49 then 0 score between 50 and 99 then 1 score between 100 and 149 then 2 score between 150 and 200 then 3 )
tj_dns(愉快的登山者) 写的非常正确。如果level为字符型的话select A.userID, A.Score, (select max(cast(Level as int)) from Leveltbl where Score < A.Score) Level from [user] A.
两种方法 方法1. 直接判断 select *,(select max(level) from level where score<=a.score) as level from user a
方法2. 将你的级别处理一下,使它具有上下限select a.*,b.level from @user a,( select a.level,a.score,b.score-1 as score1 from @level a left join @level b on a.level+1=b.level ) b where a.score between b.score and isnull(b.score1,a.score)
所以不用写存储过程,用一条语句就能实现了下面是带数据的测试过程:--定义数据测试环境 declare @level table(Level int, Score int) insert into @level select 0,0 union all select 1,50 union all select 2,100 union all select 3,150declare @user table(userID int,Score int) insert into @user select 1010,34 union all select 1011,97 union all select 1012,150--用第一种方法得到结果 select *,(select max(level) from @level where score<=a.score) as level from @user a--用第二种方法得到结果 select a.*,b.level from @user a,( select a.level,a.score,b.score-1 as score1 from @level a left join @level b on a.level+1=b.level ) b where a.score between b.score and isnull(b.score1,a.score)
(select max(Level) from Leveltbl where Score < A.Score) Level
from [user] A
score <49 then 0
score between 50 and 99 then 1
score between 100 and 149 then 2
score between 150 and 200 then 3
)
(select max(cast(Level as int)) from Leveltbl where Score < A.Score) Level
from [user] A.
方法1. 直接判断
select *,(select max(level) from level where score<=a.score) as level
from user a
from @user a,(
select a.level,a.score,b.score-1 as score1
from @level a left join @level b on a.level+1=b.level
) b where a.score between b.score and isnull(b.score1,a.score)
declare @level table(Level int, Score int)
insert into @level
select 0,0
union all select 1,50
union all select 2,100
union all select 3,150declare @user table(userID int,Score int)
insert into @user
select 1010,34
union all select 1011,97
union all select 1012,150--用第一种方法得到结果
select *,(select max(level) from @level where score<=a.score) as level
from @user a--用第二种方法得到结果
select a.*,b.level
from @user a,(
select a.level,a.score,b.score-1 as score1
from @level a left join @level b on a.level+1=b.level
) b where a.score between b.score and isnull(b.score1,a.score)