转自：https://blog.csdn.net/qq_19299335/article/details/97784674

一、建表CodeOrder

DROP TABLE [dbo].[CodeOrder]
GO
CREATE TABLE [dbo].[CodeOrder] (
[code] varchar(255) NOT NULL 
)


GO


INSERT INTO [dbo].[CodeOrder] ([code]) VALUES (N'1')
GO
GO
INSERT INTO [dbo].[CodeOrder] ([code]) VALUES (N'1.1')
GO
GO
INSERT INTO [dbo].[CodeOrder] ([code]) VALUES (N'1.1.1')
GO
GO
INSERT INTO [dbo].[CodeOrder] ([code]) VALUES (N'1.1.2')
GO
GO
INSERT INTO [dbo].[CodeOrder] ([code]) VALUES (N'1.1.3')
GO
GO
INSERT INTO [dbo].[CodeOrder] ([code]) VALUES (N'1.1.4')
GO
GO
INSERT INTO [dbo].[CodeOrder] ([code]) VALUES (N'2')
GO
GO
INSERT INTO [dbo].[CodeOrder] ([code]) VALUES (N'2.1.1')
GO
GO
INSERT INTO [dbo].[CodeOrder] ([code]) VALUES (N'2.1.10')
GO
GO
INSERT INTO [dbo].[CodeOrder] ([code]) VALUES (N'2.1.2')
GO
GO
INSERT INTO [dbo].[CodeOrder] ([code]) VALUES (N'2.1.3')
GO
GO
INSERT INTO [dbo].[CodeOrder] ([code]) VALUES (N'3')
GO
GO
INSERT INTO [dbo].[CodeOrder] ([code]) VALUES (N'4')
GO
GO
INSERT INTO [dbo].[CodeOrder] ([code]) VALUES (N'5')
GO
GO
INSERT INTO [dbo].[CodeOrder] ([code]) VALUES (N'5.1')
GO
GO
INSERT INTO [dbo].[CodeOrder] ([code]) VALUES (N'5.1.1.1')
GO
GO
INSERT INTO [dbo].[CodeOrder] ([code]) VALUES (N'5.1.1')
GO
GO
INSERT INTO [dbo].[CodeOrder] ([code]) VALUES (N'1.2.1')
GO
GO
INSERT INTO [dbo].[CodeOrder] ([code]) VALUES (N'1.2.10')
GO
GO
INSERT INTO [dbo].[CodeOrder] ([code]) VALUES (N'1.2.2')
GO
GO

二、创建函数formatOrderId

CREATE FUNCTION [dbo].[formatOrderId](
@orderId VARCHAR(50),   --排序字段/需要格式化的数值，如column_name/1.1.1
@num  int,              --格式化后的数字每个段是几位数，默认4位
@separator VARCHAR(50)  --需要格式的的字段或者数值是以什么分隔符分隔的，默认为'.'
)
RETURNS  VARCHAR(50)
AS
BEGIN
    IF @num is NULL or @num = ''
    BEGIN
        SET @num=4               
    END
    IF @separator is NULL or @separator = ''
  BEGIN
        SET @separator='.'              
    END
    DECLARE @newOrderId VARCHAR(50)
  DECLARE @tempLen int
    SET @newOrderId=''
  set @tempLen=0
    WHILE LEN(@orderId)-LEN(REPLACE(@orderId,@separator,''))>0
    BEGIN
        set @tempLen=@num-LEN(LEFT(@orderId,PATINDEX('%'+@separator+'%',@orderId)-1))
        IF @newOrderId=''
        BEGIN
            SET @newOrderId=REPLICATE('0', @tempLen) + LEFT(@orderId,PATINDEX('%'+@separator+'%',@orderId)-1)   
        END
        ELSE
        BEGIN
            SET @newOrderId=@newOrderId+@separator+REPLICATE('0', @tempLen) + LEFT(@orderId,PATINDEX('%'+@separator+'%',@orderId)-1)
        END
        SET @orderId=RIGHT(@orderId,LEN(@orderId)-PATINDEX('%'+@separator+'%',@orderId))
    END
  set @tempLen=@num-LEN(@orderId)
  IF @newOrderId=''
    BEGIN
        SET @newOrderId=REPLICATE('0', @tempLen)+@orderId                 
    END
    ELSE
    BEGIN
        SET @newOrderId=@newOrderId+@separator+REPLICATE('0', @tempLen)+@orderId        
    END

    RETURN  @newOrderId
END

三、测试运行

select *,dbo.formatOrderId(code,5,'.') orderId from CodeOrder order  by orderId

结果：

select *,REPLACE(code, '.', '-') code2,dbo.formatOrderId(REPLACE(code, '.', '-'),5,'-') orderId from CodeOrder order  by orderId

四、应用

4.1 使用普通排序

select * from CodeOrder ORDER BY dbo.formatOrderId(code,4,'.') asc

4.1 树结构数据排序

4.1.1 建表测试

order_id是对每个树节点同级别的顺序号，kid是唯一键，parent_id=0是表示根节点

DROP TABLE [dbo].[tree_code_order]
GO
CREATE TABLE [dbo].[tree_code_order] (
[code] varchar(255) NOT NULL ,
[order_id] int NULL ,
[kid] bigint NOT NULL ,
[parent_id] bigint NULL 
)


