原始数据：
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
麻烦哪位朋友指导一下，如何高效得到结果
------解决思路----------------------
操，脑子成一锅浆糊了
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
------解决思路----------------------
--递归查询
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
------解决思路----------------------
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
------解决思路----------------------
把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是什么