Oracle:
语法:
SQL Server:
语法:
select ... from ...
connect by {prior column1 = column2 | column1 = prior column2 }
start with ...
order siblings by ...
比如:
select * from unit where isdel <> 1 and jibie < 4 --查询基础表
connect by prior id = parentid and isdel <> 1 --prior放在哪个字段前,哪个就是递归的上一层,可以设置向上或向下递归
start with jibie = '1' --start with则是递归的起始位置
order siblings by ordercode --siblings指定同一父节点下的兄弟节点的顺序
SQL Server:
语法:
with cte[columns]
as
(
select ... from tab where ...
union all
select ... from tab inner join cte on tab.column1 = cte.colum2
) select ... from cte
比如:
with cte as --查找子节点
(
select * from menu where id = 0 --递归入口
union all
select a.* from menu a , cte b where a.pid = b.id --递归条件,设置查子节点还是父节点
)
select * from cte
但查出的效果却不合乎要求,如下图
若是查找父节点,效果符合要求。
另外,排序好像还有问题,或者是暂时还没找到排序方法!<标记一下>