树形结构表查找叶子节点问题

Question

表结构：
id(编号),pid（父节点编号）,lev（层次号）,nam（名字）数据示例
11,null,1,'大类A'
12,null,1,'大类B'
13,null,1,'大类C'
21,11,2,'小类A1'
22,11,2,'小类A2'
23,11,2,'小类A3'
31,21,3,'明细A1'
32,21,3,'明细A2'
33,21,3,'明细A3'
问题：
给定'11','22',要查找它们的叶子节点集合

Answer 1

--测试数据
CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))
INSERT tb SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'
GO--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3),Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @ID,@Level
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level
FROM tb a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END
RETURN
END
GO--调用函数查询002及其所有子节点
SELECT a.*
FROM tb a,f_Cid('002') b
WHERE a.ID=b.ID
/*--结果
ID   PID  Name       
------ ------- ---------- 
002  001  烟台市
004  002  招远市
--*/

Answer 2

select id into # from tb where id in('11','22')
while @@rowcount>0
 insert # select id from tb 
          where id not in(select id from #)
          and   pid in (select id from #)
--如果要所有子节点，直接得到
--如果要所有叶子节点，还要删除不是叶子节点的。
delete # where id in(select pid from tb) select * from tb where id in(select id from #)

Answer 3

-2005 CTE 递歸
示例：
USE tempdb
CREATE TABLE Employees
(
  empid   int         NOT NULL,
  mgrid   int         NULL,
  empname varchar(25) NOT NULL,
  CONSTRAINT PK_Employees PRIMARY KEY(empid),
  CONSTRAINT FK_Employees_mgrid_empid
    FOREIGN KEY(mgrid)
    REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
SET NOCOUNT ON
INSERT INTO Employees VALUES(1 , NULL, 'Nancy')
INSERT INTO Employees VALUES(2 , 1   , 'Andrew')
INSERT INTO Employees VALUES(3 , 1   , 'Janet')
INSERT INTO Employees VALUES(4 , 1   , 'Margaret') 
INSERT INTO Employees VALUES(5 , 2   , 'Steven')
INSERT INTO Employees VALUES(6 , 2   , 'Michael')
INSERT INTO Employees VALUES(7 , 3   , 'Robert')
INSERT INTO Employees VALUES(8 , 3   , 'Laura')
INSERT INTO Employees VALUES(9 , 3   , 'Ann')
INSERT INTO Employees VALUES(10, 4   , 'Ina')
INSERT INTO Employees VALUES(11, 7   , 'David')
INSERT INTO Employees VALUES(12, 7   , 'Ron')
INSERT INTO Employees VALUES(13, 7   , 'Dan')
INSERT INTO Employees VALUES(14, 11  , 'James')
經理-雇員關系圖如下：
 問題：查詢某个雇员（例如，empid=3 的 Janet）及其所有下属WITH EmpCTE(empid, empname, mgrid, lvl)
AS
( 
  -- Anchor Member (AM)
  SELECT empid, empname, mgrid, 0
  FROM Employees
  WHERE empid = 3
  UNION ALL
  -- Recursive Member (RM)
  SELECT E.empid, E.empname, E.mgrid, M.lvl+1
  FROM Employees AS E
    JOIN EmpCTE AS M
      ON E.mgrid = M.empid
)
SELECT * FROM EmpCTE;
返回結果：
empid       empname               mgrid       lvl
-------- ----------------- ----------- -----------
3           Janet                     1           0
7           Robert                    3           1
8           Laura                     3           1
9           Ann                       3           1
11          David                     7           2
12          Ron                       7           2
13          Dan                       7           2
14          James                     11          3