Древовидный запрос SQL - большая родительская группа

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

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

• http://onlamp.com/pub/a/onlamp/2004/08/05/hierarchical_sql.html
• http://www.dbmsmag.com/9603d06.html и http://www.dbmsmag.com/9604d06.html
• http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html

Вы можете использовать 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

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

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

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

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)

Как насчет такого:

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 есть собственный метод, использующий CTE, но сам я его не использовал.