ORACLE的CASE WHEN表达式

最新推荐文章于 2024-04-18 16:05:18 发布

미 칠 것 같다

最新推荐文章于 2024-04-18 16:05:18 发布

阅读量2.6k

点赞数

分类专栏： Oracle 文章标签： ORACLE

本文链接：https://blog.csdn.net/qq_42331027/article/details/89081001

版权

Oracle 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

CASE WHEN语句的一般用法，通过条件判断返回对应的值。
例：查询若部门号为 10, 则打印其工资的 1.1 倍, 20 号部门, 则打印其工资的 1.2 倍, 30 号部门打印其工资的 1.3 倍数

 select last_name, department_id, salary, 
    case department_id when 10 then 1.10*salary 
    when 20 then 1.15*salary 
    when 30 then 1.20*salary 
    else salary 
    end "revised_salary " 
    from employees

CASE WHEN语句还可以用于统计
例：统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]

select b.cno,cname,
sum(case  when score between 85 and 100 then 1 else 0 end) "[100-85]",
sum(case  when score between 70 and 85 then 1 else 0 end) "[85-70]",
sum(case  when score between 60 and 70 then 1 else 0 end) "[70-60]",
sum(case  when score <60 then 1 else 0 end) "[<60]"
from course a,sc b where a.cno = b.cno group by b.cno,cname