sql 中with 语句使用

参考 http://blog.sina.com.cn/s/blog_9e0bd57301016mru.html

多个子句时，

with branch_total(branch_name,val)as

(select branch_name,sum(balance)

fro m account

group by branch_name),             //逗号不可少

branch_total_avg(val)as

(select avg(val)

fro m branch_total)

select branch_name

fro m branch_total,branch_total_avg

where branch_total.val>=branch_total_avg.val

并且上一条语句必须以‘；’结尾

in

select * from tab where field in ('A', 'B', 'C')

等价于select * from tab where field = 'A' or field = 'B' or field = 'C'

on永在表连接的时候

select * from a inner/left/right join b on a.xx = b.xx

with用法：

创建一个表：

create table regr (pid integer,id integer, name char(20))

alter table regr alter id set not null add primary key(id)

insert into regr values(-1,1,'library'),(1,2,'news'),(2,3,'world news'),(2,4,'politics'),(2,5,'bussiness')

(2,6,'science'),(2,7,'technology'),(1,8,'sports'),(8,9,'local'),(8,10,'collegiate'),(8,11,'professional')

(9,12,'soccer'),(10,13,'soccer'),(11,14,'soccer'),(9,15,'football'),(10,16,'football'),(11,17,'football'

使用with得到数据树

WITH RPL (PID, ID, name) AS      

(SELECT ROOT.PID, ROOT.ID, ROOT.NameFROM regr ROOT      

WHERE ROOT.PID = 8

UNION ALL    

SELECT CHILD.PID, CHILD.ID, CHILD.Name

FROM RPL PARENT, regr CHILD

WHERE PARENT.ID = CHILD.PID)

SELECT DISTINCT PID, ID, Name      

FROM RPL

ORDER BY PID, ID, Name

RPL 作为一个具有以下三列的虚拟表：PID、ID 和 name。

WITH 子句内的第一个 SELECT 语句是初始化表。它只执行一次。它的结果形成虚拟表的初始内容以作为递归的种子。在上面的示例中，种子是 PID 为 8 的一行或多行。

第二个 SELECT 语句执行多次。将种子作为输入（JOIN 中的辅助表）传递给第二个 SELECT 语句以产生下一个行集合。将 JOIN 的结果添加（UNION ALL）到虚拟表的当前内容中，并放回到其中以形成用于下一次传递的输入。只要有行产生，这个过程就会继续。

sql

with

as

用法(适用sqlserver，好像oracle也适用)

Server

2005中提供了公用表表达式（CTE），使用CTE，可以使SQL语句的可维护性，同时，CTE要比表变量的效率高得多。

下面是CTE的语法：

[

WITH

<common_table_expression>

[

,n

]

]

<

common_table_expression>::=

expression_name

[

(

column_name

[

,n

]

)

]

AS

(

CTE_query_definition

)

现在使用CTE来解决上面的问题，SQL语句如下：

with

cr

as

(

select

CountryRegionCode

from

person.CountryRegion

where

Name

like

'C%'

)

select

*

from

person.StateProvince

where

CountryRegionCode

in

(select

*

from

cr)

其中cr是一个公用表表达式，该表达式在使用上与表变量类似，只是SQL

Server

2005在处理公用表表达式的方式上有所不同。

在使用CTE时应注意如下几点：

1.

CTE后面必须直接跟使用CTE的SQL语句（如select、insert、update等），否则，CTE将失效。如下面的SQL语句将无法正常使用CTE：

with

cr

as

(

select

CountryRegionCode

from

person.CountryRegion

where

Name

like

'C%'

)

select

*

from

person.CountryRegion

--

应将这条SQL语句去掉

--

使用CTE的SQL语句应紧跟在相关的CTE后面

--

select

*

from

person.StateProvince

where

CountryRegionCode

in

(select

*

from

cr)

2.

CTE后面也可以跟其他的CTE，但只能使用一个with，多个CTE中间用逗号（,）分隔，如下面的SQL语句所示：

with

cte1

as

(

select

*

from

table1

where

name

like

'abc%'

),

cte2

as

(

select

*

from

table2

where

id

>

20

),

cte3

as

(

select

*

from

table3

where

price

<

100

)

select

a.*

from

cte1

a,

cte2

b,

cte3

c

where

a.id

=

b.id

and

a.id

=

c.id

3.

如果CTE的表达式名称与某个数据表或视图重名，则紧跟在该CTE后面的SQL语句使用的仍然是CTE，当然，后面的SQL语句使用的就是数据表或视图了，如下面的SQL语句所示：

--

table1是一个实际存在的表

with

table1

as

(

select

*

from

persons

where

age

<

30

)

select

*

from

table1

--

使用了名为table1的公共表表达式

select

*

from

table1

--

使用了名为table1的数据表

4.

CTE

可以引用自身，也可以引用在同一

WITH

子句中预先定义的

CTE。不允许前向引用。

5.

不能在

CTE_query_definition

中使用以下子句：

（1）COMPUTE

或

COMPUTE

BY

（2）ORDER

BY（除非指定了

TOP

子句）

（3）INTO

（4）带有查询提示的

OPTION

子句

（5）FOR

XML

（6）FOR

BROWSE

6.

如果将

CTE

用在属于批处理的一部分的语句中，那么在它之前的语句必须以分号结尾，如下面的SQL所示：

declare

@s

nvarchar(3)

set

@s

=

'C%'

--

必须加分号

with

t_tree

as

(

select

CountryRegionCode

from

person.CountryRegion

where

Name

like

@s

)

select

*

from

person.StateProvince

where

CountryRegionCode

in

(select

*

from

t_tree)

7、CTE除了可以简化嵌套SQL语句外，还可以进行递归调用

---