主页 / dba / 问题 / 78779
Accepted
Muflix
Asked: 2014-10-10 06:39:11 +0800 CST

从层次结构中选择孩子 (/a/b/c/d/)

  • 772

我必须从我没有创建的数据库中进行选择，我遇到了这个问题：

示例架构：

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

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

我需要有这样的结果

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

我试过像这样加入表

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

但它因错误而崩溃

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

如何在此 hierarchyPath“数据类型”中获取子项？例如，我可以从一支球队中选择球员

Like '/42/%'

但是如何为所有团队做到这一点？！

sql-server

1 个回答

  1. Best Answer

    你需要“走树”。这可以使用递归 CTE 来完成，如下所示：

    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

相关问题