22,207
社区成员
发帖
与我相关
我的任务
分享
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.ReplaceCode(@code VARCHAR(20))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @re VARCHAR(8000),@tempStr VARCHAR(100)
SELECT @re='',@tempStr=''
WHILE CHARINDEX('.',@code)>0
BEGIN
SELECT @tempStr=LEFT(@code,CHARINDEX('.',@code)-1),
@re=@re+RIGHT('0000000000'+@tempStr,10),
@code=STUFF(@code,1,CHARINDEX('.',@code),'')
END
IF LEN(@code)>0
SET @re=@re+RIGHT('0000000000'+@code,10)
RETURN @re
END
GO
SELECT *
FROM tb
ORDER BY dbo.ReplaceCode(code)
DROP TABLE tb
DROP FUNCTION ReplaceCode
create table #t (id varchar(100))
insert into #t
select '1' union all
select '1.1' union all
select '1.1.1' union all
select '1.1.2'union all
select '1.2'union all
select '1.2.1'union all
select '1.2.2'union all
select '1.2.3'union all
select '2'union all
select '2.1'union all
select '2.1.1'union all
select '2.1.1.1'
select * from #t order by id desc
/*2.1.1.1
2.1.1
2.1
2
1.2.3
1.2.2
1.2.1
1.2
1.1.2
1.1.1
1.1
1*/
select * from #t order by id asc
/*1
1.1
1.1.1
1.1.2
1.2
1.2.1
1.2.2
1.2.3
2
2.1
2.1.1
2.1.1.1*/