All geek questions in one place

SQL tree query - core group

I am having problems executing a "tree" query (what do we call this?) In SQL.

Take a look at my chart below (table and column names in Danish - sorry for that):

Database diagram http://img197.imageshack.us/img197/8721/44060572.jpg Using MSSQL Server 2005, the goal is to find the main group (Gruppe) for each client (Kunde).

Each group can have many parent groups and many child groups.

And I would also like to know how to display the tree as follows:

  Customer 1
    - Parent group 1
       - Child group 1
          - ChildChild group n
       - Child group n
    - Parent group n
       - ...
          - ...
 Customer n
    - ...

Another question:

What does the request look like to get ALL groups for all customers? Parent and child groups.

+5
source share
6 answers

I just can't say it better than Joe Selco. As a rule, the problem is that the constructed models do not allow you to build hierarchies well and that these models must take into account the characteristics of your hierarchy. Is it too deep? Is he too wide? Is it narrow and small?

One key to success on wide and shallow trees is to have the full path in the hierarchy in the column, for example, Celko mentions in the first link.

+4
source

You can use CTE to create a full path column on the fly.

--DROP TABLE Gruppe, Kunde, Gruppe_Gruppe, Kunde_Gruppe CREATE TABLE Gruppe ( Id INT PRIMARY KEY , Name VARCHAR(100) ) CREATE TABLE Kunde ( Id INT PRIMARY KEY , Name VARCHAR(100) ) CREATE TABLE Gruppe_Gruppe ( ParentGruppeId INT , ChildGruppeId INT ) CREATE TABLE Kunde_Gruppe ( KundeId INT , GruppeId INT ) INSERT Gruppe VALUES (1, 'Group 1'), (2, 'Group 2'), (3, 'Group 3') , (4, 'Sub-group A'), (5, 'Sub-group B'), (6, 'Sub-group C'), (7, 'Sub-group D') INSERT Kunde VALUES (1, 'Kunde 1'), (2, 'Kunde 2'), (3, 'Kunde 3') INSERT Gruppe_Gruppe VALUES (1, 4), (1, 5), (1, 7) , (2, 6), (2, 7) , (6, 1) INSERT Kunde_Gruppe VALUES (1, 1), (1, 2) , (2, 3), (2, 4) ;WITH CTE AS ( SELECT CONVERT(VARCHAR(1000), REPLACE(CONVERT(CHAR(5), k.Id), ' ', 'K')) AS TheKey , k.Name AS Name FROM Kunde k UNION ALL SELECT CONVERT(VARCHAR(1000), REPLACE(CONVERT(CHAR(5), x.KundeId), ' ', 'K') + REPLACE(CONVERT(CHAR(5), g.Id), ' ', 'G')) AS TheKey , g.Name FROM Gruppe g JOIN Kunde_Gruppe x ON g.Id = x.GruppeId UNION ALL SELECT CONVERT(VARCHAR(1000), p.TheKey + REPLACE(CONVERT(CHAR(5), g.Id), ' ', 'G')) AS TheKey , g.Name FROM Gruppe g JOIN Gruppe_Gruppe x ON g.Id = x.ChildGruppeId JOIN CTE p ON REPLACE(CONVERT(CHAR(5), x.ParentGruppeId), ' ', 'G') = RIGHT(p.TheKey, 5) WHERE LEN(p.TheKey) < 32 * 5 ) SELECT * , LEN(TheKey) / 5 AS Level FROM CTE c ORDER BY c.TheKey

Performance may not be optimal if you have a lot of readings or rare changes.

+5
source

I came up with a solution that solves the problem of listing ALL groups for each client. Parent and child groups.

What do you think?

  WITH GroupTree
 As
 (
     SELECT kg.KundeId, g.Id GruppeId
     FROM ActiveDirectory.Gruppe g
     INNER JOIN ActiveDirectory.Kunde_Gruppe kg ON g.Id = kg.GruppeId
     AND (EXISTS (SELECT * FROM ActiveDirectory.Gruppe_Gruppe WHERE ParentGruppeId = g.Id)
     OR NOT EXISTS (SELECT * FROM ActiveDirectory.Gruppe_Gruppe WHERE ParentGruppeId = g.Id))

     UNION ALL

     SELECT GroupTree.KundeId, gg.ChildGruppeId
     FROM ActiveDirectory.Gruppe_Gruppe gg
     INNER JOIN GroupTree ON gg.ParentGruppeId = GroupTree.GruppeId
 )
 SELECT KundeId, GruppeId
 FROM GroupTree

 OPTION (MAXRECURSION 32767)
