SQL“树状"查询 - 大多数父组 [英] SQL "tree-like" query - most parent group

问题描述

我在 SQL 中执行树状"查询(我们称之为什么?)时遇到了一些问题.

看看我下面的图表(表名和列名都是丹麦语 - 对此表示抱歉):

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

每个组可以有多个父组和多个子组.

而且，我也想知道如何像这样显示树:

<前>客户 1- 家长组 1- 儿童组 1- ChildChild 组 n- 儿童组 n- 家长组 n- ...- ...客户- ...

<小时>

另一个问题:

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

解决方案

我不能说比 Joe Celko 更好.问题通常是构建的模型不适合构建层次结构，并且这些模型必须考虑您的层次结构的特征.是不是太深了?是不是太宽了?是不是又窄又浅?

在宽树和浅树上取得成功的一个关键是在列中的层次结构中拥有完整路径，就像 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

I'm having some trouble doing a "tree-like" query (what do we call this?) in 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.jpg Using MSSQL Server 2005, the goal is to find the most parent group (Gruppe), for each customer (Kunde).

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.

解决方案

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.

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

这篇关于SQL“树状"查询 - 大多数父组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！