oracle的case when写法学习
来源:互联网 发布:hadoop2.7.1 windows 编辑:程序博客网 时间:2024/05/29 18:41
case when 语句可以在SQL语句中实现IF..ELSE
语法上有两种操作:
语法一:
CASE EXPRESSION WHEN EXPRESSION1 THEN VAL1
WHEN EXPRESSION2 THEN VAL2
WHEN EXPRESSION3 THEN VAL3
..
WHEN EXPRESSIONN THEN VALN
END ;
语法二:
CASE WHEN EXPRESSION1 THEN VAL1
WHEN EXPRESSION2 THEN VAL2
WHEN EXPRESSION3 THEN VAL3
..
WHEN EXPRESSIONN THEN VALN
END ;
例:常见的一种面试题:在SCOTT模式下的EMP表中,将DEPT=10的员工工资变为原来的0.8倍,
DEPT=20的员工工资变为原来的1.2倍,DEPT=300的员工工资变为原来的1.5倍
语法一:
SQL> SELECT ENAME, JOB, HIREDATE, deptno,sal,
2 CASE DEPTNO WHEN 10 THEN SAL * 0.8
3 WHEN 20 THEN sal*1.2
4 WHEN 30 THEN sal*1.5
5 END
6 FROM emp ;
ENAME JOB HIREDATE DEPTNO SAL CASEDEPTNOWHEN10THENSAL*0.8WHE
---------- --------- ----------- ------ --------- ------------------------------
SMITH CLERK 1980-12-17 20 800.00 960
ALLEN SALESMAN 1981-02-20 30 1600.00 2400
WARD SALESMAN 1981-02-22 30 1250.00 1875
JONES MANAGER 1981-04-02 20 2975.00 3570
MARTIN SALESMAN 1981-09-28 30 1250.00 1875
BLAKE MANAGER 1981-05-01 30 2850.00 4275
CLARK MANAGER 1981-06-09 10 2450.00 1960
SCOTT ANALYST 1987-04-19 20 3000.00 3600
KING PRESIDENT 1981-11-17 10 5000.00 4000
TURNER SALESMAN 1981-09-08 30 1500.00 2250
ADAMS CLERK 1987-05-23 20 1100.00 1320
JAMES CLERK 1981-12-03 30 950.00 1425
FORD ANALYST 1981-12-03 20 3000.00 3600
MILLER CLERK 1982-01-23 10 1300.00 1040
语法二:
SQL> SELECT ENAME, JOB, HIREDATE, deptno,sal,
2 CASE WHEN DEPTNO = 10 THEN SAL * 0.8
3 WHEN deptno =20 THEN sal*1.2
4 WHEN deptno =30 THEN sal*1.5
5 END
6 FROM emp ;
ENAME JOB HIREDATE DEPTNO SAL CASEWHENDEPTNO=10THENSAL*0.8WH
---------- --------- ----------- ------ --------- ------------------------------
SMITH CLERK 1980-12-17 20 800.00 960
ALLEN SALESMAN 1981-02-20 30 1600.00 2400
WARD SALESMAN 1981-02-22 30 1250.00 1875
JONES MANAGER 1981-04-02 20 2975.00 3570
MARTIN SALESMAN 1981-09-28 30 1250.00 1875
BLAKE MANAGER 1981-05-01 30 2850.00 4275
CLARK MANAGER 1981-06-09 10 2450.00 1960
SCOTT ANALYST 1987-04-19 20 3000.00 3600
KING PRESIDENT 1981-11-17 10 5000.00 4000
TURNER SALESMAN 1981-09-08 30 1500.00 2250
ADAMS CLERK 1987-05-23 20 1100.00 1320
JAMES CLERK 1981-12-03 30 950.00 1425
FORD ANALYST 1981-12-03 20 3000.00 3600
MILLER CLERK 1982-01-23 10 1300.00 1040
语法上有两种操作:
语法一:
CASE EXPRESSION WHEN EXPRESSION1 THEN VAL1
WHEN EXPRESSION2 THEN VAL2
WHEN EXPRESSION3 THEN VAL3
..
WHEN EXPRESSIONN THEN VALN
END ;
语法二:
CASE WHEN EXPRESSION1 THEN VAL1
WHEN EXPRESSION2 THEN VAL2
WHEN EXPRESSION3 THEN VAL3
..
WHEN EXPRESSIONN THEN VALN
END ;
例:常见的一种面试题:在SCOTT模式下的EMP表中,将DEPT=10的员工工资变为原来的0.8倍,
DEPT=20的员工工资变为原来的1.2倍,DEPT=300的员工工资变为原来的1.5倍
语法一:
SQL> SELECT ENAME, JOB, HIREDATE, deptno,sal,
2 CASE DEPTNO WHEN 10 THEN SAL * 0.8
3 WHEN 20 THEN sal*1.2
4 WHEN 30 THEN sal*1.5
5 END
6 FROM emp ;
ENAME JOB HIREDATE DEPTNO SAL CASEDEPTNOWHEN10THENSAL*0.8WHE
---------- --------- ----------- ------ --------- ------------------------------
SMITH CLERK 1980-12-17 20 800.00 960
ALLEN SALESMAN 1981-02-20 30 1600.00 2400
WARD SALESMAN 1981-02-22 30 1250.00 1875
JONES MANAGER 1981-04-02 20 2975.00 3570
MARTIN SALESMAN 1981-09-28 30 1250.00 1875
BLAKE MANAGER 1981-05-01 30 2850.00 4275
CLARK MANAGER 1981-06-09 10 2450.00 1960
SCOTT ANALYST 1987-04-19 20 3000.00 3600
KING PRESIDENT 1981-11-17 10 5000.00 4000
TURNER SALESMAN 1981-09-08 30 1500.00 2250
ADAMS CLERK 1987-05-23 20 1100.00 1320
JAMES CLERK 1981-12-03 30 950.00 1425
FORD ANALYST 1981-12-03 20 3000.00 3600
MILLER CLERK 1982-01-23 10 1300.00 1040
语法二:
SQL> SELECT ENAME, JOB, HIREDATE, deptno,sal,
2 CASE WHEN DEPTNO = 10 THEN SAL * 0.8
3 WHEN deptno =20 THEN sal*1.2
4 WHEN deptno =30 THEN sal*1.5
5 END
6 FROM emp ;
ENAME JOB HIREDATE DEPTNO SAL CASEWHENDEPTNO=10THENSAL*0.8WH
---------- --------- ----------- ------ --------- ------------------------------
SMITH CLERK 1980-12-17 20 800.00 960
ALLEN SALESMAN 1981-02-20 30 1600.00 2400
WARD SALESMAN 1981-02-22 30 1250.00 1875
JONES MANAGER 1981-04-02 20 2975.00 3570
MARTIN SALESMAN 1981-09-28 30 1250.00 1875
BLAKE MANAGER 1981-05-01 30 2850.00 4275
CLARK MANAGER 1981-06-09 10 2450.00 1960
SCOTT ANALYST 1987-04-19 20 3000.00 3600
KING PRESIDENT 1981-11-17 10 5000.00 4000
TURNER SALESMAN 1981-09-08 30 1500.00 2250
ADAMS CLERK 1987-05-23 20 1100.00 1320
JAMES CLERK 1981-12-03 30 950.00 1425
FORD ANALYST 1981-12-03 20 3000.00 3600
MILLER CLERK 1982-01-23 10 1300.00 1040
- oracle的case when写法学习
- Oracle学习之case when的用法
- case when then的两种写法
- case when then 的两种写法
- ORACLE CASE WHEN 的用法
- oracle case when的用法
- oracle case when的用法
- oracle case when的用法
- oracle case when的用法
- oracle case when的使用方法
- oracle case when的用法
- oracle case when的使用方法
- oracle case when的用法
- oracle case when的使用方法
- oracle case when的用法
- oracle case when的使用方法
- Oracle case when 的使用方法
- oracle case when的使用方法
- socket 编程入门教程(一)TCP server 端:5、创建监听嵌套字
- linux定时任务的设置
- c/c++程序之_KMP字符串模式匹配详解
- UrlRewriteFilter 学习笔记
- VC回调函数
- oracle的case when写法学习
- SQL提取数据库表名、字段名等信息
- Linux内存管理
- Visual重命名数据库失败
- php学习
- Android Studio 上local path doesn't exist问题
- xcode4.4+ APP 打包以及提交apple审核详细流程(新版本更新提交审核)
- 创建ORACLE JOB
- AlarmManager的使用