自引用/ CTE /为一个映射实体查找跟踪

Question

啊啊。

我正处于大脑冻结状态。

下面我有一个自我引用表。我有个CTE可以自己加入并得到信息。

我想得到的是一个人的“完整的层次结构路径”。

请注意，我没有预先确定的级别数。(下面是我的例子，请带这个人去定位关系，这代表了我的数据结构，这不是我真正的数据结构)

请注意，@PersonUUID_Me映射到“城镇”级别，而@PersonUUID_Nobody映射到街道级别。换句话说，这两个不同的人有两个不同的排计数，他们的层次结构。因此，我不能指望“深度”是3(或任何预先确定的)。

我现在得到的只是Person_Me的“底部”行。

AAAAAAAA-6666-6666-6666-666666666666    Strasburg   AAAAAAAA-7777-7777-7777-777777777777    3

我想要的是：

99999999-9999-9999-9999-999999999999    USA (NULL)  0
AAAAAAAA-8888-8888-8888-888888888888    Virginia    99999999-9999-9999-9999-999999999999    1
AAAAAAAA-7777-7777-7777-777777777777    Shenandoah  AAAAAAAA-8888-8888-8888-888888888888    2
AAAAAAAA-6666-6666-6666-666666666666    Strasburg   AAAAAAAA-7777-7777-7777-777777777777    3

而且(未显示)，如果我为Person_Nobody运行它，我将得到：

99999999-9999-9999-9999-999999999999    USA (NULL)  0
BBBBBBBB-8888-8888-8888-888888888888    North Carolina  99999999-9999-9999-9999-999999999999    1
BBBBBBBB-7777-7777-7777-777777777777    Wake    BBBBBBBB-8888-8888-8888-888888888888    2
BBBBBBBB-6666-6666-6666-666666666666    Raleigh BBBBBBBB-7777-7777-7777-777777777777    3
BBBBBBBB-5555-5555-5555-555555555555    Main St BBBBBBBB-6666-6666-6666-666666666666    4

Gaaaa我错过了一些基本的东西，我知道，但我脑子都快冻僵了。

再一次，我试图得到一个人的完整层次线索，这个人被映射到“最底层的”层次元素.但是该元素的“深度”不是预先确定的。在这个例子中，它将是“我将映射我最了解的层次值，但我可能不知道最详细的信息(街道)，我可能只知道你的州或城镇。

-- START TSQL

SET NOCOUNT ON

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Person]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
BEGIN 
DROP TABLE [dbo].[Person] 
END 



IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[BreadCrumbTrail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
BEGIN 
DROP TABLE [dbo].[BreadCrumbTrail] 
END 
GO



CREATE TABLE [dbo].[BreadCrumbTrail] ( 
      [BreadCrumbTrailSurrogateUUID] [uniqueidentifier] NOT NULL
    , [BreadCrumbTrailName] varchar(64) NOT NULL
    , [CreateDate] [datetime] NOT NULL
    , ParentBreadCrumbTrailSurrogateUUID [uniqueidentifier] NULL

)


GO

ALTER TABLE dbo.BreadCrumbTrail ADD CONSTRAINT PK_BreadCrumbTrail PRIMARY KEY NONCLUSTERED (BreadCrumbTrailSurrogateUUID) 
GO

ALTER TABLE [dbo].[BreadCrumbTrail] ADD CONSTRAINT FK_BreadCrumbTrail_SelfRef FOREIGN KEY ([ParentBreadCrumbTrailSurrogateUUID]) REFERENCES dbo.[BreadCrumbTrail] (BreadCrumbTrailSurrogateUUID) 
GO


IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Person]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
BEGIN 
DROP TABLE [dbo].[Person] 
END 


CREATE TABLE [dbo].[Person] ( 

    [PersonUUID] [uniqueidentifier] NOT NULL,
    [BreadCrumbTrailSurrogateUUID]  [uniqueidentifier] NOT NULL,
    [LastName] [varchar](64) NOT NULL,
    [FirstName] [varchar](64) NOT NULL,
    [CreateDate] [datetime] NOT NULL
    )

GO

