Ordering by a non-numeric hierarchy string numerically
I have a table with data representing a tree structure, with one column indicating the row's position in the hierarchical tree. Each level is separated with a -
.
1
1-1
2
2-1
2-2
2-2-1
2-2-2
2-2-2-1
The tree is retrieved in order simply with an ORDER BY
on this column. This falls down when there ar开发者_如何学编程e more than 10 items at any level, as the column is sorted alphabetically. MySQL sorts 10
before 3
.
Actual result:
1
1-10
1-3
2
Desired result:
1
1-3
1-10
2
There could be any number of levels of depth to the values.
Is it possible to sort this data numerically in MySQL?
I think your best shot is to convert the data into something that does naturally sort. If you tree structure will always have less than 99 children, you could create a function like I have below. You would just use the "GetTreeStructureSort(columnName)" in the sort function. (If you have the possibility of 3-digit numbers, you could adjust this to be more intuitive.)
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
This would convert these results:
1
1-10
1-3
2
into this:
01
01-03
01-10
02
I tested this with the following code:
DECLARE @something varchar(255)
set @something = '1-10-3-21'
SELECT dbo.GetTreeStructureSort(@something)
精彩评论