Id Parent
1 0
2 1
3 2
4 0
5 0
6 4
-----------------
比如有这样一个表 只给一个Id参数 要查出该Id为Parent其下的所以数据
如:id=1
需列出:
Id Parent
2 1
3 2
更明确的解释就是 id=3的parent是2 ->查id=2的parent是1->查id=1的parent是0 ->0为最顶级
--------------------
1 0
2 1
3 2
4 0
5 0
6 4
-----------------
比如有这样一个表 只给一个Id参数 要查出该Id为Parent其下的所以数据
如:id=1
需列出:
Id Parent
2 1
3 2
更明确的解释就是 id=3的parent是2 ->查id=2的parent是1->查id=1的parent是0 ->0为最顶级
--------------------
CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))
INSERT tb SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'
GO--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3),Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @ID,@Level
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level
FROM tb a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END
RETURN
END
GO--调用函数查询002及其所有子节点
SELECT a.*
FROM tb a,f_Cid('002') b
WHERE a.ID=b.ID
/*--结果
ID PID Name
------ ------- ----------
002 001 烟台市
004 002 招远市
--*/
insert into tb select 1 , 0
insert into tb select 2 , 1
insert into tb select 3 , 2
insert into tb select 4 , 0
insert into tb select 5 , 0
insert into tb select 6 , 4
go
CREATE FUNCTION f_Cid(@ID int)
RETURNS @t_Level TABLE(ID int,parent int,Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT id,parent,@level
FROM tb WHERE parent=@id
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,a.parent,@Level
FROM tb a,@t_Level b
WHERE a.parent=b.ID
AND b.Level=@Level-1
END
RETURN
END
GOselect id,parent from f_Cid(1) --查id=1drop table tb
drop function f_Cid/*
id parent
----------- -----------
2 1
3 2(2 row(s) affected)
*/
结帖方式:管理帖子->给分->输入密码->结帖谢谢.详细结帖方式地址:
http://topic.csdn.net/u/20080110/19/7cb462f1-cac6-4c28-848e-0a879f4fd642.html
drop table tb
if object_id('f_getchild') is not null
drop function f_getchild
go
create table tb(Id int,Parent int)
insert into tb
select 1, 0 union all
select 2, 1 union all
select 3, 2 union all
select 4, 0 union all
select 5, 0 union all
select 6, 4
select * from tbgo
create function f_getchild(@ID int)
returns @T table(id int,Parent int,lev int)
as
begin
declare @i int
set @i=1
insert @T select *,@i from tb where Parent=@ID
while @@rowcount>0
begin
set @i=@i+1
insert @T
select a.*,@i
from tb a join @T b on a.Parent=b.id and b.lev=@i-1
end
return
end
go--测试
select * from f_getchild(0)接分了