ALTER TABLE dbo.Person ADD CONSTRAINT PK_Person PRIMARY KEY NONCLUSTERED (PersonUUID) 
GO

ALTER TABLE [dbo].Person ADD CONSTRAINT FK_Person_To_BreadCrumb FOREIGN KEY ([BreadCrumbTrailSurrogateUUID]) REFERENCES dbo.[BreadCrumbTrail] (BreadCrumbTrailSurrogateUUID) 
GO




declare @PersonUUID_Me [uniqueidentifier]
select @PersonUUID_Me = '11111111-1111-1111-1111-111111111111'

declare @PersonUUID_Nobody [uniqueidentifier]
select @PersonUUID_Nobody = '22222222-2222-2222-2222-222222222222'

declare @BreadCrumbTrailSurrogateUUID_Country [uniqueidentifier]
select @BreadCrumbTrailSurrogateUUID_Country = '99999999-9999-9999-9999-999999999999'
declare @BreadCrumbTrailSurrogateUUID_State1 [uniqueidentifier]
select @BreadCrumbTrailSurrogateUUID_State1 = 'AAAAAAAA-8888-8888-8888-888888888888'
declare @BreadCrumbTrailSurrogateUUID_County1 [uniqueidentifier]
select @BreadCrumbTrailSurrogateUUID_County1 = 'AAAAAAAA-7777-7777-7777-777777777777'
declare @BreadCrumbTrailSurrogateUUID_Town1 [uniqueidentifier]
select @BreadCrumbTrailSurrogateUUID_Town1 = 'AAAAAAAA-6666-6666-6666-666666666666'

declare @BreadCrumbTrailSurrogateUUID_State2 [uniqueidentifier]
select @BreadCrumbTrailSurrogateUUID_State2 = 'BBBBBBBB-8888-8888-8888-888888888888'
declare @BreadCrumbTrailSurrogateUUID_County2 [uniqueidentifier]
select @BreadCrumbTrailSurrogateUUID_County2 = 'BBBBBBBB-7777-7777-7777-777777777777'
declare @BreadCrumbTrailSurrogateUUID_Town2 [uniqueidentifier]
select @BreadCrumbTrailSurrogateUUID_Town2 = 'BBBBBBBB-6666-6666-6666-666666666666'

declare @BreadCrumbTrailSurrogateUUID_Street2 [uniqueidentifier]
select @BreadCrumbTrailSurrogateUUID_Street2 = 'BBBBBBBB-5555-5555-5555-555555555555'



INSERT INTO [dbo].[BreadCrumbTrail] ( 
      [BreadCrumbTrailSurrogateUUID] 
    , [BreadCrumbTrailName]
    , [CreateDate] 
    , ParentBreadCrumbTrailSurrogateUUID
     )
Select @BreadCrumbTrailSurrogateUUID_Country , 'USA' , CURRENT_TIMESTAMP , NULL
UNION ALL Select @BreadCrumbTrailSurrogateUUID_State1 , 'Virginia' , CURRENT_TIMESTAMP , @BreadCrumbTrailSurrogateUUID_Country
UNION ALL Select @BreadCrumbTrailSurrogateUUID_County1 , 'Shenandoah' , CURRENT_TIMESTAMP , @BreadCrumbTrailSurrogateUUID_State1
UNION ALL Select @BreadCrumbTrailSurrogateUUID_Town1, 'Strasburg' , CURRENT_TIMESTAMP , @BreadCrumbTrailSurrogateUUID_County1
UNION ALL Select @BreadCrumbTrailSurrogateUUID_State2 , 'North Carolina' , CURRENT_TIMESTAMP , @BreadCrumbTrailSurrogateUUID_Country
UNION ALL Select @BreadCrumbTrailSurrogateUUID_County2 , 'Wake' , CURRENT_TIMESTAMP , @BreadCrumbTrailSurrogateUUID_State2
UNION ALL Select @BreadCrumbTrailSurrogateUUID_Town2, 'Raleigh' , CURRENT_TIMESTAMP , @BreadCrumbTrailSurrogateUUID_County2
UNION ALL Select @BreadCrumbTrailSurrogateUUID_Street2, 'Main St' , CURRENT_TIMESTAMP , @BreadCrumbTrailSurrogateUUID_Town2


