I have following table:
ID ParentID
1 NULL
2 1
3 2
4 NULL
5 4
6 5
7 3
我想要找到特定子ID的第一个ID。 例如:ID=7,结果为1
ID=6,结果为4。
如何做到这一点?
I have following table:
ID ParentID
1 NULL
2 1
3 2
4 NULL
5 4
6 5
7 3
declare @data table(id int, parentid int)
insert into @data
select 1, null
union select 2,1
union select 3,2
union select 4, null
union select 5,4
union select 6,5
union select 7,3
;with recursiveTable as
(
select d.id, d.parentId, 0 as depth
from @data d
where d.id=6 -- Parameterize this
union all
select d.id, d.parentid, r.depth-1
from @data d
inner join recursiveTable r
on d.id = r.parentId
)
select top 1 id
from recursiveTable
order by depth
6
插入如上所述,返回的结果是 4
。将其更改为 7
,即可按照要求返回 1
。试试这个:
CREATE TABLE childpar(ID int,ParentID int)
INSERT INTO childpar
values(1,NULL),
(2, 1),
(3, 2),
(4, NULL),
(5, 4),
(6, 5),
(7, 3)
DECLARE @inpyID int
SET @inpyID=7
;WITH CTE1 as (
select * from childpar where id=@inpyID
union all
select c2.* from CTE1 c1 inner join childpar c2 on c1.ParentID = c2.ID
)
select top 1 id from CTE1 order by id asc