当前位置:   article > 正文

oracle数据库的as语句怎么写,oracle中create table with as和insert into with as语句

insert into with as select

oracle中create table with as和insert into with as语句

发布时间:2020-07-07 22:26:28

来源:51CTO

阅读:751

作者:天黑顺路

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 )

[, clause_name1 as

(select query ) ...]

select column1,...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,NVL(sal,0)+NVL(comm,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,'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;

INSERT INTO w_test_18

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,NVL(sal,0)+NVL(comm,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 ;

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/小桥流水78/article/detail/853204
推荐阅读
相关标签
  

闽ICP备14008679号