Oracle 转 postgresql 递归 connect_by_isleaf 方案

user_number, LTRIM( SYS_CONNECT_BY_PATH ( NAME, ‘,‘ ), ‘,‘ ) NAME ,RN FROM ( SELECT u.user_number, r.NAME, ROW_NUMBER ( ) OVER ( PARTITION BY u.user_number ORDER BY ur.role_id ) RN FROM ems_role r, ( SELECT ur.user_id, ur.role_id FROM ems_user_role ur UNION SELECT ur.user_id, ur.role_id FROM ems_user_device_role ur ) ur, ems_user u WHERE ur.user_id = u.ID AND r.ID = ur.role_id ) WHERE 1=1 and connect_by_isleaf = 1 START WITH rn = 1 CONNECT BY RN - 1 = PRIOR RN AND user_number = PRIOR user_number

postgresql 

select user_number,name from 
(
 select alias1.*,
 row_number() over (partition by alias1.user_number order by rn desc) as leaf
from (    
with RECURSIVE cte as
        (
        select a.user_number,cast(A.name as varchar(1000)),A.rn from (
        SELECT
                    u.user_number,
                    r.NAME,
                    ROW_NUMBER ( ) OVER ( PARTITION BY u.user_number ORDER BY ur.role_id ) RN
                FROM
                    ems_role r,
                    ( SELECT ur.user_id, ur.role_id FROM ems_user_role ur 
                    UNION SELECT ur.user_id, ur.role_id FROM ems_user_device_role ur ) ur,
                    ems_user u 
                WHERE
                    ur.user_id = u.ID 
                    AND r.ID = ur.role_id   ) A where a.rn = 1
        union all 
        select k.user_number,cast(c.name||‘,‘||k.name as varchar(1000)) as path,k.rn from (SELECT
                    u.user_number,
                    r.NAME,
                    ROW_NUMBER ( ) OVER ( PARTITION BY u.user_number ORDER BY ur.role_id) RN 
                FROM
                    ems_role r,
                    ( SELECT ur.user_id, ur.role_id FROM ems_user_role ur 
                    UNION SELECT ur.user_id, ur.role_id FROM ems_user_device_role ur ) ur,
                    ems_user u 
                WHERE
                    ur.user_id = u.ID 
                    AND r.ID = ur.role_id) k inner join cte c on c.RN + 1 = k.rn and c.user_number = k.user_number
        )
    select e.* from cte e where 1=1 
) as alias1 where 1=1 
) as  alias2 where leaf =1

 

Oracle 转 postgresql 递归 connect_by_isleaf 方案

标签：ber   user   desc   path   span   方案   tar   acl   color