+3
source

How about something like this:

 DECLARE @Customer TABLE( CustomerID INT IDENTITY(1,1), CustomerName VARCHAR(MAX) ) INSERT INTO @Customer SELECT 'Customer1' INSERT INTO @Customer SELECT 'Customer2' INSERT INTO @Customer SELECT 'Customer3' DECLARE @CustomerTreeStructure TABLE( CustomerID INT, TreeItemID INT ) INSERT INTO @CustomerTreeStructure (CustomerID,TreeItemID) SELECT 1, 1 INSERT INTO @CustomerTreeStructure (CustomerID,TreeItemID) SELECT 2, 12 INSERT INTO @CustomerTreeStructure (CustomerID,TreeItemID) SELECT 3, 1 INSERT INTO @CustomerTreeStructure (CustomerID,TreeItemID) SELECT 3, 12 DECLARE @TreeStructure TABLE( TreeItemID INT IDENTITY(1,1), TreeItemName VARCHAR(MAX), TreeParentID INT ) INSERT INTO @TreeStructure (TreeItemName,TreeParentID) SELECT '001', NULL INSERT INTO @TreeStructure (TreeItemName,TreeParentID) SELECT '001.001', 1 INSERT INTO @TreeStructure (TreeItemName,TreeParentID) SELECT '001.001.001', 2 INSERT INTO @TreeStructure (TreeItemName,TreeParentID) SELECT '001.001.002', 2 INSERT INTO @TreeStructure (TreeItemName,TreeParentID) SELECT '001.001.003', 2 INSERT INTO @TreeStructure (TreeItemName,TreeParentID) SELECT '001.002', 1 INSERT INTO @TreeStructure (TreeItemName,TreeParentID) SELECT '001.003', 1 INSERT INTO @TreeStructure (TreeItemName,TreeParentID) SELECT '001.003.001', 7 INSERT INTO @TreeStructure (TreeItemName,TreeParentID) SELECT '001.001.002.001', 4 INSERT INTO @TreeStructure (TreeItemName,TreeParentID) SELECT '001.001.002.002', 4 INSERT INTO @TreeStructure (TreeItemName,TreeParentID) SELECT '001.001.002.003', 4 INSERT INTO @TreeStructure (TreeItemName,TreeParentID) SELECT '002', NULL INSERT INTO @TreeStructure (TreeItemName,TreeParentID) SELECT '002.001', 12 INSERT INTO @TreeStructure (TreeItemName,TreeParentID) SELECT '002.001.001', 13 INSERT INTO @TreeStructure (TreeItemName,TreeParentID) SELECT '002.001.002', 13 ;WITH Structure AS ( SELECT TreeItemID, TreeItemName, TreeParentID, REPLICATE('0',5 - LEN(CAST(TreeItemID AS VARCHAR(MAX)))) + CAST(TreeItemID AS VARCHAR(MAX)) + '\\' TreePath FROM @TreeStructure ts WHERE ts.TreeParentID IS NULL UNION ALL SELECT ts.*, s.TreePath + REPLICATE('0',5 - LEN(CAST(ts.TreeItemID AS VARCHAR(5)))) + CAST(ts.TreeItemID AS VARCHAR(5)) + '\\' TreePath FROM @TreeStructure ts INNER JOIN Structure s ON ts.TreeParentID = s.TreeItemID ) SELECT c.CustomerName, Children.TreeItemName, Children.TreePath FROM @Customer c INNER JOIN @CustomerTreeStructure cts ON c.CustomerID = cts.CustomerID INNER JOIN Structure s ON cts.TreeItemID = s.TreeItemID INNER JOIN ( SELECT * FROM Structure ) Children ON Children.TreePath LIKE s.TreePath +'%' ORDER BY 1,3 OPTION (MAXRECURSION 0)
+2
source

In T-SQL, you can write a while loop. Unverified:

 @group = <starting group> WHILE (EXISTS(SELECT * FROM Gruppe_Gruppe WHERE ChildGruppeId=@group )) BEGIN SELECT @group=ParentGruppeId FROM Gruppe_Gruppe WHERE ChildGruppeId=@group END
+1
source

We are using SQL Server 2000, and there is an example of expanding the hierarchy using the stack in SQL Books Online, I wrote several options for our ERP system

http://support.microsoft.com/kb/248915

I understand that in SQL 2005 there is a native method using CTE, but I didn’t use it myself

+1
source

Source: https://habr.com/ru/post/918520/

More articles:


All Articles