oracle中create table with as和insert into with as语句
发布时间:2020-12-12 14:12:43 所属栏目:百科 来源:网络整理
导读:oracle支持使用with as 子句来创建表 语法: create table table_name as with clause_name as (select query ) [,clause_name1 as (select query ) ...] select column1,...columnn from clause_name; 语法: insert into table_name with clause_name a
oracle支持使用with as 子句来创建表 语法: create table table_name as with clause_name as (select query ) [,clause_name1 as (select query ) ...] select column1,...columnn from clause_name; 语法: insert into table_name with clause_name as (select query ) [,...columnn from clause_name; 例子: --create with 语句 CREATE TABLE w_test_20 AS WITH data_info_19 AS (SELECT NVL(ename,'unknow employee') AS ename,DECODE(deptno,10,'ACCOUNTING',20,'RESEARCH',30,'SALES',40,'OPERATIONS','no this department') AS dname,NVL(job,'unkown job') AS job,substr(to_char(hiredate,'yyyy-mm-dd'),1,7) AS hiredate,CASE WHEN sal <= 1000 THEN 'D' WHEN sal > 1000 AND sal <= 2000 THEN 'C' WHEN sal > 2000 AND sal <= 3000 THEN 'B' WHEN sal > 3000 THEN 'A' ELSE 'unkown grade' END AS grade,ROUND(sal,2) AS sal,FLOOR(nvl(comm,0)) AS comm,NVL(sal,0)+NVL(comm,0) AS total_sal FROM emp WHERE deptno IN (SELECT DISTINCT deptno FROM dept)) SELECT * FROM data_info_19; CREATE TABLE w_test_18 AS WITH dept_info AS (SELECT deptno AS deptno_1,dname FROM dept),emp_info AS (SELECT empno AS empno_1,ename,job,deptno FROM emp),other_info AS (SELECT empno,hiredate,0) AS total_sal FROM emp ) SELECT * FROM dept_info d LEFT JOIN emp_info e ON d.deptno_1=e.deptno RIGHT JOIN other_info o ON e.empno_1=o.empno ; INSERT INTO w_test_19 WITH data_info_19 AS (SELECT NVL(ename,0) AS total_sal FROM emp WHERE deptno IN (SELECT DISTINCT deptno FROM dept)) SELECT * FROM data_info_19; INSERT INTO w_test_18 WITH dept_info AS (SELECT deptno AS deptno_1,0) AS total_sal FROM emp ) SELECT * FROM dept_info d LEFT JOIN emp_info e ON d.deptno_1=e.deptno RIGHT JOIN other_info o ON e.empno_1=o.empno ; (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |
相关内容
- 使用hash或case-statement [Ruby]
- 使用ajax上传图片(ajaxfileupload.js)
- 如何从C#中的预编译器获取当前的DateTime?
- ruby-on-rails – Ruby on Rails 3.每个都有问题
- 【Oracle】RMAN duplicate复制库
- end.method在Ruby中做了什么?
- Cocos2d-x 游戏发布攻防篇01:防止打包党的小技巧
- postgresql – ALTER ROLE与CREATEDB和GRANT CREATE ON TAB
- Nosql主要概念、产品分类、及各产品要点
- ruby-on-rails – 为什么twitter-bootstrap-rails生成的视图