Lektion 2: Skapa och hantera data i en hierarkisk tabell

2025-02-04

gäller för:SQL Server Azure SQL Database Azure SQL Managed Instance SQL-databas i Microsoft Fabric

I lektion 1 ändrade du en befintlig tabell för att använda hierarchyid datatyp och fyllde i kolumnen hierarchyid med representationen av befintliga data. I den här lektionen börjar du med en ny tabell och infogar data med hjälp av hierarkiska metoder. Sedan frågar och manipulerar du data med hjälp av hierarkiska metoder.

Förutsättningar

För att slutföra den här självstudien behöver du SQL Server Management Studio, åtkomst till en server som kör SQL Server och en AdventureWorks2022 databas.

Installera SQL Server Management Studio (SSMS).
Installera SQL Server 2022 Developer Edition.
Ladda ned AdventureWorks-exempeldatabasen.

Anvisningar för att återställa databaser i SSMS finns här: Återställa en databassäkerhetskopia med SSMS-.

Skapa en tabell med datatypen hierarchyid

I följande exempel skapas en tabell med namnet EmployeeOrg, som innehåller medarbetardata tillsammans med deras rapporteringshierarki. Exemplet skapar tabellen i databasen AdventureWorks2022, men det är valfritt. För att hålla exemplet enkelt innehåller den här tabellen bara fem kolumner:

OrgNode är en hierarchyid kolumn som lagrar den hierarkiska relationen.
OrgLevel är en beräknad kolumn baserat på den OrgNode kolumn som lagrar varje nodnivå i hierarkin. Det används för ett bredd-förstindex.
EmployeeID innehåller det typiska nummer för medarbetaridentifiering som används för program som löneuppgifter. I ny programutveckling kan program använda kolumnen OrgNode och den här separata EmployeeID kolumnen behövs inte.
EmpName innehåller namnet på medarbetaren.
Title innehåller den anställdes titel.

Skapa tabellen EmployeeOrg

I ett frågeredigerarefönster kör du följande kod för att skapa tabellen EmployeeOrg. Om du anger OrgNode-kolumnen som primärnyckel med ett klustrat index skapas ett djup-först-index.

USE AdventureWorks2022;
GO

IF OBJECT_ID('HumanResources.EmployeeOrg') IS NOT NULL
    DROP TABLE HumanResources.EmployeeOrg

CREATE TABLE HumanResources.EmployeeOrg (
    OrgNode HIERARCHYID PRIMARY KEY CLUSTERED,
    OrgLevel AS OrgNode.GetLevel(),
    EmployeeID INT UNIQUE NOT NULL,
    EmpName VARCHAR(20) NOT NULL,
    Title VARCHAR(20) NULL
);
GO

Kör följande kod för att skapa ett sammansatt index för kolumnerna OrgLevel och OrgNode för att ge stöd för effektiva bredd-första sökningar:
```
CREATE UNIQUE INDEX EmployeeOrgNc1
ON HumanResources.EmployeeOrg(OrgLevel, OrgNode);
GO
```

Tabellen är nu redo för data. Nästa uppgift fyller i tabellen med hjälp av hierarkiska metoder.

Fylla i en hierarkisk tabell med hjälp av hierarkiska metoder

AdventureWorks2022 har åtta anställda som arbetar på marknadsföringsavdelningen. Medarbetarhierarkin ser ut så här:

David, EmployeeID 6, är marknadschef. Tre marknadsföringsspecialister rapporterar till David:

Sariya, EmployeeID 46
John, EmployeeID 271
Jill, EmployeeID 119

Marketing Assistant Wanida (EmployeeID 269), rapporterar till Sariyaoch Marketing Assistant Mary (EmployeeID 272), rapporterar till John.

Infoga roten i hierarkiträdet

I följande exempel infogas David marknadschef i tabellen vid roten av hierarkin. Kolumnen OrdLevel är en beräknad kolumn. Därför är det inte en del av INSERT-uttrycket. Den här första posten använder metoden GetRoot (databasmotor) för att fylla i den första posten som rot i hierarkin.
```
INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)
VALUES (hierarchyid::GetRoot(), 6, 'David', 'Marketing Manager');
GO
```

Kör följande kod för att undersöka den första raden i tabellen:

SELECT OrgNode.ToString() AS Text_OrgNode,
OrgNode, OrgLevel, EmployeeID, EmpName, Title
FROM HumanResources.EmployeeOrg;

Här är resultatet.

Text_OrgNode OrgNode OrgLevel EmployeeID EmpName Title
------------ ------- -------- ---------- ------- -----------------
/            Ox      0        6          David   Marketing Manager

Precis som i föregående lektion använder vi metoden ToString() för att konvertera hierarchyid datatyp till ett format som är enklare att förstå.

