MSSQL中实现领导查询

 原帖见于 http://blog.csdn.net/wzy0623/archive/2007/06/08/1644049.aspx 

Oracle我没用过，这里提供一种MSSQL的实现方法，如果存在级数大于3的话，可以根据需要增加左连接的方法实现，首先建立所需要的资料表，执行一下语句可以实现对所需要数据的查询。

select f.emp_id ,
       case 
           when f.aemp_id is null then f.manager_id
           when f.aemp_id is not null and f.bemp_id is null then f.amanager_id
           else f.bmanager_id
       end   'manager_id'
from 
(
select emp.*,s.* from emp left join 
(select a.emp_id aemp_id,
        a.manager_id amanager_id,
        b.emp_id bemp_id ,
        b.manager_id bmanager_id
 from emp a left join emp b  on a.manager_id=b.emp_id) s
on emp.manager_id=s.aemp_id 
) f 

 一个比较难的SQL（Oracle 9i 实现 10G 的 CONNECT_BY_ISLEAF、CONNECT_BY_ROOT 等功能）

有一个emp表，2个字段，员工id和主管id. 1，emp_id, 2,manager_id
假如有以下资料,一个员工可以对应一个或多个主管id,即一个员工可能有几个主管。
emp_id manager_id
001      101
001      102
101      201
102      202
002      102
003      103
103      203
201      301
203      303

现在要通过任何一个员工id,能查到他的最高主管的id，可能结果不止一笔。

即如果是001，则结果如下：
emp_id manager_id
001      301
001      202
........................
即如果是002，则结果如下：
emp_id manager_id
002      102
........................
即如果是103，则结果如下：
emp_id manager_id
103      303
......................................
以下是建表脚本及资料：
 

CREATE   TABLE  emp
(
emp_id  VARCHAR2 ( 10  ),
manager_id  VARCHAR2 ( 10  )
);

INSERT   INTO  emp
            (emp_id, manager_id
            )
      VALUES  ( ' 001 ' ,  ' 101 '
            );
INSERT   INTO  emp
            (emp_id, manager_id
            )
      VALUES  ( ' 001 ' ,  ' 102 '
            );
INSERT   INTO  emp
            (emp_id, manager_id
            )
      VALUES  ( ' 101 ' ,  ' 201 '
            );
INSERT   INTO  emp
            (emp_id, manager_id
            )
      VALUES  ( ' 102 ' ,  ' 202 '
            );
INSERT   INTO  emp
            (emp_id, manager_id
            )
      VALUES  ( ' 002 ' ,  ' 102 '
            );
INSERT   INTO  emp
            (emp_id, manager_id
            )
      VALUES  ( ' 003 ' ,  ' 103 '
            );
INSERT   INTO  emp
            (emp_id, manager_id
            )
      VALUES  ( ' 103 ' ,  ' 203 '
            );
INSERT   INTO  emp
            (emp_id, manager_id
            )
      VALUES  ( ' 201 ' ,  ' 301 '
            );
INSERT   INTO  emp
            (emp_id, manager_id
            )
      VALUES  ( ' 203 ' ,  ' 303 '
            );
COMMIT  ;

有人给出这样的答案：

SELECT  emp_id, manager_id
   FROM  ( SELECT  CONNECT_BY_ROOT (emp_id) emp_id, manager_id,
               CONNECT_BY_ISLEAF v_isleaf
         FROM  emp
        CONNECT  BY  emp_id  =  PRIOR manager_id)
WHERE  v_isleaf  =   1 ;

这个写法非常简洁，用到了10G connect by 增强的特性，如判断是否叶子节点的伪列 CONNECT_BY_ISLEAF，只使用根行返回结果的一元操作符 CONNECT_BY_ROOT 等，很好。但提问者说使用的是 9i，这就有些麻烦了，能否使用一个 sql 而不是 plsql 实现呢？深入研究后给出了我的 sql：

SELECT  emp_id, manager_id
   FROM  ( SELECT  FIRST_VALUE (emp_id)  OVER  (PARTITION  BY  part  ORDER   BY  lev) emp_id,
               ROW_NUMBER ()  OVER  (PARTITION  BY  part  ORDER   BY  lev  DESC ) rn,
               part, manager_id  AS  manager_id
         FROM  ( SELECT  emp_id, manager_id,  LEVEL  lev, (ROWNUM  -   LEVEL ) part
                 FROM  emp
              CONNECT  BY  emp_id  =  PRIOR manager_id))
WHERE  rn  =   1 ;

9i 没有提供 CONNECT_BY_ISLEAF 及 CONNECT_BY_ROOT，但可以使用分析函数实现其基本功能，下面分析一下。

最内层的查询：
SELECT emp_id, manager_id, LEVEL lev, (ROWNUM - LEVEL) part
FROM emp
CONNECT BY emp_id = PRIOR manager_id;

这里用到了从叶子到跟的反向遍历，同时用 (ROWNUM - LEVEL) part 列的值表示一个从叶子到根的路径，为使用分析函数的分区条件做准备。

二层嵌套查询
SELECT FIRST_VALUE (emp_id) OVER (PARTITION BY part ORDER BY lev) emp_id,
       ROW_NUMBER () OVER (PARTITION BY part ORDER BY lev DESC) rn, part,
       manager_id AS manager_id
FROM (SELECT emp_id, manager_id, LEVEL lev, (ROWNUM - LEVEL) part
        FROM emp
      CONNECT BY emp_id = PRIOR manager_id);

按 part 分区，以 lev 正排序，再使用 FIRST_VALUE 操作使第一列都显示叶子节点；
按 part 分区，以 lev 倒排序，再使用 ROW_NUMBER () 函数划分等级，等级排第一的即为根节点。

最外层嵌套查询，取得所要求的结果输出。