学步园

设计资料表中的字段必须有的ID和ParentID 他们的关系是自身引用。

常用的两种方法：

1.节点函数

create function f_cid(@ID int) returns @t_level table(id int , level int)<br /> as<br /> begin<br /> declare @level int<br /> set @level = 1<br /> insert into @t_Level @id , @level</p> <p>while @@ROWCOUNT > 0<br /> begin<br /> set @level = @level 1<br /> insert into @t_level select @id , @level</p> <p>from Article_Type a , @t_Level b<br /> where a.ParentID = b.id and b.level = @level - 1<br /> end<br /> return<br /> end<br /> go</p> <p>

调用

select a.* from tb a , f_cid(1) b where a.id = b.id order by a.id</p> <p>

2.存储过程方式

IF object_id('rArticle_TypeByParentID') IS NOT NULL<br /> DROP PROCEDURE rArticle_TypeByParentID<br /> GO<br /> CREATE PROCEDURE rArticle_TypeByParentID<br /> @ParentID int ,<br /> @HasParent bit =0<br /> AS<br /> BEGIN</p> <p> SET NOCOUNT ON</p> <p> DECLARE @TypeID TABLE(ID INT)</p> <p> ;With CTE_Article_TypeID AS<br /> (<br /> SELECT ID FROM Article_Type WHERE ParentID=@ParentID AND Effectiveness=1<br /> UNION ALL<br /> SELECT a.ID FROM (SELECT *FROM Article_Type WHERE Effectiveness=1) AS a INNER JOIN CTE_Article_TypeID AS b ON a.ParentID=b.ID<br /> )</p> <p> INSERT INTO @TypeID SELECT ID FROM CTE_Article_TypeID </p> <p> IF (@HasParent<>0)<br /> BEGIN<br /> INSERT INTO @TypeID SELECT ID FROM Article_Type WHERE ID=@ParentID AND Effectiveness=1</p> <p> END</p> <p> SELECT ID,Name,ParentID,Seq,Effectiveness FROM Article_Type WHERE ID IN(SELECT ID FROM @TypeID) ORDER BY ParentID ,SEQ</p> <p>END</p> <p>

这两种方法比较下第二种方法是比教灵活的