Hirarchical sorting in sql server when child id co

问题:

i have some childid like below

childid
------------

1.1
1.2
2.8
2.7
6.5
6.5.1
6.5.15
7.1
8

sort order will be

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

i tried to converted to intiger like below

declare @str nvarchar(max)='1.23.2';

set @str=(select replace(@str,'.',''))
select @str

but it fails when

7.1
8

comes it gives order like

8
7.1 

but i need order like below

7.1
8

also if number like

7.1.1
7.1.8
6.7.7.7

then order should be

6.7.7.7
7.1.1
7.1.8

i hope somebody can help me to solve this

回答1:

Try it like this:

EDIT: I changed the approach to deal with non-numeric values like '123abc' too.

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

回答2:

Not an elegant solution but it works for me:

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

Each cross apply is for getting next value between dots. Downside of this approach is that it is not dynamic and you should add as many cross applies as there could be nesting levels.