关于各个数据库递归（start with connect by prior）的相互转换

查找根结点（自下而上）

Oracle/KingBase8

select D.No from Port_Dept D start with D.No='1061' connect by prior D.ParentNo = D.No;

SQL Server／DB2

WITH allsub(No,Name,ParentNo) as (
SELECT No,Name,ParentNo FROM Port_Dept where No='1061' 
UNION ALL SELECT a.No,a.Name,a.ParentNo FROM Port_Dept a,allsub b where a.No = b.ParentNo
) 
select * from allsub

MySQL

select t.No,t.Name,t.ParentNo from (
    select @No idlist,
    (select @No:=group_concat(ParentNo separator ',') from Port_Dept where find_in_set(No,@No)) sub
    from Port_Dept,(select @No:='1061') vars
    where @No is not null) tl,Port_Dept t
where find_in_set(t.No,tl.idlist)

查找叶子结点，自上而下

Oracle/KingBase8

select D.No from Port_Dept D start with D.No='100' connect by prior D.No = D.ParentNo;

SQL Server

WITH allsub(No,Name,ParentNo) as (
SELECT No,Name,ParentNo FROM Port_Dept where No='100' 
UNION ALL SELECT a.No,a.Name,a.ParentNo FROM Port_Dept a,allsub b where a.ParentNo = b.No
) 
select * from allsub

MySQL

select t.* from (
    select @No idlist, @lv:=@lv+1 lv,
    (select @No:=group_concat(No separator ',') from Port_Dept where find_in_set(ParentNo,@No)) sub
    from Port_Dept,(select @No:='100',@lv:=0) vars
    where @No is not null) tl,Port_Dept t
where find_in_set(t.No,tl.idlist);