Infoga en underordnad medarbetare

Sariya rapporterar till David. Om du vill infoga Sariya's nod måste du skapa ett lämpligt OrgNode värde för datatypen hierarchyid. Följande kod skapar en variabel av datatyp hierarchyid och fyller den med rotvärdet OrgNode i tabellen. Använder sedan variabeln med metoden GetDescendant (Database Engine) för att infoga en rad som är en underordnad nod. GetDescendant tar två argument. Granska följande alternativ för argumentvärdena:
- Om överordnad är NULLreturnerar GetDescendantNULL.
- Om överordnad inte är NULLoch både child1 och child2 är NULLreturnerar GetDescendant ett barn till överordnad.
- Om föräldern och child1 inte är NULLoch child2 är NULL, returnerar GetDescendant ett barn till föräldern som är större än child1.
- Om förälder och child2 inte är NULL och child1 är NULL, returnerar GetDescendant ett barn till förälder som är mindre än child2.
- Om förälder, child1och child2 alla inte är NULL, returnerar GetDescendant ett barn till förälder som är större än child1 och mindre än child2.
I följande kod används (NULL, NULL)-argumenten för roten som är överordnad, eftersom det ännu inte finns några rader i tabellen utom roten. Kör följande kod för att infoga Sariya:
```
DECLARE @Manager HIERARCHYID
SELECT @Manager = HIERARCHYID::GetRoot()
FROM HumanResources.EmployeeOrg;

INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)
VALUES
(@Manager.GetDescendant(NULL, NULL), 46, 'Sariya', 'Marketing Specialist');
```

Upprepa frågan från den första proceduren för att köra en fråga mot tabellen och se hur posterna i tabellen visas.

SELECT OrgNode.ToString() AS Text_OrgNode,
OrgNode, OrgLevel, EmployeeID, EmpName, Title
FROM HumanResources.EmployeeOrg;

Här är resultatet.

Text_OrgNode OrgNode OrgLevel EmployeeID EmpName Title
------------ ------- -------- ---------- ------- -----------------
/            Ox      0        6          David   Marketing Manager
/1/          0x58    1        46         Sariya  Marketing Specialist

Skapa en procedur för att ange nya noder

För att förenkla inmatningen av data skapar du följande lagrade procedur för att lägga till anställda i tabellen EmployeeOrg. Proceduren accepterar indatavärden om medarbetaren som läggs till. Detta inkluderar EmployeeID för den nya medarbetarens chef, den nya medarbetarens EmployeeID nummer och deras förnamn och titel. Proceduren använder GetDescendant() och även metoden GetAncestor (Databasmotor). Kör följande kod för att skapa proceduren:

CREATE PROCEDURE AddEmp (
    @mgrid INT,
    @empid INT,
    @e_name VARCHAR(20),
    @title VARCHAR(20)
)
AS
BEGIN
    DECLARE @mOrgNode HIERARCHYID, @lc HIERARCHYID;

    SELECT @mOrgNode = OrgNode
    FROM HumanResources.EmployeeOrg
    WHERE EmployeeID = @mgrid;

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

    BEGIN TRANSACTION;

    SELECT @lc = max(OrgNode)
    FROM HumanResources.EmployeeOrg
    WHERE OrgNode.GetAncestor(1) = @mOrgNode;

    INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)
    VALUES (@mOrgNode.GetDescendant(@lc, NULL), @empid, @e_name, @title);

    COMMIT;
END;
GO

I följande exempel läggs de återstående fyra anställda som rapporterar direkt eller indirekt till David.

EXEC AddEmp 6, 271, 'John', 'Marketing Specialist';
EXEC AddEmp 6, 119, 'Jill', 'Marketing Specialist';
EXEC AddEmp 46, 269, 'Wanida', 'Marketing Assistant';
EXEC AddEmp 271, 272, 'Mary', 'Marketing Assistant';

Kör återigen följande fråga och granska raderna i tabellen EmployeeOrg:

SELECT OrgNode.ToString() AS Text_OrgNode,
OrgNode, OrgLevel, EmployeeID, EmpName, Title
FROM HumanResources.EmployeeOrg;
GO

Här är resultatet.

Text_OrgNode OrgNode OrgLevel EmployeeID EmpName Title
------------ ------- -------- ---------- ------- --------------------
/            Ox      0        6          David   Marketing Manager
/1/          0x58    1        46         Sariya  Marketing Specialist
/1/1/        0x5AC0  2        269        Wanida  Marketing Assistant
/2/          0x68    1        271        John    Marketing Specialist
/2/1/        0x6AC0  2        272        Mary    Marketing Assistant
/3/          0x78    1        119        Jill    Marketing Specialist

Tabellen är nu helt ifylld med marknadsföringsorganisationen.