INSERT INTO [dbo].[Person] (    [PersonUUID] , [BreadCrumbTrailSurrogateUUID] , [LastName] , [FirstName] , [CreateDate] )
            Select @PersonUUID_Me , @BreadCrumbTrailSurrogateUUID_Town1 ,  'Coder', 'Granada' , CURRENT_TIMESTAMP
UNION ALL   Select @PersonUUID_Nobody , @BreadCrumbTrailSurrogateUUID_Street2 ,  'Body', 'No' , CURRENT_TIMESTAMP




/*
Select  [PersonUUID] , [BreadCrumbTrailSurrogateUUID] , [LastName] , [FirstName] , [CreateDate] 
from [dbo].[Person] per
    where per.PersonUUID = @PersonUUID_Me
*/




 ;WITH cteLocation (BreadCrumbTrailSurrogateUUID, [BreadCrumbTrailName] , ParentBreadCrumbTrailSurrogateUUID , Depth)
 AS
 (
    Select bc1.BreadCrumbTrailSurrogateUUID, bc1.[BreadCrumbTrailName], bc1.ParentBreadCrumbTrailSurrogateUUID , 0 as Depth From dbo.BreadCrumbTrail bc1 where bc1.ParentBreadCrumbTrailSurrogateUUID IS NULL
    Union ALL
    Select bc2.BreadCrumbTrailSurrogateUUID, bc2.[BreadCrumbTrailName] , bc2.ParentBreadCrumbTrailSurrogateUUID , Depth + 1 
    From dbo.BreadCrumbTrail bc2 
    inner join cteLocation cteL on bc2.ParentBreadCrumbTrailSurrogateUUID = cteL.BreadCrumbTrailSurrogateUUID
 )

 Select cteLoc.BreadCrumbTrailSurrogateUUID, [BreadCrumbTrailName] , ParentBreadCrumbTrailSurrogateUUID , Depth 
 from cteLocation cteLoc
 join [dbo].[Person] per on per.BreadCrumbTrailSurrogateUUID = cteLoc.BreadCrumbTrailSurrogateUUID
    where per.PersonUUID = @PersonUUID_Me

Answer 1

您需要另一个CTE才能从层次结构中的人员中回溯：

 ;WITH cteLocation (BreadCrumbTrailSurrogateUUID, [BreadCrumbTrailName] , ParentBreadCrumbTrailSurrogateUUID , Depth)
 AS
 (
    Select bc1.BreadCrumbTrailSurrogateUUID, bc1.[BreadCrumbTrailName], bc1.ParentBreadCrumbTrailSurrogateUUID , 0 as Depth From dbo.BreadCrumbTrail bc1 where bc1.ParentBreadCrumbTrailSurrogateUUID IS NULL
    Union ALL
    Select bc2.BreadCrumbTrailSurrogateUUID, bc2.[BreadCrumbTrailName] , bc2.ParentBreadCrumbTrailSurrogateUUID , Depth + 1 
    From dbo.BreadCrumbTrail bc2 
    inner join cteLocation cteL on bc2.ParentBreadCrumbTrailSurrogateUUID = cteL.BreadCrumbTrailSurrogateUUID
 )
,ctePerson as
(
    Select cteLoc.BreadCrumbTrailSurrogateUUID, [BreadCrumbTrailName] , ParentBreadCrumbTrailSurrogateUUID , Depth 
    from cteLocation cteLoc
    join [dbo].[Person] per on per.BreadCrumbTrailSurrogateUUID = cteLoc.BreadCrumbTrailSurrogateUUID
    where per.PersonUUID = @PersonUUID_Nobody

    union all

    Select c.BreadCrumbTrailSurrogateUUID, c.[BreadCrumbTrailName], c.ParentBreadCrumbTrailSurrogateUUID, c.Depth 
    from cteLocation c
    inner join ctePerson p on c.BreadCrumbTrailSurrogateUUID = p.ParentBreadCrumbTrailSurrogateUUID
)
select *
from ctePerson
order by Depth