--为表SC创建游标,将成绩按下列对应关糸由分数转换成等级:成绩score为int型
--小于60分,不及格;60~70,及格;70~80,中;80~90,良;90以上,优
declare on_sc cursor for
select score from sc
open on_scselect score
from sc
declare @score int
fetch on_sc into @scorewhile @@fetch_status = 0
begin
if @score < 60
update sc
set @score = convert(char(10),@score),@score = '不及格'
where current of on_sc
else if @score <70 and @score >= 60
update sc
set @score = convert(char(10),@score),@score = '及格'
where current of on_sc
else if @score < 80 and @score >=70
update sc
set @score = convert(char(10),@score),@score = '中'
where current of on_sc
else if @score <90 and @score >= 80
update sc
set @score = convert(char(10),@score),@score = '良'
where current of on_sc
else if @score <=100 and @score >=90
update sc
set @score =convert( char(10),@score),@score = '优'
where current of on_sc
fetch on_sc into @score
end
close on_sc
deallocate on_sc
得不到正确的结果,不知道如何把字符串赋给int型
--小于60分,不及格;60~70,及格;70~80,中;80~90,良;90以上,优
declare on_sc cursor for
select score from sc
open on_scselect score
from sc
declare @score int
fetch on_sc into @scorewhile @@fetch_status = 0
begin
if @score < 60
update sc
set @score = convert(char(10),@score),@score = '不及格'
where current of on_sc
else if @score <70 and @score >= 60
update sc
set @score = convert(char(10),@score),@score = '及格'
where current of on_sc
else if @score < 80 and @score >=70
update sc
set @score = convert(char(10),@score),@score = '中'
where current of on_sc
else if @score <90 and @score >= 80
update sc
set @score = convert(char(10),@score),@score = '良'
where current of on_sc
else if @score <=100 and @score >=90
update sc
set @score =convert( char(10),@score),@score = '优'
where current of on_sc
fetch on_sc into @score
end
close on_sc
deallocate on_sc
得不到正确的结果,不知道如何把字符串赋给int型
大致方法是:select case when score < 60 then '不及格'
when score >= 60 and score < 70 then '及格'
when score >= 70 and score < 80 then '中'
when score >= 80 and score < 90 then '良'
when score >= 90 then '优'
end
from tb
update sc
set 字段 = case when score < 60 then '不及格'
when score >= 60 and score < 70 then '及格'
when score >= 70 and score < 80 then '中'
when score >= 80 and score < 90 then '良'
when score >= 90 then '优'
end
from tb
(
case when score < 60 then '不及格'
when score <70 and score>=60 then '及格'
when score <80 and score>=70 then '中'
when score <90 and score>=80 then '良'
else '优'
end
)
from sc
update sc
set 成绩 = (case
when score < 60 then '不及格'
when score >= 60 and score < 70 then '及格'
when score >= 70 and score < 80 then '中'
when score >= 80 and score < 90 then '良'
when score >= 90 then '优'
end)
另外,字符串怎么能赋给int型呢,肯定不可以了啊
在表sc加一字段处理吧,如加CJ varchar(32),那么可以如下处理:update sc
set cj = ( case when score < 60 then '不及格'
when score >= 60 and score < 70 then '及格'
when score >= 70 and score < 80 then '中'
when score >= 80 and score < 90 then '良'
when score >= 90 then '优' end )
真周到啊~
真周到啊~
while @@fetch_status = 0
begin
if @score < 60
update sc
set @score = convert(char(10),@score),@score = '不及格'
where current of on_sc 你这个UPDATE语句明星有问题啊, 你更新定义的参数干嘛?
应该是更新某个FIELD吧, 另外就算你要更新定义的参数, 可是参数是定义是INT的, 怎么能更新成字符的呢?
只能再加个字段放这个rank了
add class char(10)
declare on_sc cursor for
select score from sc
open on_sc select score
from sc
declare @score intfetch on_sc into @score while(@@FETCH_STATUS=0)
begin
update sc
set class = case when @score < 60
then '不及格'
when @score >= 60 and @score < 70
then '及格'
when @score >= 70 and @score < 80
then '中'
when @score >= 80 and @score < 90
then '良'
when @score >= 90
then '优'
end
from sc
where current of on_sc
fetch on_sc into @score
endclose on_sc
deallocate on_sc
select *
from sc