2,507
社区成员




create table tb(id int, ItemNO varchar(16))
insert tb select 1, '1.4.6.4'
union all select 1, '1.4.6.5'
union all select 1, '1.4.6.2'
union all select 1, '1.4.6.13'
union all select 1, '1.4.6.3.9'
union all select 1, '1.4.6.3.10'
union all select 1, '1.4.6.3.11'
union all select 1, '10'
union all select 1, '11'
union all select 1, '2'
union all select 1, '2.1'
union all select 1, '2.1.1'
go
create function fn_Hash(@ItemNO varchar(16))
returns bigint as
begin
declare @result bigint, @multiple int
select @result=0, @multiple=4
while charindex('.', @ItemNO)>0
begin
select @result=@result+left(@ItemNO, charindex('.', @ItemNO)-1)*power(100,@multiple)
, @ItemNO = right(@ItemNO, len(@ItemNO)-charindex('.', @ItemNO))
,@multiple=@multiple-1
end
select @result=@result+@ItemNO*power(100,@multiple)
return @result
end
go
select * from tb
order by dbo.fn_Hash(ItemNO)
/*
id ItemNO
----------- ----------------
1 1.4.6.2
1 1.4.6.3.9
1 1.4.6.3.10
1 1.4.6.3.11
1 1.4.6.4
1 1.4.6.5
1 1.4.6.13
1 2
1 2.1
1 2.1.1
1 10
1 11
(12 row(s) affected)
*/
drop function dbo.fn_Hash
drop table tb
create table tb(id int, ItemNO varchar(32))
insert tb select 1, '1.4.6.4'
union all select 1, '1.4.6.5'
union all select 1, '10'
union all select 1, '11'
union all select 1, '2'
union all select 1, '2.1'
union all select 1, '2.1.1'
select * from tb
order by cast(substring(ItemNO, 1, charindex('.', ItemNO+'.')-1) as int), ItemNO
/*
id ItemNO
----------- --------------------------------
1 1.4.6.4
1 1.4.6.5
1 2
1 2.1
1 2.1.1
1 10
1 11
(7 row(s) affected)
*/
drop table tb