Id InvID InvDate OriginalInvID FilterDesc
1 1 2017-03-01 00:00:00.000 0 First Inv Created
2 2 2017-03-06 00:00:00.000 1 Link ID
3 3 2017-03-01 00:00:00.000 0 First Inv Created
4 4 2017-03-03 00:00:00.000 3 Link ID
5 5 2017-03-06 00:00:00.000 4 Second Inv Created on top of MapID
6 6 2017-03-01 00:00:00.000 0 First Inv Created
7 7 2017-03-03 00:00:00.000 6 Link ID
8 8 2017-03-05 00:00:00.000 7 Second In Created on top of Map Id
9 9 2017-03-06 00:00:00.000 8 Second Inv Created on top of Map Id
hi,在上表中,我必须通过invId
,并且我需要在OriginalInvID
列中使用MAP Id
的结果。
例如:
- 如果我通过1,那么我得到1&2
invId
- 如果我通过4,那么我得到3,4,5
- 如果我通过了8,那么我会得到6,7,8,9这样的方式
如果我了解您想要的东西,我认为您想要这样的查询:
declare @InvId int = 8;
with cte(Id, InvID, InvDate, OriginalInvID) as (
select *
from yourTable
where OriginalInvID = @InvId
union all
select t.*
from yourTable t join cte on t.InvId = cte.OriginalInvID
)
select *
from cte
order by Id;
您可以使用一个way循环获取父亲和孩子的节点将它们存储在临时表中:
declare @myInvId int
declare @father_counter int
declare @children_counter int
declare @temp table (Id int, InvID int, InvDate datetime, OriginalInvID int, FilterDesc nvarchar(100))
declare @fathers table(id int, status int)
declare @children table(id int, status int)
insert into @temp
select 1, 1, '2017-03-01 00:00:00.000', 0, 'First Inv Created'
union all select 2, 2, '2017-03-06 00:00:00.000', 1, 'Link ID'
union all select 3, 3, '2017-03-01 00:00:00.000', 0, 'First Inv Created'
union all select 4, 4, '2017-03-03 00:00:00.000', 3, 'Link ID'
union all select 5, 5, '2017-03-06 00:00:00.000', 4, 'Second Inv Created on top of MapID'
union all select 6, 6, '2017-03-01 00:00:00.000', 0, 'First Inv Created'
union all select 7, 7, '2017-03-03 00:00:00.000', 6, 'Link ID'
union all select 8, 8, '2017-03-05 00:00:00.000', 7, 'Second In Created on top of Map Id'
union all select 9, 9, '2017-03-06 00:00:00.000', 8, 'Second Inv Created on top of Map Id'
--set input parameter
set @myInvId = 8
--init counters
set @father_counter = 1
set @children_counter = 1
--insert starting node
insert into @fathers select InvID,0 from @temp where OriginalInvID = @myInvId
insert into @children select OriginalInvID,0 from @temp where InvID = @myInvId
--loop on table
while (@father_counter + @children_counter) > 0
begin
--fetch father nodes
insert into @fathers select InvID, 1 from @temp where OriginalInvID in (select id from @fathers where status=0)
--update fathers' status
update @fathers set status = 2 where status = 0
update @fathers set status = 0 where status = 1
--fetch children nodes
insert into @children select OriginalInvID, 1 from @temp where InvID in (select id from @children where status=0)
--update children's status
update @children set status = 2 where status = 0
update @children set status = 0 where status = 1
--update counters
select @father_counter = count(*) from @fathers where status = 0
select @children_counter = count(*) from @children where status = 0
end
select @myInvId
union
select id from @fathers
union
select c.id from @children c inner join @temp t on c.id = t.InvID