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