Início / dba / Perguntas / 78779
Accepted
Muflix
Asked: 2014-10-10 06:39:11 +0800 CST

Selecionar filhos da hierarquia (/a/b/c/d/)

  • 772

Tenho que selecionar do banco de dados que não criei e estou com este problema:

esquema de exemplo:

Unit
----
id
HierarchyPath (example: '/42/48/63/84/')
UnitName (example1: "John" example2: "Team1")
typeId

UnitType
--------
id
Name (example1: "Name", example2: "TeamName")

eu preciso ter resultado como

Team1, John
Team1, Joe
Team1, Johana
Team2, Bob
Team2, Bobek
Team2, BobaFett

Eu tentei juntar tabelas como

UnitName_Teams.HierarchyPath = left(4,UnitName_Players.HierarchyPath)

mas trava no erro

'Conversion failed when converting the varchar value '/42/48/63/' to data type int.'

Como posso obter filhos neste 'tipo de dados' hierárquico? Posso selecionar jogadores de um time com, por exemplo,

Like '/42/%'

Mas como fazer para todas as Equipes?!

sql-server

1 respostas

  1. Best Answer

    Você precisa 'andar na árvore'. Isso pode ser feito usando um CTE recursivo, algo assim:

    USE tempdb
GO

CREATE TABLE dbo.Unit (
    id INT IDENTITY PRIMARY KEY,
    unitName        VARCHAR(20),
    hierarchyPath   hierarchyId UNIQUE,
    typeId          INT
    )
GO

CREATE TABLE dbo.UnitType (
    id      INT PRIMARY KEY,
    name    VARCHAR(20)
    )
GO

INSERT INTO dbo.Unit ( hierarchyPath, unitName, typeId )
VALUES 
    ( '/42/', 'Team1', 1 ),
    ( '/42/1/', 'John', 2 ),
    ( '/42/2/', 'Joe', 2 ),
    ( '/42/3/', 'Johana', 2 ),

    ( '/43/', 'Team2', 1 ),
    ( '/43/1/', 'Bob', 2 ),
    ( '/43/2/', 'Bobek', 2 ),
    ( '/43/3/', 'wBobaFett', 2 )

INSERT INTO dbo.UnitType ( Id, name )
VALUES ( 1, 'Team' ), ( 2, 'Name' )
GO

--SELECT *, hierarchyPath.ToString() hierarchyPath_string
--FROM dbo.Unit

;WITH cte AS
(
SELECT 1 xlevel, Id, hierarchyPath, unitName AS teamName, CAST( '' AS VARCHAR(20) ) unitName
FROM dbo.Unit
WHERE typeId = ( SELECT Id FROM dbo.UnitType WHERE name = 'Team' )

UNION ALL

SELECT xlevel + 1, u.Id, u.hierarchyPath, c.teamName, u.unitName
FROM cte c
    INNER JOIN dbo.Unit u ON u.hierarchyPath.GetAncestor (1) = c.hierarchyPath

)
SELECT teamName, unitName
FROM cte
WHERE xlevel > 1
ORDER BY teamName, unitName
GO

DROP TABLE dbo.Unit
DROP TABLE dbo.UnitType
GO

    HTH

    • 1

relate perguntas