SQL递归查询 [英] SQL recursive query

问题描述

我有一个表格类别，

1) 身份证
2) 类别名称
3) 分类大师

1) Id
2) CategoryName
3) CategoryMaster

数据为:

1 电脑 0
2 软件 1
3 多媒体 1
4 动画 3
5 生命值 0
6 Healthsub 5

with data as:

1 Computers 0
2 Software 1
3 Multimedia 1
4 Animation 3
5 Health 0
6 Healthsub 5

并且我创建了递归查询:

and i have created recursive query as:

 ;WITH CategoryTree AS
 (
  SELECT *, CAST(NULL AS VARCHAR(50)) AS ParentName, 0 AS Generation    
  FROM dbo.Category    
  WHERE CategoryName = 'Computers'

  UNION ALL        

  SELECT Cat.*,CategoryTree.CategoryName AS ParentName, Generation + 1    
  FROM dbo.Category AS Cat  INNER JOIN 
  CategoryTree ON Cat.CategoryMaster = CategoryTree.Id
 )

 SELECT * FROM CategoryTree

我得到了父类别的结果到底部，就像我得到了计算机的所有子类别

I get the results for parent category to bottom, like i get all sub categories for computer

但我想要自下而上的结果，比如从动画到计算机，请有人给我建议正确的方向.

but i want the results from bottom-up like from Animation to Computers, please can some one suggest me right direction.

先谢谢你:)

推荐答案

只需交换 join 子句中的字段:

Just swap the fields in the join clause:

WITH CategoryTree AS
        (
        SELECT  *, 0 AS Generation    
        FROM    dbo.Category
        WHERE   CategoryName = 'Animation'
        UNION ALL
        SELECT  Cat.*, Generation + 1    
        FROM    CategoryTree
        JOIN    dbo.Category AS Cat
        ON      Cat.Id = CategoryTree.CategoryMaster
        )
SELECT  *
FROM    CategoryTree

这篇关于SQL递归查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！