Köra frågor mot en hierarkisk tabell med hjälp av hierarkimetoder

Nu när tabellen HumanResources.EmployeeOrg är helt ifylld visar den här uppgiften hur du kör frågor mot hierarkin med hjälp av några av de hierarkiska metoderna.

Hitta underordnade noder

Sariya har en underordnad anställd. Kör följande fråga som använder metoden IsDescendantOf (Databasmotor) för att fråga efter Sariyas underordnade:
```
DECLARE @CurrentEmployee HIERARCHYID

SELECT @CurrentEmployee = OrgNode
FROM HumanResources.EmployeeOrg
WHERE EmployeeID = 46;

SELECT *
FROM HumanResources.EmployeeOrg
WHERE OrgNode.IsDescendantOf(@CurrentEmployee ) = 1;
```
Resultatet visar både Sariya och Wanida. Sariya är listad eftersom det värdet är underordnat på 0-nivå. Wanida är underordnad på 1-nivån.
Du kan också fråga efter den här informationen med hjälp av metoden GetAncestor (Databasmotor). GetAncestor tar ett argument för den nivå som du försöker returnera. Eftersom Wanida är en nivå under Sariya använder du GetAncestor(1) enligt följande kod:
```
DECLARE @CurrentEmployee HIERARCHYID

SELECT @CurrentEmployee = OrgNode
FROM HumanResources.EmployeeOrg
WHERE EmployeeID = 46;

SELECT OrgNode.ToString() AS Text_OrgNode, *
FROM HumanResources.EmployeeOrg
WHERE OrgNode.GetAncestor(1) = @CurrentEmployee
```
Den här gången visar resultatet endast Wanida.

Ändra nu @CurrentEmployee till David (EmployeeID 6) och nivån till 2. Kör följande för att också returnera Wanida:

DECLARE @CurrentEmployee HIERARCHYID

SELECT @CurrentEmployee = OrgNode
FROM HumanResources.EmployeeOrg
WHERE EmployeeID = 6;

SELECT OrgNode.ToString() AS Text_OrgNode, *
FROM HumanResources.EmployeeOrg
WHERE OrgNode.GetAncestor(2) = @CurrentEmployee

Den här gången får du också Mary som också rapporterar till David, två nivåer ner.

Använda GetRoot och GetLevel

När hierarkin växer sig större är det svårare att avgöra var medlemmarna finns i hierarkin. Använd metoden GetLevel (Databasmotor) för att hitta hur många nivåer nedåt varje rad finns i hierarkin. Kör följande kod för att visa nivåerna för alla rader:
```
SELECT OrgNode.ToString() AS Text_OrgNode,
OrgNode.GetLevel() AS EmpLevel, *
FROM HumanResources.EmployeeOrg;
GO
```
Använd metoden GetRoot (databasmotor) för att hitta rotnoden i hierarkin. Följande kod returnerar den enskilda raden, som är roten:
```
SELECT OrgNode.ToString() AS Text_OrgNode, *
FROM HumanResources.EmployeeOrg
WHERE OrgNode = HIERARCHYID::GetRoot();
GO
```

Ändra ordning på data i en hierarkisk tabell med hjälp av hierarkiska metoder

gäller för: SQL Server

Omorganisering av en hierarki är en vanlig underhållsuppgift. I den här uppgiften använder vi en UPDATE-instruktion med GetReparentedValue (databasmotor) metod för att först flytta en enskild rad till en ny plats i hierarkin. Sedan flyttar vi ett helt underträd till en ny plats.

Metoden GetReparentedValue tar två argument. Det första argumentet beskriver den del av hierarkin som ska ändras. Om en hierarki till exempel är /1/4/2/3/ och du vill ändra avsnittet /1/4/, blir hierarkin /2/1/2/3/och lämnar de två sista noderna (2/3/) oförändrade, måste du ange de föränderliga noderna (/1/4/) som det första argumentet. Det andra argumentet innehåller den nya hierarkinivån i vårt exempel /2/1/. De två argumenten behöver inte innehålla samma antal nivåer.

Flytta en enskild rad till en ny plats i hierarkin

Wanida rapporterar för närvarande till Sariya. I den här proceduren flyttar du Wanida från den aktuella noden /1/1/, så att den här personen rapporterar till Jill. Den nya noden blir /3/1/ så /1/ är det första argumentet och /3/ är det andra. Dessa motsvarar de OrgNode värdena för Sariya och Jill. Kör följande kod för att flytta Wanida från Sariyas organisation till Jills:

DECLARE @CurrentEmployee HIERARCHYID,
    @OldParent HIERARCHYID,
    @NewParent HIERARCHYID;

SELECT @CurrentEmployee = OrgNode
FROM HumanResources.EmployeeOrg
WHERE EmployeeID = 269;

