代码之家 › 专栏 › 技术社区 › William

搜索分层数据的最佳方法

recursive-query sql-server-2005 asp.net c#

William · 技术社区 · 15 年前

我正在考虑建立一个允许使用分层过滤查询数据的工具。我有一些想法,我将如何去做,但想知道是否有任何建议或建议,可能会更有效。

例如,假设用户正在搜索作业。工作领域如下。

1: Scotland
2: --- West Central
3: ------ Glasgow
4: ------ Etc
5: --- North East
6: ------ Ayrshire
7: ------ Etc

用户可以搜索特定区域(如格拉斯哥)或更大区域(如苏格兰)。

我正在考虑的两种方法是:

在数据库中记录每个记录的子项(即cat 1的子项字段中有2、3、4),并使用 SELECT * FROM Jobs WHERE Category IN Areas.childrenField .
使用递归函数查找与选定区域有关系的所有结果。

我从这两方面看到的问题是:

将这些数据保存在数据库中意味着必须跟踪结构的所有更改。
递归是缓慢而无效的。

对最佳方法有何想法、建议或建议?我正在使用c asp.net和mssql 2005数据库。

5 回复 | 直到 14 年前

unclepaul84 15 年前

下面是我看到的一种方法:

创建一个名为hierarchyid的varchar(max)字段。为所有根对象生成基ID。为每个子对象生成一个id并用父对象的id将其前置。

示例表

ID(PK) HierarchyID Area
1       sl           Scotland 
2       slwc        West Central
3       slwcgg       Glasgow

示例查询

SELECT * FROM Areas Where HierarchyID LIKE 'sl%'

Martin 15 年前

应该使用嵌套集。这是mysql中的一个实现。 http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

Jelle 15 年前

你可以使用 Common Table Expressions 进行递归查询。我发现这种技术非常强大,易于阅读和维护。

takrl cck 14 年前

这个怎么样?

表=&

身份证件帕伦特名字

好简单的桌子?

那么,用一段漂亮复杂的sql语句来处理这个问题怎么样?(我想是铁石)

public object FetchCategoryTree()
{
    var sql = @"SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

    WITH AreaTree (ID, Name, ParentID, OrgLevel, SortKey) AS
    (
        -- Create the anchor query. This establishes the starting
        -- point
        SELECT
            a.ID,
            cast('---- ' + a.Name as varchar(255)),
            a.ParentID,
            cast('----' as varchar(55)),
            CAST(a.ID AS VARBINARY(900))
        FROM dbo.Area a
        WHERE a.ParentID is null
        UNION ALL
        -- Create the recursive query. This query will be executed
        -- until it returns no more rows
        SELECT
            a.ID,
            cast('----' + b.OrgLevel + '  ' + a.Name as varchar(255)),
            a.ParentID,
            cast(b.OrgLevel+ '----' as varchar(55)),
            CAST(b.SortKey + CAST (a.ID AS BINARY(4)) AS VARBINARY(900))
        FROM dbo.Area a
                INNER JOIN AreaTree b ON a.ParentID = b.ID
    )
    SELECT * FROM AreaTree
    ORDER BY SortKey";

    return FetchObject(sql);
}

现在这会产生一些不太确定的sql魔术。不过,用外行的话说,它基本上把第一部分作为根查询。然后它返回到表中,通过连接使用第一部分的答案执行第二部分,并继续执行,直到仍然找不到任何匹配项,基本上是一个大循环。也很快。

您将得到一个附加排序键的一堆行。按排序键排序查询后,将得到如下答案:

 ---- parent 1
 -------- child 1
 -------- child 2
 ------------ child 2.1
 ---- parent 2
 -------- etc

可能是你要找的?

user296065 15 年前

在我们的应用程序中,我使用joe celko的树模型(state/county/city/misc)来表示销售税层次结构,它工作得很好。

您的“在此区域或以下查找作业”查询如下所示:

SELECT * FROM Jobs WHERE Jobs.AreaID IN
(SELECT P1.AreaID
FROM Areas AS P1, Areas AS P2
WHERE P1.lft BETWEEN P2.lft AND P2.rgt
AND P2.Areas.AreaID = @selectedAreaID)

Celko Tree in SQL article