关于数据库的递归查询

1、Oracle

以scott.emp表举例：empno是人员编号，mgr是上级领导（也就是上级人员编码）

（1）、从上到下查询

--该查询查询员工JONES下属所有的员工
select emp.* 
from emp
start with ename='JONES'
CONNECT BY mgr=PRIOR empno;

 

7566 JONES MANAGER 7839 02-4月 -81 2975 (null) 20
7788 SCOTT ANALYST 7566 19-4月 -87 3000 (null) 20
7876 ADAMS CLERK 7788 23-5月 -87 1100 (null) 20
7902 FORD ANALYST 7566 03-12月-81 3000 (null) 20
7369 SMITH CLERK 7902 17-12月-80 800 (null) 20

(2)、从下到上查询

--该查询查询员工JONES上面的所有的员工
select emp.* 
from emp
start with ename='JONES'
CONNECT BY PRIOR mgr=empno;

 

7566 JONES MANAGER 7839 02-4月 -81 2975 (null) 20
7839 KING PRESIDENT (null) 17-11月-81 5000 (null) 10
(3)、中见到两边查询

--该查询以员工JONES为准查询上下级所有员工
select emp.*
from emp
start with ename='JONES'
CONNECT BY mgr=PRIOR empno
UNION
select emp.* 
from emp
start with ename='JONES'
CONNECT BY PRIOR mgr=empno;

 

 

select 
tab_temp.ID,tab_temp.FID,tab_temp.USER_ID,tab_temp.USER_NAME,LTRIM(SYS_CONNECT_BY_PATH(NCHR(TAB_TEMP.NEWORDER),','),',') NEWORDER
from
(
  select 
  G_DEPT.ID,G_DEPT.FID,G_DEPT.USER_ID,G_USERS.UNAME USER_NAME,LEVEL LEV,G_USERS.UTYPE,
  (ROW_NUMBER () OVER (PARTITION BY G_DEPT.FID ORDER BY G_DEPT.SHORDER)) NEWORDER
  from G_DEPT
  inner join G_USERS on G_USERS.ID=G_DEPT.USER_ID
  where 1=1 and G_USERS.STATUS>-1 and G_USERS.ISNATIVE=1
  start with G_DEPT.FID=0
  CONNECT by prior G_DEPT.ID=G_DEPT.FID
) tab_temp
where 1=1 and TAB_TEMP.UTYPE IN(0,9)
start with tab_temp.FID=0
CONNECT by prior tab_temp.ID=tab_temp.FID
order by NEWORDER;

select G_USERS.ID,G_USERS.UNAME,G_DEPT.FID,G_DEPT.ID,
SYS_CONNECT_BY_PATH(G_DEPT.SHORDER,',') bb,
level lv
from (
     select G_DEPT.ID,G_DEPT.FID,G_DEPT.USER_ID,G_DEPT.ISMAIN,
     LPAD(row_number() over(partition by G_DEPT.FID order by G_DEPT.SHORDER),3,'0') SHORDER
     from G_DEPT
     where 1=1
) G_DEPT
inner join G_USERS on G_USERS.ID=G_DEPT.USER_ID
where 1=1and G_USERS.STATUS>-1 and G_USERS.UTYPE=0 and G_DEPT.ISMAIN=1
start with G_DEPT.USER_ID IN(
      select tab_inner1.User_Id from G_DEPT tab_inner1
      inner join G_DEPT tab_inner2 on tab_inner2.ID=tab_inner1.Fid
      where tab_inner2.USER_ID=(select ID from G_USERS where UTYPE=8 and status>-1 and isnative=1)
)
connect by prior G_DEPT.ID=G_DEPT.FID
order by bb;

2、sql server

把emp表的数据迁移到sql Server上面来

(1)、从下级到上级取数据

--以员工JONES为准获取上级的所有员工
WITH OrgPath(empno,mgr)
AS
(
 SELECT empno,mgr FROM emp WHERE ename='JONES'
 
 UNION ALL
 
 SELECT emp.empno,emp.mgr
 FROM emp 
 INNER JOIN OrgPath on emp.empno=OrgPath.mgr
)
SELECT  emp.*
FROM emp
INNER JOIN OrgPath ON OrgPath.empno=emp.empno;

 

(数据和Oracle上的一样)

(2)、从上级到下级取数据

--以员工JONES为准获取下级的所有员工
WITH OrgPath(empno,mgr)
AS
(
 SELECT empno,mgr FROM emp WHERE ename='JONES'
 
 UNION ALL
 
 SELECT emp.empno,emp.mgr
 FROM emp 
 INNER JOIN OrgPath on emp.mgr=OrgPath.empno
)
SELECT  emp.*
FROM emp
INNER JOIN OrgPath ON OrgPath.empno=emp.empno;

 

(数据与Oracle的一样)
(3)、从中间到两边取数据

--以员工JONES为准获取上下级的所有员工
WITH 
TempPath(empno,mgr)
AS
(
 SELECT empno,mgr FROM emp WHERE ename='JONES'
 
 UNION ALL
 
 SELECT emp.empno,emp.mgr
 FROM emp 
 INNER JOIN TempPath on emp.mgr=TempPath.empno
),
OrgPath(empno,mgr)
AS
(
 SELECT empno,mgr FROM emp WHERE ename='JONES'
 
 UNION ALL
 
 SELECT emp.empno,emp.mgr
 FROM emp 
 INNER JOIN OrgPath on emp.empno=OrgPath.mgr
)
SELECT  emp.* FROM emp
WHERE 1=1
AND (
 emp.empno IN(SELECT empno FROM TempPath)
 OR
 emp.empno IN(SELECT empno FROM OrgPath)
);

 

（数据与Oracle数据一样）

注：其实oracle数据也是支持with语法查询的，但是sql Server 不支持start with语法查询

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

其他数据库以后更新。。。。