SELECT @OldParent = OrgNode
FROM HumanResources.EmployeeOrg
WHERE EmployeeID = 46;

SELECT @NewParent = OrgNode
FROM HumanResources.EmployeeOrg
WHERE EmployeeID = 119;

UPDATE HumanResources.EmployeeOrg
SET OrgNode = @CurrentEmployee.GetReparentedValue(@OldParent, @NewParent)
WHERE OrgNode = @CurrentEmployee;
GO

Kör följande kod för att se resultatet:

SELECT OrgNode.ToString() AS Text_OrgNode,
OrgNode, OrgLevel, EmployeeID, EmpName, Title
FROM HumanResources.EmployeeOrg;
GO

Wanida är nu på nod /3/1/.

Omorganisera ett avsnitt i en hierarki

Om du vill visa hur du flyttar ett större antal personer samtidigt kör du först följande kod för att lägga till en internrapportering till Wanida:
```
EXEC AddEmp 269, 291, 'Kevin', 'Marketing Intern';
GO
```

Nu rapporterar Kevin till Wanida, som rapporterar till Jill, som rapporterar till David. Det betyder att Kevin är på nivå /3/1/1/. Om du vill flytta alla Jills underordnade till en ny chef uppdaterar vi alla noder som har /3/ som deras OrgNode till ett nytt värde. Kör följande kod för att uppdatera Wanida för att rapportera till Sariya, men låt Kevin rapportera till Wanida:

DECLARE @OldParent HIERARCHYID,
    @NewParent HIERARCHYID

SELECT @OldParent = OrgNode
FROM HumanResources.EmployeeOrg
WHERE EmployeeID = 119;-- Jill

SELECT @NewParent = OrgNode
FROM HumanResources.EmployeeOrg
WHERE EmployeeID = 46;-- Sariya

DECLARE children_cursor CURSOR
FOR
SELECT OrgNode
FROM HumanResources.EmployeeOrg
WHERE OrgNode.GetAncestor(1) = @OldParent;

DECLARE @ChildId HIERARCHYID;

OPEN children_cursor

FETCH NEXT
FROM children_cursor
INTO @ChildId;

WHILE @@FETCH_STATUS = 0
BEGIN
    START:

    DECLARE @NewId HIERARCHYID;

    SELECT @NewId = @NewParent.GetDescendant(MAX(OrgNode), NULL)
    FROM HumanResources.EmployeeOrg
    WHERE OrgNode.GetAncestor(1) = @NewParent;

    UPDATE HumanResources.EmployeeOrg
    SET OrgNode = OrgNode.GetReparentedValue(@ChildId, @NewId)
    WHERE OrgNode.IsDescendantOf(@ChildId) = 1;

    IF @@error <> 0
        GOTO START -- On error, retry

    FETCH NEXT
    FROM children_cursor
    INTO @ChildId;
END

CLOSE children_cursor;

DEALLOCATE children_cursor;

Kör följande kod för att se resultatet:

SELECT OrgNode.ToString() AS Text_OrgNode,
OrgNode, OrgLevel, EmployeeID, EmpName, Title
FROM HumanResources.EmployeeOrg;
GO

Här är resultaten.

Text_OrgNode OrgNode OrgLevel EmployeeID EmpName Title
------------ ------- -------- ---------- ------- -----------------
/            Ox      0        6          David   Marketing Manager
/1/          0x58    1        46         Sariya  Marketing Specialist
/1/1/        0x5AC0  2        269        Wanida  Marketing Assistant
/1/1/1/      0x5AD0  3        291        Kevin   Marketing Intern
/2/          0x68    1        271        John    Marketing Specialist
/2/1/        0x6AC0  2        272        Mary    Marketing Assistant
/3/          0x78    1        119        Jill    Marketing Specialist

Hela organisationsträdet som rapporterade till Jill (både Wanida och Kevin) rapporterar nu till Sariya.

En lagrad procedur för att omorganisera ett avsnitt i en hierarki finns i avsnittet Flytta underträd i Hierarkiska data (SQL Server).

Dela via

Lektion 2: Skapa och hantera data i en hierarkisk tabell

Förutsättningar

Skapa en tabell med datatypen hierarchyid

Skapa tabellen EmployeeOrg

Fylla i en hierarkisk tabell med hjälp av hierarkiska metoder

Infoga roten i hierarkiträdet

Infoga en underordnad medarbetare

Skapa en procedur för att ange nya noder

Köra frågor mot en hierarkisk tabell med hjälp av hierarkimetoder

Hitta underordnade noder

Använda GetRoot och GetLevel

Ändra ordning på data i en hierarkisk tabell med hjälp av hierarkiska metoder

Flytta en enskild rad till en ny plats i hierarkin

Omorganisera ett avsnitt i en hierarki

Feedback

Ytterligare resurser