34,838
社区成员




create table tb_Student
(ID int identity(1,1),
姓名 varchar(20),
课程 varchar(20),
成绩 varchar(20)
)
insert into tb_Student(姓名,课程,成绩) values('001','yingyu','60')
insert into tb_Student(姓名,课程,成绩) values('002','shuxue','50')
insert into tb_Student(姓名,课程,成绩) values('003','yingyu','80')
insert into tb_Student(姓名,课程,成绩) values('004','huaxue','70')
select ID,姓名,课程,成绩,绩点=case when 成绩<60 then 0 else 1+(成绩-60)/10 end from tb_Student
----
17 001 yingyu 60 1
18 002 shuxue 50 0
19 003 yingyu 80 3
20 004 huaxue 70 2
--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (课程编号 int,成绩 int)
insert into #T
select 100,70 union all
select 101,60 union all
select 102,59
select 课程编号,成绩,课程绩点=case when 成绩<60 then 0 else 1+(成绩-60)/10 end from #T
/*
课程编号 成绩 课程绩点
----------- ----------- -----------
100 70 2
101 60 1
102 59 0
*/
select 课程编号,
成绩,
课程绩点 = case when 成绩 <60 then 0 else 1+(成绩-60)/10 end
from ta
--> 不行?
select 课程编号,成绩,课程绩点=case when 成绩 <60 then 0 else 1+(成绩-60)/10 end from cs