给定一个ID的开头,中间或结尾,我想查找它们之间链接的所有行。
有了这个,我递归地发现了子元素
declare @T table(
Id int primary key,
Name nvarchar(255) not null,
ParentId int)
insert into @T values
(1, 'A', NULL),
(2, 'B', 1),
(3, 'C', 2),
(4, 'D', NULL),
(5, 'E', 1)
declare @Id int = 2
;with cte as
(
select T.*
from @T as T
where T.Id = @Id
union all
select T.*
from @T as T
inner join cte as C
on T.ParentId = C.Id
)
select *
from cte
如果@Id等于2,我将得到2和3。但是我也想检索父级,在这种情况下为1。
我想得到这个:
如果id = 1
1 A NULL
2 B 1
3 C 2
5 E 1
如果id = 2
1 A NULL
2 B 1
3 C 2
5 E 1
如果id = 3
1 A NULL
2 B 1
3 C 2
5 E 1
如果id = 4
4 D NULL
如果id = 5
1 A NULL
2 B 1
3 C 2
5 E 1
我怎样才能解决这个问题?
好的!我有你:
我创建了一个单独的家庭进行测试。
您需要做的第一件事是创建一个查询,以查找所选ID的最老祖先。这是下面的第一个CTE。然后您指定该名称,我称它为@Eve
Eve,是Eve的所有子代。
create table UserType (
Id int,
name nvarchar(255),
parentid int)
insert into UserType values
(1, 'A', NULL),
(2, 'B', 1),
(3, 'C', 2),
(4, 'D', NULL),
(5, 'E', 1),
(6, 'F', NULL),
(7, 'G', 6),
(8, 'H', 7);
DECLARE @eve BIGINT; --ancestor
DECLARE @id BIGINT;
SET @id = 8; --This is where you choose
WITH tblParent AS --CTE for oldest ancestor
(
SELECT *
FROM UserType WHERE Id = @id
UNION ALL
SELECT UserType.*
FROM UserType JOIN tblParent ON UserType.Id = tblParent.ParentId
)
select
@eve = (select top 1 id from tblParent order by id);
WITH tblChild AS --CTE for all @eve and all children
(
SELECT *
FROM UserType WHERE id = @eve
UNION ALL
SELECT UserType.* FROM UserType JOIN tblChild ON UserType.ParentId = tblChild.Id
)
SELECT * FROM tblChild
order by id
OPTION(MAXRECURSION 32767)
对CodeProject的支持非常有用。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句