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,'...
,c1As(Selecttype_id,dept_id,drill_dept,sum(mine_ore0)Fromc3Wheretype_id=1GroupBytype_id,dept_id,drill_dept )Select*Fromc1 还可以用在insert语句中,如下: insertintot1Withc3As(Select*Fromv_tr_dailyWherep_date=to_date('2019-05-21','yyyy-mm-dd')) ,c1As(Selecttype_id,dept_id,drill_d...
insertintoDEPT(dept_no, dept_name)values('D001','总经理部'); insertintoDEPT(dept_no, dept_name)values('D002','人力资源部'); insertintoDEPT(dept_no, dept_name)values('D003','行政后勤部'); insertintoDEPT(dept_no, dept_name)values('D004','销售一部'); insertintoDEPT(dept_no, dep...
INSERTINTOw_test_19WITHdata_info_19AS(SELECTNVL(ename,'unknow employee')ASename, DECODE(deptno,10,'ACCOUNTING',20,'RESEARCH',30,'SALES',40,'OPERATIONS','no this department')ASdname, NVL(job,'unkown job')ASjob , substr(to_char(hiredate,'yyyy-mm-dd'),1,7)AShiredate,CASEWHENsal<=10...
其实就是把一大堆重复用到的sql语句放在with as里面,取一个别名,后面的查询就可以用它,这样对于大批量的sql语句起到一个优化的作用,而且结构清晰。 向一张表插入数据的 with as 用法: 复制 复制 insertintotable1withs1as(selectrownum c1fromdual connectbyrownum<=10),s2as(selectrownum c2fromdual ...
insertintotable2 with s1 as(selectrownumc1 fromdual connectbyrownum<= 10), s2 as(selectrownumc2 fromdual connectbyrownum<= 10) selecta.c1, b.c2 froms1 a, s2 b where...; select s1.sid, s2.sid from s1 ,s2需要有关联条件,不然结果会是笛卡尔积。 with as相当于虚拟视图。 with as短语,也...
⽤with的⽤法：insert into HW_RATE (ID,SUB_TYPE,DISTRICT_CODE,REGION_CODE,SUB_AREA_CODE,CREATE_DATE,CREATE_USER)with areaInfo as (select o.district_code bu, o.region_code re from eis_user.org_office o where o.sub_area_code ='60487'and o.status =1 and o.year= extract(year from...
其实就是把一大堆重复用到的sql语句放在with as里面,取一个别名,后面的查询就可以用它,这样对于大批量的sql语句起到一个优化的作用,而且清楚明了。 向一张表插入数据的with as用法 1 2 3 4 5 insert into table2 with s1 as (select rownum c1 from dual connect by rownum <= 10), s2 as (select ...
其实就是把一大堆重复用到的sql语句放在with as里面,取一个别名,后面的查询就可以用它,这样对于大批量的sql语句起到一个优化的作用,而且清楚明了。 向一张表插入数据的with as用法 1 2 3 4 5 insertintotable2 with s1as(selectrownum c1fromdualconnectbyrownum <= 10), ...
with a as (select * from test) select * from a; 其实就是把一大堆重复用到的SQL语句放在with as 里面,取一个别名,后面的查询就可以用它 这样对于大批量的SQL语句起到一个优化的作用,而且清楚明了 下面是搜索到的英文文档资料 About Oracle WITH clause ...