SQL-древовидный запрос – основная группа

У меня возникли проблемы с выполнением «древовидного» запроса (что мы называем этим?) В SQL.

Взгляните на мою диаграмму ниже (имена таблиц и столбцов на датском языке – извините за это):

Диаграмма DB http://img197.imageshack.us/img197/8721/44060572.jpg Используя MSSQL Server 2005, целью является найти основную группу (Gruppe) для каждого клиента (Kunde).

Каждая группа может иметь множество родительских групп и множество дочерних групп.

И я также хотел бы знать, как отображать дерево следующим образом:

 Клиент 1
    - Родительская группа 1
       - Детская группа 1
          - Группа ChildChild n
       - Детская группа n
    - Родительская группа n
       - ...
          - ...
 Клиент n
    - ...

Другой вопрос:

Как выглядит запрос, чтобы получить ВСЕ группы для всех клиентов? Родительские и дочерние группы.

Я просто не могу сказать это лучше, чем Джо Селко. Как правило, проблема заключается в том, что построенные модели не позволяют хорошо строить иерархии и что эти модели должны учитывать характеристики вашей иерархии. Это слишком глубоко? Он слишком широк? Он узкий и мелкий?

Один ключ к успеху на широких и неглубоких деревьях состоит в том, чтобы иметь полный путь в иерархии в столбце, как упоминает Селько в первой ссылке.

Вы можете использовать CTE для создания столбца «полный путь» на лету

--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 

Производительность может оказаться неоптимальной, если у вас много чтений или редких модификаций.

Я придумал решение, которое решает проблему перечисления ВСЕХ групп для каждого клиента. Родительские и дочерние группы.

Как вы думаете?

 С GroupTree
 В ВИДЕ
 (
     SELECT kg.KundeId, g.Id GruppeId
     FROM ActiveDirectory.Gruppe g
     INNER JOIN ActiveDirectory.Kunde_Gruppe kg ON g.Id = kg.GruppeId
     И (EXISTS (SELECT * FROM ActiveDirectory.Gruppe_Gruppe WHERE ParentGruppeId = g.Id)
     ИЛИ НЕ СУЩЕСТВУЕТ (SELECT * FROM ActiveDirectory.Gruppe_Gruppe WHERE ParentGruppeId = g.Id))

     СОЮЗ ВСЕ

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

 ВАРИАНТ (МАКСИРОВАНИЕ 32767)

Как насчет чего-то вроде этого:

 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) 

В T-SQL вы можете написать цикл while. Непроверенные:

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

Мы используем SQL Server 2000, и есть пример расширения иерархии с использованием стека в SQL Books Online, я написал несколько вариантов для нашей системы ERP

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

Я понимаю, что в SQL 2005 существует метод Native, использующий CTE, но я сам его не использовал