SQL Server 2008 中与其他列的排序树

问题描述

我有一个使用 hierarchyid 列实现树的表

I have a table which implements a tree using hierarchyid column

示例数据:

People             \
    Girls          \1\
        Zoey       \1\1\
        Kate       \1\2\
        Monica     \1\3\
    Boys           \2\
        Mark       \2\1\
        David      \2\2\

这是使用hierarchyid列作为排序列的顺序

This is the order using hierarchyid column as sort column

我想使用 hierarchyid 但也使用 name 对数据进行排序，所以它看起来像这样:

I would like to sort data using hierarchyid but also using name so it would look like this:

People             \
    Boys           \2\
        David      \2\2\
        Mark       \2\1\        
    Girls          \1\
        Kate       \1\2\
        Monica     \1\3\
        Zoey       \1\1\    

有没有简单的解决方案来做到这一点?

Is there a simple solution to do this?

是否可以通过单个 SQL 查询来完成?

Can it be done with a single SQL query?

推荐答案

将查询重写为递归 CTE:

DECLARE @table TABLE (id INT NOT NULL PRIMARY KEY, name NVARCHAR(4000) NOT NULL, path HIERARCHYID)

INSERT
INTO    @table
VALUES  
        (1, 'People', '/'),
        (2, 'Girls', '/1/'),
        (3, 'Boys', '/2/'),
        (4, 'Zoey', '/1/1/'),
        (5, 'Kate', '/1/2/'),
        (6, 'Monica', '/1/3/'),
        (7, 'Mark', '/2/1/'),
        (8, 'David', '/2/2/')

;WITH   q AS
        (
        SELECT  *, HIERARCHYID::Parse('/') AS newpath
        FROM    @table
        WHERE   path = HIERARCHYID::GetRoot()
        UNION ALL
        SELECT  t.*, HIERARCHYID::Parse(q.newpath.ToString() + CAST(ROW_NUMBER() OVER (ORDER BY t.name) AS NVARCHAR(MAX)) + '/')
        FROM    q
        JOIN    @table t
        ON      t.path.IsDescendantOf(q.path) = 1
                AND t.path.GetLevel() = q.path.GetLevel() + 1
        )
SELECT  *
FROM    q
ORDER BY
        newpath

这篇关于SQL Server 2008 中与其他列的排序树的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！