求一个遍历递归的SQL语句

[dstm0001]

原始数据：
ID NAME ID_PARENT
1 A 3
2 B NULL
3 C 5
4 D NULL
5 E 2
6 F 1

ID_PARENT是父节点，需要找到每条记录对应的最上层父节点

需要结果
ID NAME ID_PARENT ID_GROUP
1 A 3 2
2 B NULL 2
3 C 5 2
4 D NULL 4
5 E 2 2
6 F 1 2

麻烦哪位朋友指导一下，如何高效得到结果

[专注or全面]

操，脑子成一锅浆糊了

create table t
(
	id int,
	name varchar(100),
	id_parent  int
)

insert into t values (1,'A',3)
insert into t values (2,'B',null)
insert into t values (3,'C',5)
insert into t values (4,'D',null)
insert into t values (5,'E',2)
insert into t values (6,'F',1)




with f as 
(
select id as constid,  id ,name,id_parent ,0 as lev from t  
union all
select b.constid, a.id ,a.name,a.id_parent,lev+1  from t as a inner join f as b  on a.id=b.id_parent
)
select f1.id ,f1.name,f1.id_parent,f2.id as id_group from f  f1,f f2 where f1.constid=f2.constid and f1.lev=0 and f2.id_parent is null;



id          name                                                                                                 id_parent   id_group
----------- ---------------------------------------------------------------------------------------------------- ----------- -----------
1           A                                                                                                    3           2
2           B                                                                                                    NULL        2
3           C                                                                                                    5           2
4           D                                                                                                    NULL        4
5           E                                                                                                    2           2
6           F                                                                                                    1           2

(6 行受影响)

[习惯性蹭分]

用函数，效率有待测试。

create table test(ID int,NAME varchar(10),ID_PARENT int)
insert into test
select 1,           'A',                  3  union all
select 2,           'B',                  NULL union all
select 3,           'C',                  5 union all
select 4,           'D',                  NULL union all
select 5,           'E',                  2 union all
select 6,           'F',                  1

go
create function fn_getUP(@id int)
returns int
as begin
 declare @reID int
 ;with sel as
 (select id,id_parent,0 as lev from test where id=@id
  union all
  select a.id,a.id_parent,b.lev+1  from test a
  join sel b on a.id=b.id_parent)
  select top (1) @reID=id from sel order by lev desc
  return @reID
end
go
select id,name,id_parent,dbo.fn_getUP(id) as groupID from test

[还在加载中灬]

;WITH CTE AS(
	SELECT ID,[NAME],ID_PARENT,ID AS [ID_GROUP]
	FROM TB
	WHERE ID_PARENT IS NULL
	UNION ALL
	SELECT T1.ID,T1.[NAME],T1.ID_PARENT,T2.[ID_GROUP]
	FROM TB T1
		JOIN CTE T2 ON T1.ID_PARENT=T2.ID
)
SELECT * FROM CTE
ORDER BY ID

[HelloWordGirl]

--递归查询 create table #Temp_1 ( id int, name varchar(100), id_parent int ) insert into #Temp_1 values (1,'A',3) insert into #Temp_1 values (2,'B',null) insert into #Temp_1 values (3,'C',5) insert into #Temp_1 values (4,'D',null) insert into #Temp_1 values (5,'E',2) insert into #Temp_1 values (6,'F',1) ;WITH cet AS ( SELECT t.id AS NID,t.id,t.name,t.id_parent,0 AS Leve FROM #Temp_1 t --先取以0 作为基数 UNION ALL SELECT c.NID,t.id,t.name,t.id_parent,c.Leve+1 FROM #Temp_1 t JOIN cet c ON t.id=c.id_parent --等级加1 ) SELECT * FROM cet c1 ,cet c2 WHERE c1.NID=c2.NID AND c1.Leve=0 AND c2.id_parent IS NULL

[hery2002]

CTE不解释

[jakwos]

把null去掉 用0代替 不然影响查询效率 递归可以写个函数 举个例子给你参照一下你就知道搞了: create function [dbo].[fn_GetPgid] ( @Gid int ) returns table as return( with myT2 as( select TabUserGroup.UGid as 'SGid' from TabUserGroup Where ugid = @Gid union all select TabUserGroup.UGParentId as 'SGid' from myT2 inner join TabUserGroup on myT2.SGid = TabUserGroup.UGid ) select * from myT2 where SGid <> 0 and SGid in (select UGid from TabUserGroup a where a.UGParentId = 0) ) 这段代码中 TabUserGroup.UGid相当于你的ID列,UGParentId相当于你的 ID_PARENT列 该函数用于查询输入任一一个id查询该记录的最高级父id是什么

[骑着蜗牛去爬山]

with tb as
(
select ID=1,NAME='A',ID_PARENT=3 union all
select 2,'B',NULL union all
select 3,'C',5 union all
select 4,'D',NULL union all
select 5,'E',2 union all
select 6,'F',1
),
cte as(
select *, ID_GROUP=ID from tb where isnull(ID_PARENT,'')=''
union all
select tb.*,cte.ID_GROUP from cte,tb where cte.ID=tb.ID_PARENT
)
select * from cte order by id