一个表做无限级分类实现的查询问题

[诱惑di街]

有表tab_class
class_id int 分类ID
Class_name varchar(50) 分类的名称
father_id int 父类的ID

要求查询指定分类的下，所有的子级分类的ID，该如何实现

[中国风]

MAXRECURSION 0--加上，默认100层 e.g.

;WITH    CTETree
          AS ( SELECT   *
               FROM     tab_class AS t
               WHERE    NOT EXISTS ( SELECT 1
                                     FROM   tab_class
                                     WHERE  class_id = t.father_id )
               UNION ALL
               SELECT   t.*
               FROM     tab_class AS t
                        INNER JOIN CTETree AS t2 ON t.father_id = t2.class_id
             )
    SELECT  *
    FROM    CTETree
OPTION  ( MAXRECURSION 0 );

[二月十六]

用cte递归查询：

--测试数据
if not object_id(N'Tempdb..#tab_class') is null
	drop table #tab_class
Go
Create table #tab_class([class_id] int,[Class_name] nvarchar(23),[father_id] int)
Insert #tab_class
select 1,N'测试1',0 union all
select 2,N'测试2',1 union all
select 3,N'测试3',1 union all
select 4,N'测试4',3 union all
select 5,N'测试5',4
Go
--测试数据结束
;WITH cte AS (
Select * from #tab_class WHERE class_id=3
UNION ALL
SELECT #tab_class.* FROM #tab_class JOIN cte ON cte.class_id = #tab_class.father_id
)
SELECT * FROM cte

[Hello World,]

使用cet语句查询 with cet as ( select * from tab_class where father_id is null union all select a.* from tab_class a inner join cet on a.father_id=cet.class_id ) select * from cet

[中国风]

指定分类下的所有子类,参照 e.g.

DECLARE @father_id INT
SET @father_id=1
;WITH    CTETree
          AS ( SELECT   *
               FROM     tab_class AS t
               WHERE    t.father_id=@father_id
               UNION ALL
               SELECT   t.*
               FROM     tab_class AS t
                        INNER JOIN CTETree AS t2 ON t.father_id = t2.class_id
             )
    SELECT  *
    FROM    CTETree
OPTION  ( MAXRECURSION 0 );