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 (
还可以用在insert语句中,如下: insertintot1Withc3As(Select*Fromv_tr_dailyWherep_date=to_date('2019-05-21','yyyy-mm-dd')) ,c1As(Selecttype_id,dept_id,drill_dept,sum(mine_ore0)Fromc3Wheretype_id=1GroupBytype_id,dept_id,drill_dept )Select*Fromc1 15161718192021...
other_infoAS(SELECTempno,hiredate,NVL(sal,0)+NVL(comm,0)AStotal_salFROMemp )SELECT*FROMdept_info dLEFTJOINemp_info eONd.deptno_1=e.deptnoRIGHTJOINother_info oONe.empno_1=o.empno ; INSERTINTOw_test_19WITHdata_info_19AS(SELECTNVL(ename,'unknow employee')ASename, DECODE(deptno,10,'ACCOU...
流程也简单 insert into 表1 with t1 as (select * from 表2), t2 as (select * from t1 inner join 表2 on t1.qw = 表2.qw) select * from t2; 这么写的话就是插入表1的数据用with as全部临时查出来。
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短语,也...
其实就是把一大堆重复用到的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 ...
(ORACLEWITHAS用法大全 ORACLE WITH AS 用法 有两张表,分别为A、B,求得一个字段的值先在表A中寻找,如果A表中存在数据,则输出A表的值;如果A表中不存在,则在B表中寻找,若B表中有相应记录,则输出B表的值;如果B表中也不存在,则输出no records”字符串。 view plaincopy to clipboardprint? with?? sql1?
9.SQL> insert into t2 values(1); 10. 11.1row created. 12. 13.SQL> insert into t2 values(2); 14. 15.1row created. 16. 17.SQL> insert into t3 values(3); 18. 19.1row created. 20. 21.SQL> commit; 22. 23.Commit complete. ...
--建表create table xgj (name varchar2(20));--新增数据insert into xgj values('xiaogongjiang');--提交数据commit ;--使用for update方式获取排他行级锁select * from xgj where name='xiaogongjiang' for update ; 会话B: 在这里是在plsql中另外新开了一个窗口模拟会话B,不能在同一个会话窗口,否则测...
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 sysdate)and o....