温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

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

发布时间:2020-07-07 22:26:28 来源:网络 阅读:2933 作者:天黑顺路 栏目:关系型数据库

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 ;
向AI问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI