oracle with别名as (select * from *)
with ?别名 as (select * from *)?select * from 别名；?相当于建个临时表语法就是with tempname as (select ....)select ...?例子：with t as (select * from emp where depno=10)select * from t where empno=xxx?再举个简单的例子?with a as (select * from test)?select * from a;其实就是把一大堆重复用到的SQL语句放在with as 里面，取一个别名，后面的查询就可以用它?这样对于大批量的SQL语句起到一个优化的作用，而且清楚明了???是个临时存储,一般是在存储过程里使用的?可以做多个表的连接，结果集的连接查询?比如查询某个emp、dept、还有work表 work表是用来存储工作信息的，eid,did,salary等?要求查询工资比起所在部门工资高的员工的信息，此时需要很多的连接，就可以使用with as 比较清晰的连接几个表?Assembly code?with wd as (select did,arg(salary) 平均工资 from work), em as (select emp.*,w.salary from emp left join work w on emp.eid = w.eid) select * from wd,em where wd.did =em.did and wd.平均工资>em.salary;??create table temp_zcy (?? ?content varchar2(200)? ?);?alter table temp_zcy add sid number; ??select * from temp_zcy order by ?sid;????declare? v_str varchar2(200);? v_sql varchar2(200);?begin??for v_counter in 5..1000 loop? ? if mod(v_counter,5) = 0 then ?? ? ? ?--dbms_output.put_line(v_counter+5);? ? ? ?--v_str := 'sum(case when a.allamt>='||v_counter;? ? ? ?-- v_str :='sum(case when a.allamt>='||v_counter||' and a.allamt <'||(v_counter+5);? ? ? ? v_str := 'sum(case when a.allamt>= '||v_counter||' and a.allamt < '||(v_counter+5)||' then a.allamt else 0 end) as aaa'||v_counter||'至'||(v_counter+5)||'万bbb,';? ? ? ?--dbms_output.put_line(v_str);?? ? ? ?v_sql := 'insert into temp_zcy(sid,content) values('||v_counter||','''||v_str||''')';? ? ? ?execute immediate v_sql; ? ? ??? ? ? ?commit;? ? end if;??end loop;?end;