GO


INSERT INTO [dbo].[tree_code_order] ([code], [order_id], [kid], [parent_id]) VALUES (N'1', N'1', N'316086018983370752', N'0')
GO
GO
INSERT INTO [dbo].[tree_code_order] ([code], [order_id], [kid], [parent_id]) VALUES (N'1.1', N'1', N'316086018983370753', N'316086018983370752')
GO
GO
INSERT INTO [dbo].[tree_code_order] ([code], [order_id], [kid], [parent_id]) VALUES (N'1.1.1', N'1', N'316086018983370754', N'316086018983370753')
GO
GO
INSERT INTO [dbo].[tree_code_order] ([code], [order_id], [kid], [parent_id]) VALUES (N'1.1.2', N'2', N'316086018983370755', N'316086018983370753')
GO
GO
INSERT INTO [dbo].[tree_code_order] ([code], [order_id], [kid], [parent_id]) VALUES (N'1.1.3', N'3', N'316086018983370756', N'316086018983370753')
GO
GO
INSERT INTO [dbo].[tree_code_order] ([code], [order_id], [kid], [parent_id]) VALUES (N'1.1.4', N'4', N'316086018983370757', N'316086018983370753')
GO
GO
INSERT INTO [dbo].[tree_code_order] ([code], [order_id], [kid], [parent_id]) VALUES (N'2', N'2', N'316086018983370758', N'0')
GO
GO
INSERT INTO [dbo].[tree_code_order] ([code], [order_id], [kid], [parent_id]) VALUES (N'2.1.1', N'1', N'316086018983370759', N'316086988777758720')
GO
GO
INSERT INTO [dbo].[tree_code_order] ([code], [order_id], [kid], [parent_id]) VALUES (N'2.1.10', N'10', N'316086018983370760', N'316086988777758720')
GO
GO
INSERT INTO [dbo].[tree_code_order] ([code], [order_id], [kid], [parent_id]) VALUES (N'2.1.2', N'2', N'316086018983370761', N'316086988777758720')
GO
GO
INSERT INTO [dbo].[tree_code_order] ([code], [order_id], [kid], [parent_id]) VALUES (N'2.1.3', N'3', N'316086018983370762', N'316086988777758720')
GO
GO
INSERT INTO [dbo].[tree_code_order] ([code], [order_id], [kid], [parent_id]) VALUES (N'3', N'3', N'316086018983370763', N'0')
GO
GO
INSERT INTO [dbo].[tree_code_order] ([code], [order_id], [kid], [parent_id]) VALUES (N'4', N'4', N'316086018983370764', N'0')
GO
GO
INSERT INTO [dbo].[tree_code_order] ([code], [order_id], [kid], [parent_id]) VALUES (N'5', N'5', N'316086018983370765', N'0')
GO
GO
INSERT INTO [dbo].[tree_code_order] ([code], [order_id], [kid], [parent_id]) VALUES (N'5.1', N'1', N'316086018983370766', N'316086018983370765')
GO
GO
INSERT INTO [dbo].[tree_code_order] ([code], [order_id], [kid], [parent_id]) VALUES (N'5.1.1.1', N'1', N'316086018983370767', N'316086018983370768')
GO
GO
INSERT INTO [dbo].[tree_code_order] ([code], [order_id], [kid], [parent_id]) VALUES (N'5.1.1', N'1', N'316086018983370768', N'316086018983370766')
GO
GO
INSERT INTO [dbo].[tree_code_order] ([code], [order_id], [kid], [parent_id]) VALUES (N'1.2.1', N'1', N'316086018983370769', N'316087307322564608')
GO
GO
INSERT INTO [dbo].[tree_code_order] ([code], [order_id], [kid], [parent_id]) VALUES (N'1.2.10', N'10', N'316086018983370770', N'316087307322564608')
GO
GO
INSERT INTO [dbo].[tree_code_order] ([code], [order_id], [kid], [parent_id]) VALUES (N'1.2.2', N'2', N'316086018983370771', N'316087307322564608')
GO
GO
INSERT INTO [dbo].[tree_code_order] ([code], [order_id], [kid], [parent_id]) VALUES (N'2.1', N'1', N'316086988777758720', N'316086018983370758')
GO
GO
INSERT INTO [dbo].[tree_code_order] ([code], [order_id], [kid], [parent_id]) VALUES (N'1.2', N'2', N'316087307322564608', N'316086018983370752')
GO
GO



ALTER TABLE [dbo].[tree_code_order] ADD PRIMARY KEY ([kid])
GO

4.1.2 树结构数据，生成类似1.1.1字段lev 再进行排序

WITH codeTemp AS(
SELECT  CONVERT(VARCHAR(50), order_id) AS lev, *
FROM tree_code_Order
WHERE parent_id=0
UNION ALL
SELECT  CONVERT(VARCHAR(50), C.lev+'.'+CONVERT(VARCHAR(50),T.order_id)) AS lev, T.*
FROM codeTemp AS C
INNER JOIN tree_code_Order AS T ON C.kid=T.parent_id
) 

查询：
SELECT * FROM codeTemp order BY dbo.formatOrderId(lev,4,'.')

结果：