SqlServer 将类似于1.1.1数据进行排序-知识分享平台

查询码: 00000416

SqlServer 将类似于1.1.1数据进行排序

作者: 周杨于 2020年06月12日 发布在分类 / 计算机应用 / 数据库 / SQLSERVER ,于 2020年06月12日编辑

转自：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,'.')

结果：
粘贴图片

备注	修改日期	修改人
其他原因...	2020-06-12 16:33:36[当前版本]	系统管理员
CREAT	2020-06-12 16:24:59	系统管理员

附件类型	PNG
paste1591950122030.png(6857.0b) paste1591950220666.png(13184.0b) paste1591950661697.png(18958.0b) paste1591950778208.png(42590.0b)

[Title]