oracle with 别名 as (select * from *)
来源:互联网 发布:网络礼仪有哪些 编辑:程序博客网 时间:2024/06/14 11:07
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
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;
- oracle with 别名 as (select * from *)
- oracle with as 表别名
- oracle with as 表别名
- Oracle--select使用别名
- Select From (Select from) as Alias
- SQL SERVER中的WITH T AS () SELECT * FROM T 的用法
- sql server 每日一记之 with as delete from where in (select)
- With as 必须跟select
- oracle数据库【表复制】insert into select from跟create table as select * from 两种表复制语句区别
- ORACLE WITH AS 用法
- ORACLE WITH AS 用法
- oracle with as
- oracle with as
- oracle :with as
- ORACLE WITH AS 语法
- oracle with as 语法
- oracle with as 语法
- ORACLE WITH AS 用法
- ASP.Net生成静态HTML页!
- 深入理解HashMap
- vb.net和c#.net保存EXCEL
- Quartz 多个触发器
- js改变form的action
- oracle with 别名 as (select * from *)
- 页面回到顶部的三种实现
- 具有强大的DesignSurface的大DesignTime经验(扩展)类
- 网吧组网产品升级方案
- 下载编译过的boost库
- Spring事务配置的五种方式
- 使用CreateProcess要注意的
- hibernate中一对多(one-to-many)的实例
- 怎样阅读文献-1