SQL“树状”查询 - 大多数父组

SQL "tree-like" query - most parent group

提问by Tommy Jakobsen

I'm having some trouble doing a "tree-like" query (what do we call this?) in SQL.

我在 SQL 中执行“树状”查询（我们称之为什么？）时遇到了一些麻烦。

Take a look at my diagram below (table and column names are in danish - sorry about that):

看看我下面的图表（表名和列名都是丹麦语 - 对此表示抱歉）：

DB diagram http://img197.imageshack.us/img197/8721/44060572.jpgUsing MSSQL Server 2005, the goal is to find the most parent group(Gruppe), for each customer (Kunde).

DB 图 http://img197.imageshack.us/img197/8721/44060572.jpg使用 MSSQL Server 2005，目标是为每个客户 (Kunde)找到最父组(Gruppe)。

Each group can have many parent groups and many child groups.

每个组可以有许多父组和许多子组。

And, I would also like to know how to display the tree like this:

而且，我还想知道如何像这样显示树：

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

---

---

Another question:

另一个问题：

How does the query look to get ALL the groups for all the customers? Parent and child groups.

查询如何获取所有客户的所有组？家长和孩子团体。

采纳答案by Vinko Vrsalovic

I just can't say it better than Joe Celko. The problem is usually that the models built doesn't lend themselves well to build hierarchies, and that those models have to take in consideration the characteristics of your hierarchy. Is it too deep? Is it too wide? Is it narrow and shallow?

我不能说比乔塞尔科更好。问题通常是构建的模型不适合构建层次结构，并且这些模型必须考虑层次结构的特征。是不是太深了？是不是太宽了？它是窄而浅的吗？

One key to success on wide and shallow trees is to have the full path in the hierarchy in a column, like Celko mentions in the first link.

在宽树和浅树上成功的一个关键是在列中的层次结构中拥有完整路径，就像 Celko 在第一个链接中提到的那样。

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

• 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

回答by wqw

You can use CTE's to construct "the full path" column on the fly

您可以使用 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

Performance might be sub-optimal if you have lots of reads vs rare modifications.

如果您有大量读取而不是很少修改，则性能可能不是最佳的。

回答by Tommy Jakobsen

I came up with a solution that solves the problem of listing ALL the groups for each customer. Parent and child groups.

我想出了一个解决方案来解决为每个客户列出所有组的问题。家长和孩子团体。

What do you think?

你怎么认为？

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)

回答by Adriaan Stander

How about something like this:

这样的事情怎么样：

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)

回答by Martin v. L?wis

In T-SQL, you can write a while loop. Untested:

在 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

回答by Martin v. L?wis

We use SQL Server 2000 and there is an example of expanding hierarchies using a stack in the SQL Books Online, I have written a number of variants for our ERP system

我们使用 SQL Server 2000，并且在 SQL 联机丛书中有一个使用堆栈扩展层次结构的示例，我为我们的 ERP 系统编写了许多变体

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

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

I gather that there is a Native method using CTE within SQL 2005 but I have not used it myself

我认为在 SQL 2005 中有一个使用 CTE 的 Native 方法，但我自己没有使用过