IT忍者神龟之关于oracle with as用法终结，忍者神龟oracle
with as语法
–针对一个别名
with tmp as (select * from tb_name)
–针对多个别名
with
tmp as (select * from tb_name),
tmp2 as (select * from tb_name2),
tmp3 as (select * from tb_name3),
…
| 1 2 3 4 5 6 7 8 9 |
--相当于建了个e临时表
with e
as (select *
from scott.emp
e where e.empno=7499)
select *
from e;
--相当于建了e、d临时表
with
e
as (select *
from scott.emp),
d
as (select *
from scott.dept)
select *
from e,
d where e.deptno
= d.deptno;
|
其实就是把一大堆重复用到的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 rownum
c2 from dual
connect by rownum
<= 10)
select a.c1,
b.c2 from s1
a, s2 b where...;
|
select s1.sid, s2.sid from s1 ,s2需要有关联条件，不然结果会是笛卡尔积。
with as 相当于虚拟视图。
with as短语，也叫做子查询部分(subquery factoring)，可以让你做很多事情，定义一个sql片断，该sql片断会被整个sql语句所用到。有的时候，是为了让sql语句的可读性更高些，也有可能是在union all的不同部分，作为提供数据的部分。
特别对于union all比较有用。因为union all的每个部分可能相同，但是如果每个部分都去执行一遍的话，则成本太高，所以可以使用with as短语，则只要执行一遍即可。如果with as短语所定义的表名被调用两次以上，则优化器会自动将with as短语所获取的数据放入一个temp表里，如果只是被调用一次，则不会。而提示materialize则是强制将with as短语里的数据放入一个全局临时表里。很多查询通过这种方法都可以提高速度。
| 1 2 3 4 5 6 7 8 9 10 |
with
sql1
as (select to_char(a)
s_name from test_tempa),
sql2
as (select to_char(b)
s_name from test_tempb
where not exists
(select s_name
from sql1
where rownum=1))
select *
from sql1
union all
select *
from sql2
union all
select 'no
records' from dual
where not exists
(select s_name
from sql1
where rownum=1)
and not exists
(select s_name
from sql2
where rownum=1);
|
with as优点
增加了sql的易读性，如果构造了多个子查询，结构会更清晰；
更重要的是：“一次分析，多次使用”，这也是为什么会提供性能的地方，达到了“少读”的目标
介词
英语语法-介词
介词的定义和特征
介词是一种用来表示词与词, 词与句之间的关系的词。在句中不能单独作句字成分。介词后面一般有名词代词或相当于名词的其他词类，短语或从句作它的宾语。介词和它的宾语构成介词词组，在句中作主语, 状语，表语，补语或介词宾语。例如:
Most of the students went to the classroom.
大部分学生去了教室。
We play basketball on the sports ground.
我们在操场上打篮球。
介词常与动词,形容词,名词一起构成固定搭配。
belong to 属于 rely on 依靠
talk to 同...谈话 be afraid of 害怕
be strict with对...严格
介词一般放在名词之前。但它后面的介词宾语是疑问代词,疑问副词或者关系代词时，这些词提到了前面而只剩下介词在后了。
Where do you come from?
你是哪儿人?
Who are you talking to?
你在跟谁谈话呢?
What do you study for?
你为了什么而学习?
介词在英语词汇中所占比例很小，但它们的用法却非常灵活,复杂。下列为常用介词及含义:
about 关于,附近,大约,周围,随身.
I have bought a book about Shakespearean.
我买了一本有关莎士比亚的书。
There are about fifteen trees in the picture.
图片里大约有十五棵树。
above 在....上,高出,以上,超过,在...上游.
The plane is flying above the clouds.
飞机在云上飞行。
I think the man is above sixty years old.
我想那人有六十多岁了。
across 横过,对面,交叉,在...的对面.
Can you swim across the river？
你能游过河吗？
We live across the street.
我们住在街的对面。
after 在...后面,依照.
He went home after school.
他放学后就回家了。
Read after me, please.
请跟我朗读。
against 撞到，靠着,反对,违背,
The car hit against the tree.
汽车撞了树。
He is standing against the wall.
他靠墙站着。
along 沿着,顺着.
They are walking along the river.
他们沿着河行走。
among 在...当中.
He is the tallest among them.
他是他们当中个子最高的。
around 在...的周围,在...那一边.
They sat around the table talking the news.
他们绕桌而坐谈论新闻。
There is a drugstore around the corner.
拐角处有一家药店。
as 作为.
He doesn't like people treat him as a child.
他......余下全文>>
请看2005年一道湖南高考试题：
The more I think about him, the more reasons I find for loving him _____ I did.
A. as much as B. as long as
C. as soon as D. as far as
在2005的高考考纲词汇表和英语教学大纲中对as的用法表述如下：1）conj. & adv.像……一样；如同; 2）as...as...像，如同； 3）not so...as不像、不如； 4）as if / as though好像、好似；5）prep. 作为、当作。但从历年高考试题可以看出，作为高考重要考点的as的命题范围不仅仅限于以上五点，这是由as本身的多功能用法所决定的。本文结合历年高考试题，对其常见的考点进行剖析。
考点一：用于比较级，表示“相等、相似、一样”。
结构是as...as...，前一个as是副词，在否定句中也可以用so，后跟被修饰的形容词或副词原级。如果所跟形容词修饰可数名词单数，则用as + adj. + a (an) + 名词 + as...，其中后面的as是连词。
[考例]
He speaks English well indeed, but of course not _____ a native speaker. (2004上海)
A. as fluent as B. more fluent than
C. so fluently as D. much fluently than
[解析]答案为C。根据句意可看出应该是用副词修饰谓语动词speak，排除形容词的A、B项；根据比较级排除D项，C项是so...as...结构，由于是否定句，所以选“so fluently as”。
考点二：as many / much / far / long...as + 具体数目表示达到某种程度,译作“多达…… / 远到…… / 时间长达……”。
[考例1]
John is the tallest boy in the class _______ according to himself. (2005安徽)
A. five foot eight as tall as
B. as tall as five foot eight
C. as five foot eight tall as
D. as tall five foot eight as
[解析] 答案为B。根据句意可看出是表示“高达5.8英尺”，所以用本结构，表示具体高度的five foot eight应该置于连词as之后，排除A、C和D。
[考例2]
After supper she would sit down by the fire, sometimes for _______ an hour, thinking of her young and happy days. (2003上海春招)
A. as long as B. as soon as
C. as much as D. as many as
[解析] 答案为A。本题考查相同比较级结构的不同意义。根据题干中的具体时间an hour可以看出是指时间的长度。
考点三：as用于表示倍数的句型中
如果表示A是B的X倍可以用句型“A is X times as + adj. /......余下全文>>