sql server如何对序列号排序

[转折、]

struct
1.1
1.2.1
1.10.1
1.10.2
1.10.10
1.1.1.1.1.1
1.3
如何排序变成
1.1
1.1.1.1.1.1
1.2.1
1.3
1.10.1
1.10.2
1.10.10

[IEEE_China]

蛋贴

[吉普赛的歌]

USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
	struct VARCHAR(50)	
)
GO
INSERT INTO t
SELECT '1.1'
UNION ALL SELECT '1.2.1'
UNION ALL SELECT '1.10.1' 
UNION ALL SELECT '1.10.2'
UNION ALL SELECT '1.10.10' 
UNION ALL SELECT '1.1.1.1.1.1'
UNION ALL SELECT '1.3'
GO
---以上为测试表及测试数据

--增加函数
IF OBJECT_ID('dbo.Fun_AlignChar') IS NOT NULL DROP FUNCTION dbo.Fun_AlignChar
GO
-- =============================================
-- Author:		yenange
-- Create date: 2018-08-08
-- Description:	将聚合字符串补齐位数
-- =============================================
CREATE FUNCTION dbo.Fun_AlignChar
(
	@str VARCHAR(200),@splitChar VARCHAR(20), @singleLen INT
)
RETURNS VARCHAR(400)
AS
BEGIN
	DECLARE @r VARCHAR(400)
	DECLARE @xml XML;
	SET @xml = CONVERT(XML, '<x><![CDATA[' + replace(CONVERT(VARCHAR(MAX), @str), @splitChar, ']]></x><x><![CDATA[') + ']]></x>')
 
	SET @r = STUFF(
		(SELECT  '.'+REPLICATE('0',@singleLen-LEN(item))+item
			FROM   (SELECT c.value('text()[1]', 'nvarchar(4000)') [item]
					FROM   @xml.nodes('/x') t(c)) t
			WHERE  item IS NOT NULL
			FOR XML PATH('')
		),1,1,'')

	RETURN @r;
END
GO

--查询
SELECT * FROM t 
ORDER BY dbo.Fun_AlignChar(struct,'.',5)
/*
struct
1.1
1.1.1.1.1.1
1.2.1
1.3
1.10.1
1.10.2
1.10.10
*/