将分数进行转换
小于60——D,60-69——C,70-79——B,80以上——A,
自己写了一个
create proc ShiJYJ_R_change
as begin
alter table Student_skill1 alter column grade varchar(50)
update Student_skill1 set grade='D' where grade<60update Student_skill1 set grade='C' where grade between 60 and 69update Student_skill1 set grade='B' where grade between 70 and 79update Student_skill1 set grade='A' where grade between 80 and 100
end
执行的时候,提示:"服务器: 消息 245,级别 16,状态 1,过程 ShiJYJ_R_change,行 6
将 varchar 值 'D' 转换为数据类型为 int 的列时发生语法错误。"
表里的字段都是Vachar类型的,执行时只能响应一个分数段的,为什么?
小于60——D,60-69——C,70-79——B,80以上——A,
自己写了一个
create proc ShiJYJ_R_change
as begin
alter table Student_skill1 alter column grade varchar(50)
update Student_skill1 set grade='D' where grade<60update Student_skill1 set grade='C' where grade between 60 and 69update Student_skill1 set grade='B' where grade between 70 and 79update Student_skill1 set grade='A' where grade between 80 and 100
end
执行的时候,提示:"服务器: 消息 245,级别 16,状态 1,过程 ShiJYJ_R_change,行 6
将 varchar 值 'D' 转换为数据类型为 int 的列时发生语法错误。"
表里的字段都是Vachar类型的,执行时只能响应一个分数段的,为什么?
--当进行完第一步后,以后grade存储D这种不能转换为数值型的数据存在
--so……
--我是这么理解的。
CASE WHEN grade<60 THEN 'D'
WHEN grade between 60 and 69 THEN 'C'
WHEN grade between 70 and 79 THEN 'B'
WHEN grade between 80 and 100 THEN 'A'
END
FROM Studen_skill1
--grade是varchar类型,grade between '60' and '69'
SELECT grade,
CASE WHEN grade<60 THEN 'D'
WHEN grade between 60 and 69 THEN 'C'
WHEN grade between 70 and 79 THEN 'B'
WHEN grade between 80 and 100 THEN 'A'
else 0 END
FROM Studen_skill1
up...
go
up...
go
up...
end
1. 执行update Student_skill1 set grade='D' where grade < 60,成功,表中数据变成:
grade
----------
D
61
71
81
(4 行受影响)
2. 执行第2条update语句,update Student_skill1 set grade='C' where grade between 60 and 69,因为第一行记录的grade值已经变成'D',它无法强制转换成整数(between 60 and 69),所以报错
3. 存储过程停止
create table Student_skill1(id int,grade int)
insert into Student_skill1 select 1,76 union select 2,88 union
select 3,62 union select 4,95 union select 5,32 union select 6,66
go
alter table Student_skill1 alter column grade varchar(50)
go
update Student_skill1 set grade = case grade/10 when 10 then 'A' when 9 then 'A' when 8 then 'A' when 7 then 'B' when 6 then 'C' else 'D' end
select * from Student_skill1
go
drop table Student_skill1
/*
id grade
----------- --------------------------------------------------
1 B
2 A
3 C
4 A
5 D
6 C(6 行受影响)*/
--小于60——D,60-69——C,70-79——B,80以上——A,
update Studen_skill1
Set [grade]=
CASE WHEN len(ltrim(grade))=2 and (ascii(grade) between 80 and 85) THEN 'D' --00 --59
WHEN ascii(grade)=86 THEN 'C' --60--69
WHEN ascii(grade)=87 THEN 'B' --70--79
WHEN ascii(grade)=88 or ascii(grade)=89 or (ascii(grade)=80 and len(ltrim(grade))=3) THEN 'A'--80以上,因为100是特例
END
FROM Studen_skill1