transact - Consulta SQL "tipo árbol": la mayoría de los grupos principales

tipo de dato hierarchyid

Question

Tengo problemas para hacer una consulta tipo "árbol" (¿cómo lo llamamos?) En SQL.

Eche un vistazo a mi diagrama a continuación (los nombres de las tablas y columnas están en danés, lo siento):

Diagrama DB http://img197.imageshack.us/img197/8721/44060572.jpg Con MSSQL Server 2005, el objetivo es encontrar el grupo principal (Gruppe) para cada cliente (Kunde).

Cada grupo puede tener muchos grupos de padres y muchos grupos de niños.

Y, también me gustaría saber cómo mostrar el árbol de esta manera:

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

Otra pregunta:

¿Cómo se ve la consulta para obtener TODOS los grupos para todos los clientes? Grupos de padres e hijos.

Answer 1

En T-SQL, puede escribir un ciclo while. No probado:

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

Answer 2

No puedo decirlo mejor que Joe Celko. El problema suele ser que los modelos construidos no se prestan bien para construir jerarquías, y que esos modelos deben tener en cuenta las características de su jerarquía. ¿Es muy profundo? ¿Es demasiado ancho? ¿Es estrecho y superficial?

Una clave para el éxito en árboles anchos y poco profundos es tener el camino completo en la jerarquía en una columna, como menciona Celko en el primer enlace.

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

Answer 3

Puede usar CTE para construir la columna "la ruta completa" sobre la marcha

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

El rendimiento puede ser subóptimo si tiene muchas lecturas y pocas modificaciones.

Answer 4

Qué tal algo como esto:

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)

Answer 5

Se me ocurrió una solución que resuelve el problema de listar TODOS los grupos para cada cliente. Grupos de padres e hijos.

¿Qué piensas?

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)

Answer 6

Usamos SQL Server 2000 y hay un ejemplo de expansión de jerarquías utilizando una pila en los Libros en pantalla de SQL, he escrito una serie de variantes para nuestro sistema ERP

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

Deduzco que hay un método nativo que usa CTE en SQL 2005 pero no lo he usado yo mismo