oracle start with connect by PRIOR的使用

select * from mofmworkcenter 

这里华立工作重心是父节点，子节点有华立1，华立1下有华立2,3，华立3下有华立4.

select * from mofmworkcenter wc start with wc.workcenterid ='4f583dcc-79e5-8429-41c4-8cae6ed7cacd' connect by prior wc.id=wc.workcenterid

prior和子ID在一起，表示向下查询。

select * from mofmworkcenter wc start with wc.workcenterid ='4f583dcc-79e5-8429-41c4-8cae6ed7cacd' connect by prior wc.workcenterid= wc.id

prior和父节点在一起，向上查询。

sqlserver写法：

with cte(id,workcenterid)
as
(
select id,workcenterid from mofmworkcenter where workcenterid='4f583dcc-79e5-8429-41c4-8cae6ed7cacd'
union all
select m.id,m.workcenterid from mofmworkcenter as m 
inner join cte as c on m.workcenterid=c.id
)
select id,workcenterid from cte

实现的是根据父ID向下查询。

嵌套写法：

declare @sql table(id varchar(36))
 insert into @sql(id) (select id from mofmworkcenter where workcenterid='4f583dcc-79e5-8429-41c4-8cae6ed7cacd')
 select * from mofmworkcenter where workcenterid in (select * from @sql)

这里定义了一个变量去接受第一条查询的结果。

虽然这样会使sql更容易维护，但是由于表变量实际上使用了临时表，从而增加了额外的I/O开销，因此，表变量的方式并不太适合数据量大且频繁查询的情况。

为此，在SQL Server 2005中提供了另外一种解决方案，这就是公用表表达式（CTE），使用CTE，可以使SQL语句的可维护性，同时，CTE要比表变量的效率高得多。

上面的sqlserver写法便是用了cte，接着

 

with cte as
 (select id from mofmworkcenter where workcenterid='4f583dcc-79e5-8429-41c4-8cae6ed7cacd')
 select * from mofmworkcenter where workcenterid in (select * from cte)

同样实现了上条的查询。

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

1.在cte后面必须跟着使用cte的sql语句（select，update，insert等），否则cte会失效。

2.在cte后面可以跟其他的cte，但只能又一个with，多个cte直接用逗号隔开，如

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,但这条之后就使用表或视图了。

4.cte可以引用自身，如上面sqlserver写法。

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)