使用? CTE? 来处理
?
--测试表与测试数据
CREATE?TABLE?test_tree?(
??test_id???INT??NOT?NULL,
??pid???????INT,
??test_val??VARCHAR(10),
??PRIMARY?KEY?(test_id)
);

INSERT?INTO?test_tree?VALUES(1,?NULL,???'.NET');
INSERT?INTO?test_tree?VALUES(2,?1,??????'C#');
INSERT?INTO?test_tree?VALUES(3,?1,??????'J#');
INSERT?INTO?test_tree?VALUES(4,?1,??????'ASP.NET');
INSERT?INTO?test_tree?VALUES(5,?1,??????'VB.NET');
INSERT?INTO?test_tree?VALUES(6,?NULL,???'J2EE');
INSERT?INTO?test_tree?VALUES(7,?6,??????'EJB');
INSERT?INTO?test_tree?VALUES(8,?6,??????'Servlet');
INSERT?INTO?test_tree?VALUES(9,?6,??????'JSP');
INSERT?INTO?test_tree?VALUES(10,?NULL,??'Database');
INSERT?INTO?test_tree?VALUES(11,?10,????'DB2');
INSERT?INTO?test_tree?VALUES(12,?10,????'MySQL');
INSERT?INTO?test_tree?VALUES(13,?10,????'Oracle');
INSERT?INTO?test_tree?VALUES(14,?10,????'SQL?Server');

INSERT?INTO?test_tree?VALUES(15,?13,????'PL/SQL');
INSERT?INTO?test_tree?VALUES(16,?15,????'Function');
INSERT?INTO?test_tree?VALUES(17,?15,????'Procedure');
INSERT?INTO?test_tree?VALUES(18,?15,????'Package');
INSERT?INTO?test_tree?VALUES(19,?15,????'Cursor');
INSERT?INTO?test_tree?VALUES(20,?14,????'T-SQL');


--??需要在?With?后面，加一个?recursive?关键字。否则会提示错误。

WITH?recursive?StepCTE
?(test_id,
??pid,
??test_val,
??Lev)
AS
(
SELECT
??test_id,
??pid,
??test_val,
??1?as?Lev
FROM
??test_tree
WHERE
??test_id?IN?(1,6,10)
UNION?ALL
SELECT
??T.test_id,
??T.pid,
??T.test_val,
??CTE.Lev?+?1
FROM
??test_tree?T?,?StepCTE?CTE
WHERE
??T.pid?=?CTE.test_id
)
SELECT
??test_id,?pid,?test_val,?Lev
FROM?StepCTE;

?test_id?|?pid?|??test_val??|?lev
---------+-----+------------+-----
???????1?|?????|?.NET???????|???1
???????6?|?????|?J2EE???????|???1
??????10?|?????|?Database???|???1
???????2?|???1?|?C#?????????|???2
???????3?|???1?|?J#?????????|???2
???????4?|???1?|?ASP.NET????|???2
???????5?|???1?|?VB.NET?????|???2
???????7?|???6?|?EJB????????|???2
???????8?|???6?|?Servlet????|???2
???????9?|???6?|?JSP????????|???2
??????11?|??10?|?DB2????????|???2
??????12?|??10?|?MySQL??????|???2
??????13?|??10?|?Oracle?????|???2
??????14?|??10?|?SQL?Server?|???2
??????15?|??13?|?PL/SQL?????|???3
??????20?|??14?|?T-SQL??????|???3
??????16?|??15?|?Function???|???4
??????17?|??15?|?Procedure??|???4
??????18?|??15?|?Package????|???4
??????19?|??15?|?Cursor?????|???4
(20?行记录)
?
?
上面的例子， 是从? 根节点，? 向下递归查找所有子节点的处理。

---

　　判断递归:只用看函数是否调用了其本身。类似这样的形式。有函数
int fun(int a)
{
..........
fun(a);
..........
}
这就是递归