当子 ID 包含"时，SQL 服务器中的分层排序。

Question

我有一些孩子喜欢下面

childid
------------
1.1
1.2
2.8
2.7
6.5
6.5.1
6.5.15
7.1
8

排序顺序为

childid 
--------
1.1
1.2
2.7
2.8
6.5
6.51
6.5.15
7.1

我试图转换成整数，就像下面的

declare @str nvarchar(max)='1.23.2';
set @str=(select replace(@str,'.',''))
select @str

但是当

7.1
8

是它给出了像

这样的顺序

8
7.1 

但是我需要像下面这样的顺序

7.1
8

也可以是number like

7.1.1
7.1.8
6.7.7.7

则顺序应为

6.7.7.7
7.1.1
7.1.8

我希望有人能帮我解决这个问题

Answer 1

试一下:

编辑:我也改变了处理像'123abc'这样的非数字值的方法。

declare @ids table(idList varchar(100))
insert into @ids values
 ('1.1')
,('1.2')
,('2.8')
,('2.7')
,('6.5')
,('6.5.1')
,('6.5.15')
,('7.1')
,('8');
select idList,padded.OrderBy
from @ids as ids
cross apply(select cast('<r>' + replace(idList,'.','</r><r>') + '</r>' as xml)) as AsXml(val)
cross apply
(
    select right('                ' + rtrim(x.y.value('.','varchar(max)')),10) 
    from AsXml.val.nodes('/r') as x(y)
    for xml path('')
) as padded(OrderBy)
order by padded.OrderBy

Answer 2

不是一个优雅的解决方案，但它适用于我:

DECLARE @t TABLE ( childid VARCHAR(100) )
INSERT  INTO @t
VALUES  ( '1.1' ),
        ( '1.2' ),
        ( '2.8' ),
        ( '2.7' ),
        ( '6.5' ),
        ( '6.5.1' ),
        ( '6.5.15' ),
        ( '7.1' ),
        ( '8' )
;WITH cte AS(SELECT childid + '.' AS childid FROM @t)
SELECT LEFT(childid, LEN(childid) - 1) AS childid
FROM cte
CROSS APPLY(SELECT CHARINDEX('.', childid) i1) c1
CROSS APPLY(SELECT CASE WHEN i1 = 0 THEN 0 ELSE CHARINDEX('.', childid, i1 + 1) END i2) c2
CROSS APPLY(SELECT CASE WHEN i2 = 0 THEN 0 ELSE CHARINDEX('.', childid, i2 + 1) END i3) c3
CROSS APPLY(SELECT CASE WHEN i3 = 0 THEN 0 ELSE CHARINDEX('.', childid, i3 + 1) END i4) c4
CROSS APPLY(SELECT CASE WHEN i4 = 0 THEN 0 ELSE CHARINDEX('.', childid, i4 + 1) END i5) c5
ORDER BY
        CASE WHEN i1 = 0 THEN childid ELSE SUBSTRING(childid, 1, i1 - 1) END,
        CASE WHEN i2 = 0 THEN '0' ELSE SUBSTRING(childid, i1 + 1, i2 - i1 - 1) END,
        CASE WHEN i3 = 0 THEN '0' ELSE SUBSTRING(childid, i2 + 1, i3 - i2 - 1) END,
        CASE WHEN i4 = 0 THEN '0' ELSE SUBSTRING(childid, i3 + 1, i4 - i3 - 1) END,
        CASE WHEN i5 = 0 THEN '0' ELSE SUBSTRING(childid, i4 + 1, i5 - i4 - 1) END

每个cross apply用于获取点之间的下一个值。这种方法的缺点是它不是动态的，您应该添加尽可能多的cross applies，因为可能存在嵌套级别。