有表a如下
姓名 分数
a 70
c 80
d 40
我想得到如下结果:
姓名 分数 code
a 70 10
c 80 20
d 40 0
条件为
如果分数> 60 就显示 (分数-60)的差额,否则就显示0;
请指教谢谢!
------解决方案--------------------
SQL> select tt.name,tt.score,decode(sign(tt.score-60),1,tt.score-60,0)
2 from (select 'a ' as name,70 as score from dual
3 union all
4 select 'c ' as name,80 as score from dual
5 union all
6 select 'd ' as name,40 as score from dual
7 )tt;
NAME SCORE DECODE(SIGN(TT.SCORE-60),1,TT.
---- ---------- ------------------------------
a 70 10
c 80 20
d 40 0