SQL “arbre de la requête” - la plupart des parents du groupe

J'ai un peu de mal à faire un "arbre de la requête" (que faisons-nous appeler cela?) dans SQL.

Prendre un coup d'oeil à mon schéma ci-dessous (table et les noms de colonne sont en danois - désolé):

DB diagramme http://img197.imageshack.us/img197/8721/44060572.jpg
À l'aide de MSSQL Server 2005, l'objectif est de trouver la plus groupe parent (Gruppe), pour chaque client (Kunde).

Chaque groupe peut avoir de nombreux groupes de parents et de nombreux groupes d'enfants.

Et je voudrais aussi savoir comment faire pour afficher l'arborescence comme ceci:

Client 1 
- Parent, groupe 1 
- Enfant groupe 1 
- ChildChild groupe n 
- Groupe enfant n 
- Groupe Parent n 
- ... 
- ... 
Client n 
- ...

Une autre question:

Comment la requête de chercher à obtenir de TOUS les groupes pour tous les clients? Le Parent et l'enfant groupes.

InformationsquelleAutor Tommy Jakobsen | 2009-07-16
  1. 4

    Je ne peux pas le dire mieux que Joe Celko. Le problème est généralement que les modèles construits ne se prêtent pas bien à établir des hiérarchies, et que ces modèles doivent prendre en considération les caractéristiques de votre hiérarchie. Est-il trop profond? Est-il trop grand? Est-il étroit et peu profond?

    Une des clés du succès sur de larges et peu profondes des arbres est d'avoir le chemin complet dans la hiérarchie dans une colonne, comme Celko mentionne dans le premier lien.

    • J'ai déjà regardé ces articles. Ce que je ne comprends pas c'est comment je peux l'appliquer à mon schéma.
    • Vous ne pouvez pas être en mesure de le dire mieux que Celko, mais vous assurer que dit-il de plus agréable 🙂
    • Je suis dire que, sans une modification à votre schéma, vous aurez un moment difficile l'obtention de bonnes performances.
    • Qu'aimeriez-vous changer dans le schéma?
    • Cela dépend de l'utilisation et de l'extension, mais je voudrais ajouter le nom de chemin complet de la colonne de kunde, de cette façon, vous avez le groupe de tête pour gratuit, au coût de la mise à jour d'/générer le nom de chemin complet lors de la mise à jour de l'insertion,
    InformationsquelleAutor Vinko Vrsalovic
  2. 5

    Vous pouvez utiliser la CTE est de construire "le chemin d'accès complet de la colonne" à la volée

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

    Les performances pourraient être sous-optimale si vous avez beaucoup de lectures vs rares modifications.

    InformationsquelleAutor wqw
  3. 3

    Je suis venu avec une solution qui résout le problème de la liste de TOUS les groupes pour chaque client. Le Parent et l'enfant groupes.

    Qu'en pensez-vous?

    AVEC GroupTree 
COMME 
( 
SÉLECTIONNEZ kg.KundeId, g de.Id GruppeId 
DE ActiveDirectory.Gruppe g 
INNER JOIN ActiveDirectory.Kunde_Gruppe kg SUR g.Id = kg.GruppeId 
ET (EXISTS (SELECT * from ActiveDirectory.Gruppe_Gruppe OÙ ParentGruppeId = g.Id) 
OU not EXISTS (SELECT * from ActiveDirectory.Gruppe_Gruppe OÙ ParentGruppeId = g.Id)) 
L'UNION DE TOUS 
SÉLECTIONNEZ GroupTree.KundeId, gg.ChildGruppeId 
DE ActiveDirectory.Gruppe_Gruppe gg 
INNER JOIN GroupTree SUR gg.ParentGruppeId = GroupTree.GruppeId 
) 
SÉLECTIONNEZ KundeId, GruppeId 
DE GroupTree 
OPTION (MAXRECURSION 32767)
    InformationsquelleAutor Tommy Jakobsen
  4. 2

    Comment quelque chose comme cela:

    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)
    InformationsquelleAutor Adriaan Stander
  5. 1

    En T-SQL, vous pouvez écrire une boucle while. Non testé:

    @group = <starting group>
WHILE (EXISTS(SELECT * FROM Gruppe_Gruppe WHERE ChildGruppeId=@group))
BEGIN
SELECT @group=ParentGruppeId FROM Gruppe_Gruppe WHERE ChildGruppeId=@group
END
    • Cela fonctionnerait-il? Que faire si @groupe est parent?
    • Tu veux dire, si @groupe ne dispose pas d'un groupe de parents plus? La boucle n'est pas entré, et le résultat est @groupe, ce que je comprends est ce que vous voulez.
    • Ah oui. Mon mauvais. Comment voulez-vous trouver le plus groupe parent pour chaque client? Il peut y avoir plus d'un.
    InformationsquelleAutor Martin v. Löwis
  6. 1

    Nous utilisons SQL Server 2000 et il est un exemple de l'expansion de hiérarchies à l'aide d'une pile dans la documentation en Ligne de SQL, j'ai écrit un certain nombre de variantes pour notre système ERP

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

    Je comprends qu'il y a une méthode Native à l'aide de CTE dans SQL 2005 mais je n'ai pas utilisé moi-même

    InformationsquelleAutor