日期:2014-05-17  浏览次数:20967 次

如果是合格律100%得分就是60分,否则每低0.1个百分点扣一分,存储过程怎么写
如果是合格律100%得分就是60分,否则每低0.1个百分点扣一分,存储过程怎么写

------解决方案--------------------
SQL code

create table t1 (col1 varchar(10));

insert into t1 values ('96%');
insert into t1 values ('90%');
insert into t1 values ('100%');
insert into t1 values ('88%');
insert into t1 values ('34%');
insert into t1 values ('77%');

select col1,case when substr(col1,1,length(col1)-1)-40 >=0 then substr(col1,1,length(col1)-1)-40 else 0 end score
from t1

      col1     score
-------------------------
1    96%    56
2    90%    50
3    100%    60
4    88%    48
5    34%    0
6    77%    37