--1。如果在数据表里面不改变原来的成绩,就是想输出的时候显示出来!
select *,等级=case
when Mark>=90 then '优'
when Mark between 80 and 89 then '良'
when Mark between 70 and 79 then '中'
when Mark between 60 and 69 then '及格'
else '差' end
from StuSelCourse--2。如果直接在数据库里面转换
update StuSelCourse set
等级=case
when Mark>=90 then '优'
when Mark between 80 and 89 then '良'
when Mark between 70 and 79 then '中'
when Mark between 60 and 69 then '及格'
else '差' end
select *,等级=case
when Mark>=90 then '优'
when Mark between 80 and 89 then '良'
when Mark between 70 and 79 then '中'
when Mark between 60 and 69 then '及格'
else '差' end
from StuSelCourse--2。如果直接在数据库里面转换
update StuSelCourse set
等级=case
when Mark>=90 then '优'
when Mark between 80 and 89 then '良'
when Mark between 70 and 79 then '中'
when Mark between 60 and 69 then '及格'
else '差' end
create function f_getid()
returns int
as
begin
return(select isnull(max(id),0)+1 from tb)
end
go--创建表
create table tb(id int default dbo.f_getid() primary key,name varchar(10),addr varchar(12))
go
insert tb (name) values(10)
insert tb (name) values(10)
select * from tb
godrop table tb
drop function f_getid/*--结果
id name addr
----------- ---------- ------------
1 10 NULL
2 10 NULL(所影响的行数为 2 行)
--*/
update tb set dj="优" where between 80 and 89;
update tb set dj="良" where between 70 and 79;
...
select *,等级=case
when Mark>=90 then '优'
when Mark between 80 and 89 then '良'
when Mark between 70 and 79 then '中'
when Mark between 60 and 69 then '及格'
when Mark>100 or Mark<0 then '錯誤'
else '差' end
from StuSelCourse--2。如果直接在数据库里面转换
update StuSelCourse set
等级=case
when Mark>=90 then '优'
when Mark between 80 and 89 then '良'
when Mark between 70 and 79 then '中'
when Mark between 60 and 69 then '及格'
when Mark>100 or Mark<0 then '錯誤'
else '差' end