SQL Order Chars数字 - 节点分组

I have a column of numbers stored as chars separated by periods - which is used as a parent-child grouping mechanism. Having issues ordering due to the nature of the varchar and 100 coming before 11, as highlighted in bold below:

我有一列数字存储为以句点分隔的字符 - 用作父子分组机制。由于varchar的性质和在11之前出现的问题而导致排序问题,如下面的粗体突出显示:

01
01.01
01.02
01.03
01.03.01
01.03.02
...
01.03.10
01.03.100
01.03.101
01.03.11
01.03.12
...
01.04
01.04.01
01.04.01.01
01.04.01.02
01.04.01.03
01.04.02
01.04.03
02
02.01

01 01.01 01.02 01.03 01.03.01 01.03.02 ... 01.03.10 01.03.100 01.03.101 01.03.11 01.03.12 ... 01.04 01.04.01 01.04.01.01 01.04.01.02 01.04.01.03 01.04.02 01.04.03 02 02.01

etc

Any thoughts on how can I order these chars numerically? There could potentially be unlimited child nodes, so this isn't impossible:

关于如何以数字方式订购这些字符的任何想法?可能存在无限的子节点,因此这不是不可能的:

nn.nn.nn.nn.nn.nn.nn.nn.nn.nn etc

Thank you!

1 个解决方案

#1

If there's a limit to the depth¹ of the tree, then you can write something like:

如果树的depth1有限制,那么你可以这样写:

declare @t table (OrdCol varchar(50) not null)
insert into @t (OrdCol) values
('01'),
('01.01'),
('01.02'),
('01.03'),
('01.03.01'),
('01.03.02'),
('01.03.10'),
('01.03.100'),
('01.03.101'),
('01.03.11'),
('01.03.12'),
('01.04'),
('01.04.01'),
('01.04.01.01'),
('01.04.01.02'),
('01.04.01.03'),
('01.04.02'),
('01.04.03'),
('02'),
('02.01')

select OrdCol from
(select OrdCol,CAST('<a><b>' + REPLACE(OrdCol,'.','</b><b>') + '</b></a>' as xml) as xOrd from @t
) t
order by
    xOrd.value('(a/b)[1]','int'),
    xOrd.value('(a/b)[2]','int'),
    xOrd.value('(a/b)[3]','int'),
    xOrd.value('(a/b)[4]','int'),
    xOrd.value('(a/b)[5]','int'),
    xOrd.value('(a/b)[6]','int'),
    xOrd.value('(a/b)[7]','int'),
    xOrd.value('(a/b)[8]','int'),
    xOrd.value('(a/b)[9]','int'),
    xOrd.value('(a/b)[10]','int')

¹Why I asked a clarifying comment to your question about which way "unlimited" children is meant to be interpreted. This query deals with an unlimited number of children at each level, but only deals with a depth of up to 10.

1为什么我要求澄清一个关于“无限”儿童被解释的方式的问题的评论。此查询处理每个级别的无限数量的子项,但仅处理最多10个的深度。

Unlimited depth version, works provided that there's at most one leading 0 on any of the numbers:

无限深度版本,如果在任何数字上最多有一个前导0,则可以使用:

select OrdCol from
(select OrdCol,CAST(REPLACE(REPLACE('.' + OrdCol + '.','.0','.'),'.','/') as hierarchyid) as hOrd from @t
) t
order by
    hOrd

Which just munges the string until it fits a format castable to hierarchyid, which already performs sorts in the order you expected. Of course, if this is valid, you might consider changing the column datatype to use this type anyway.

这只是对字符串进行管理,直到它符合可转换为hierarchyid的格式,该格式已经按照您预期的顺序执行排序。当然,如果这是有效的,您可以考虑更改列数据类型以使用此类型。

#1