关于类别的父与子的查询

Question

实现效果：工业
  汽车
    轿车
    越野轿车
  火车
农业
  小麦
  玉米
牧业
  推车
现在要实现功能是搜索车  like '%车%'  就将所有的车的类别   以及它的所有父级节点全部列出。id  //标识
pid  //记录标识ID
name   //类别名称表名称为：TargetClassification
需要写一个存储过程，请各位高手大哥帮忙指点一下，谢谢。

Answer 1

使用CTE递归遍历层次关系，在微软的示例AdventureWork数据库中
WITH EmployeePath( EmployeeID,ManagerID,LV)
AS
(
SELECT EmployeeID,ManagerID,1
FROM HumanResources.Employee WHERE ManagerID=109
UNION ALL
SELECT A.EmployeeID,A.ManagerID,LV+1 FROM HumanResources.Employee AS A JOIN
EmployeePath AS B ON A.ManagerID=B.EmployeeID
)
SELECT * FROM EmployeePath

Answer 2

#1.用SQL直接生成具有多层次的XML，写一句SQL语句不太好生成
#2.一般这种问题的处理方法是：用递归CTE查询出所有的记录，且标注它们之间的关系，及级别，然后用C#代码来生成XML结构。
CREATE TABLE #TargetClassification(id INT, pid INT, NAME VARCHAR(10))
INSERT #TargetClassification
SELECT 1, 0, '工业' UNION ALL
SELECT 2, 0, '农业' UNION ALL
SELECT 3, 0, '牧业' UNION ALL
SELECT 4, 1, '汽车' UNION ALL
SELECT 5, 1, '火车' UNION ALL
SELECT 6, 4, '轿车' UNION ALL
SELECT 7, 4, '越野轿车' UNION ALL
SELECT 8, 2, '小麦' UNION ALL
SELECT 9, 2, '玉米' UNION ALL
SELECT 10, 3, '推车';WITH cte AS
(
SELECT id, pid, name FROM #TargetClassification WHERE name LIKE '%车%'
UNION ALL
SELECT b.id, b.pid, b.name FROM cte a INNER JOIN #TargetClassification b ON a.pid=b.id
),
ctelevel AS
(
SELECT [level] = 1, id, pid, name FROM #TargetClassification WHERE pid = 0
UNION ALL
SELECT [level]=a.[level]+1, b.id, b.pid, b.name FROM ctelevel a INNER JOIN #TargetClassification b ON a.id=b.pid
)
SELECT DISTINCT [level], a.NAME, a.id, a.pid
FROM cte A
INNER JOIN ctelevel B
ON a.NAME = b.name
ORDER BY a.pid, id/*
level NAME id pid
1 工业 1 0
1 牧业 3 0
2 汽车 4 1
2 火车 5 1
2 推车 10 3
3 轿车 6 4
3 越野轿车 7 4
*/