mysql - 按数字顺序按非数字层次结构字符串排序

Question

我有一个表，其中包含表示树结构的数据，其中一列表示该行在分层树中的位置。每个级别都用 - 分隔。

1
1-1
2
2-1
2-2
2-2-1
2-2-2
2-2-2-1

只需在该列上使用 ORDER BY 按顺序检索树。当任何级别的项目超过 10 个时，这会下降，因为该列是按字母顺序排序的。 MySQL 将 10 排在 3 之前。

Actual result:

1
1-10
1-3
2

Desired result:

1
1-3
1-10
2

值可以有任意数量的深度级别。

是否可以在 MySQL 中对这些数据进行数字排序？

Answer 1

我认为最好的办法是将数据转换成可以自然排序的东西。如果你的树结构总是有少于 99 个 child ，你可以创建一个像我下面的函数。您只需在排序函数中使用“GetTreeStructureSort(columnName)”。 (如果你有可能是 3 位数字，你可以调整这个更直观。)

CREATE FUNCTION GetTreeStructureSort
(
    -- Add the parameters for the function here
    @structure varchar(500) 
)
RETURNS varchar(500)
AS
BEGIN

    DECLARE @sort varchar(500)

    -- Add a hyphen to the beginning and end to make all the numbers from 1 to 9 easily replaceable
    SET @sort = '-' + @structure + '-'

    -- Replace each instance of a one-digit number to a two-digit representation
    SELECT @sort = REPLACE(@sort, '-1-', '-01-')
    SELECT @sort = REPLACE(@sort, '-2-', '-02-')
    SELECT @sort = REPLACE(@sort, '-3-', '-03-')
    SELECT @sort = REPLACE(@sort, '-4-', '-04-')
    SELECT @sort = REPLACE(@sort, '-5-', '-05-')
    SELECT @sort = REPLACE(@sort, '-6-', '-06-')
    SELECT @sort = REPLACE(@sort, '-7-', '-07-')
    SELECT @sort = REPLACE(@sort, '-8-', '-08-')
    SELECT @sort = REPLACE(@sort, '-9-', '-09-')

    -- Strip off the first and last hyphens that were added at the beginning.
    SELECT @sort = SUBSTRING(@sort, 2, LEN(@sort) - 2)

    -- Return the result of the function
    RETURN @sort

END

这将转换这些结果:

1
1-10
1-3
2

进入这个:

01
01-03
01-10
02

我用下面的代码测试了这个:

DECLARE  @something varchar(255)
set @something = '1-10-3-21'

SELECT dbo.GetTreeStructureSort(@something)