SQL筛选多级树形结构的所有叶子节点.docx

第一种方法：if not object_id(tb) is null drop table tbGoCreate table tb([id] int,[parentid] int,[name] varchar(10))Insert tbselect 1,0,null union allselect 2,0,null union allselect 3,1,null union allselect 4,3,null union allselect 5,2,nullGo--Title:查找指定節點下的子結點if object_id(fn_GetChildID)is not null drop function fn_GetChildIDgocreate function fn_GetChildID(@ParentID int)returns @t table(ID int,PID int)asbegin insert @t select ID,[parentid] from tb where ParentID=@ParentID while @@rowcount0 begin insert @t select a.ID,a.[parentid] from tb a inner join @t b on a.ParentID=b.id and not exists(select 1 from @t where id=a.id) end delete a from @t a where exists(select 1 from @t where a.id=pid)returnendgoselect ID from dbo.fn_GetChildID(0)/*ID-----------54(2 行受影响)*/第二种方法：if not object_id(tb) is null drop table tbIF OBJECT_ID(FUN_MU) IS NOT NULL DROP FUNCTION FUN_MUGoCREATE FUNCTION FUN_MU(@ID INT,@FID INT)RETURNS BITASBEGINDECLARE @TEMPID INTSELECT @TEMPID=PARENTID FROM TB WHERE ID=@IDWHILE @TEMPID@FID AND @TEMPID IS NOT NULLBEGINSELECT @TEMPID=PARENTID FROM TB WHERE ID=@TEMPIDENDIF @TEMPID=@FIDAND NOT EXISTS(SELECT 1 FROM TB WHERE PARENTID=@ID)RETURN 1RETURN 0ENDGOCreate table tb([id] int,[parentid] int,[name] varchar(10))Insert tbselect 1,0,null union allselect 2,0,null union allselect 3,1,null union allselect 4,3,null union allselect 5,2,nullGoSELECT * FROM TB WHERE DBO.FUN_MU(ID,0)=1/*4 3 NULL5 2 NULL*/我采用的方法是第二种：CREATE FUNCTION FUN_MU(@ID INT,@FID INT)RETURNS BITASBEGINDECLARE @TEMPID INTSELECT @TEMPID=PARENT_ID FROM JSH_INFO_LAW_PART WHERE ID=@IDWHILE @TEMPID@FID AND @TEMPID IS NOT NULLBEGINSELECT @TEMPID=PARENT_ID FROM JSH_INFO_LAW_PART WHERE ID=@TEMPIDENDIF @TEMPID=@FIDAND NOT EXISTS(SELECT 1 FROM JSH_INFO_LAW_PART WHERE PARENT_ID=@ID)RETURN 1RETURN 0ENDGO执行方法：SELECT * FROM 表名WHERE DBO.FUN_MU(ID,0)=1//ID为主键字段名//0为顶级PARENT_ID值//1为顶级ID主键字段名对应的值