22,206
社区成员
发帖
与我相关
我的任务
分享
--> 还是来个2005的吧,不用函数,无限级,各级字符长度最长可达10个字符,不过需要一个id:
declare @T table (id int identity, f001 varchar(20))
insert into @T
select '1' union all
select '2' union all
select '3' union all
select '1.1' union all
select '2.3.10.10.13' union all
select '2.3.10.10.12' union all
select '2.3.10.10.11' union all
select '3.1' union all
select '3.2' union all
select '20.1' union all
select '1.1.2' union all
select '1.1.1' union all
select '1.1.3'
;
with t1 as
(
select
a.id,data=replicate('0', 10-len(b.data))+b.data --> 每级长度10还不够,这里再增加前导0
from
(select id,data = convert(xml, '<root><v>' + replace(f001, '.', '</v><v>') + '</v></root>') from @T ) a
outer apply
(select data = N.v.value('.', 'varchar(100)') from a.data.nodes('/root/v') N(v)) b
)
select
* -- a.f001
from
@T a
outer apply
(select data = stuff((select '.' + data as [text()] from t1 where id = a.id for xml path('')), 1, 1, '')) b
order by
b.data
/*
id f001 data
----------- -------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 1 0000000001
4 1.1 0000000001.0000000001
12 1.1.1 0000000001.0000000001.0000000001
11 1.1.2 0000000001.0000000001.0000000002
13 1.1.3 0000000001.0000000001.0000000003
2 2 0000000002
7 2.3.10.10.11 0000000002.0000000003.0000000010.0000000010.0000000011
6 2.3.10.10.12 0000000002.0000000003.0000000010.0000000010.0000000012
5 2.3.10.10.13 0000000002.0000000003.0000000010.0000000010.0000000013
3 3 0000000003
8 3.1 0000000003.0000000001
9 3.2 0000000003.0000000002
10 20.1 0000000020.0000000001
*/
CREATE TABLE tb (ID INT IDENTITY(1,1),Code VARCHAR(20))
GO
INSERT tb SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT '1.1'
UNION ALL SELECT '2.1'
UNION ALL SELECT '131.2.1.11.1.131111'
UNION ALL SELECT '2.2'
UNION ALL SELECT '121.2.1.1.1.131111'
UNION ALL SELECT '3.1'
UNION ALL SELECT '3.2'
UNION ALL SELECT '20.1'
UNION ALL SELECT '1.1.1'
UNION ALL SELECT '1.1.2'
UNION ALL SELECT '1.1.3'
UNION ALL SELECT '1.1.11.1'
UNION ALL SELECT '1.1.2.1'
UNION ALL SELECT '2.1.2.1'
GO
CREATE FUNCTION dbo.ReplaceByPosition
(
@s1 VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @perPositionSTR VARCHAR(100),@i INT,@tmpSTR VARCHAR(8000)
SELECT @i=0,@tmpSTR = ''
WHILE CHARINDEX('.',@s1)>0
BEGIN
SELECT @perPositionSTR = LEFT(@s1,CHARINDEX('.',@s1)-1),@tmpSTR=@tmpSTR + RIGHT('0000000000' + @perPositionSTR,10),@s1 = STUFF(@s1,1,CHARINDEX('.',@s1),'')
END
SET @tmpSTR=@tmpSTR + RIGHT('0000000000' + @perPositionSTR,10)
RETURN @tmpSTR
END
GO
SELECT * FROM tb
ORDER BY dbo.ReplaceByPosition(Code)
GO
DROP FUNCTION ReplaceByPosition
GO
DROP TABLE tb
GO