SQL QUERY 选择特定老板手下的员工

Question

一个具有挑战性的问题，我有如下表格

员工ID 老板ID

pic http://img16.imageshack.us/img16/7659/20130430113245.jpg

创建查询的任何想法，以便当某个员工进入时，它将查询他下面的所有员工以及他的老板是谁

Answer 1

试试这个查询

你必须使用 Sohail 提到的 CTE。

WITH DirectReports (bossId, EmpID, Level)
AS
(
-- Anchor member definition
    SELECT bossId, empId,
        0 AS Level
    FROM tbl
    WHERE empId = 2
    UNION ALL
-- Recursive member definition
    SELECT e.bossId, e.empId,
        Level + 1 AS Level
    FROM tbl e
    INNER JOIN DirectReports AS d
        ON e.bossId = d.empId
)
-- Statement that executes the CTE
SELECT *
FROM DirectReports;

SQL FIDDLE

| BOSSID | EMPID | LEVEL |
--------------------------
|      1 |     2 |     0 |
|      2 |     4 |     1 |
|      4 |     5 |     2 |
|      4 |     6 |     2 |