oracle with as 语句
来源:互联网 发布:德国做律师 知乎 编辑:程序博客网 时间:2024/06/05 15:39
1、
Oracle with语句是经常可以见到的语句,下面就为您详细介绍Oracle with语句的用法,如果您对Oracle with语句感兴趣的话,不妨一看。
当查询中多次用到某一部分时,可以用Oracle with语句创建一个公共临时表。因为子查询在内存临时表中,避免了重复解析,所以执行效率会提高不少。临时表在一次查询结束自动清除。
一般语法格式:
- with
- alias_name1 as (subquery1),
- alias_name2 as (subQuery2),
- ……
- alias_nameN as (subQueryN)
- select col1,col2…… col3
- from alias_name1,alias_name2……,alias_nameN
Oracle with语句的例子:
- SQL> WITH
- Q1 AS (SELECT 3 + 5 S FROM DUAL),
- Q2 AS (SELECT 3 * 5 M FROM DUAL),
- Q3 AS (SELECT S, M, S + M, S * M FROM Q1, Q2)
- SELECT * FROM Q3;
输出结果:
2、with 语句和时间
- S M S+M S*M
- ---------- ---------- ---------- ----------
- 8 15 23 120
有时候我们需要查询出过去几天的数据,然后展示出来,那么使用with语句如何实现呢?
with t1 as (select to_date(‘sysdate’,'yyyy-mm-dd')-level+1 d from dual connect by level<? order by d
select aa
from t1 left join tb_member t2 on to_date(to_char(t1.d,'YYYY-mm-dd'),'yyyy-mm-dd')<t2.regist_time
group by to_char(t1.d,'YYYY-mm-dd') order by to_date(to_char(t1.d,'YYYY-mm-dd'),'yyyy-mm-dd'
其中的select后面是要查询的属性,?为要查询的天数
0 0
- oracle with as 语句
- oracle和sql的with as 语句
- oracle with as语句的DDL尴尬
- oracle中with as语句的使用
- oracle with alias as (subsquery fatoring) 语句
- with as SQL语句
- 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 语法
- ORACLE WITH AS 用法
- Oracle WITH AS 用法
- ORACLE WITH AS 用法
- ubuntu 登陆root权限
- QVariant类 万能数据类型
- ORACLE 11G 导出报错(EXP-00003)未找到段 (0,0) 的存储定义
- Spring scope属性详解
- bin文件夹关于dll引用的注意事项
- oracle with as 语句
- charles抓取https请求包
- git问题记录
- c++中冒号(:)和双冒号(::)的区别与用法
- PHP典型扩展模块安装
- PhoneGap揭开你的神秘面纱
- untiy 3d ShaderLab_第6章_VertexLit渲染路径_4_顶点照明和Unity存放光源的第三种方式
- CSS3圆角详解
- Andriod 中两个Activity之间传值的几种思路