数据库有如下字段:
wbsid
1.1
1.1.1
1.1.2
1.1.3
1.3
1.3.1
1.3.2
1.2
1.2.1
1.2.2
1.2.3
1.4
1.4.1
1.4.2 问:
如何实现排序?
1.1
1.1.1
1.1.2
1.1.3
1.2
1.2.1
1.2.2
1.2.3
1.3
1.3.1
1.3.2
1.4
1.4.1
1.4.2
wbsid
1.1
1.1.1
1.1.2
1.1.3
1.3
1.3.1
1.3.2
1.2
1.2.1
1.2.2
1.2.3
1.4
1.4.1
1.4.2 问:
如何实现排序?
1.1
1.1.1
1.1.2
1.1.3
1.2
1.2.1
1.2.2
1.2.3
1.3
1.3.1
1.3.2
1.4
1.4.1
1.4.2
order by wbsid
insert into tb values('1.1')
insert into tb values('1.1.1')
insert into tb values('1.1.2')
insert into tb values('1.1.3')
insert into tb values('1.3')
insert into tb values('1.3.1')
insert into tb values('1.3.2')
insert into tb values('1.2')
insert into tb values('1.2.1')
insert into tb values('1.2.2')
insert into tb values('1.2.3')
insert into tb values('1.4')
insert into tb values('1.4.1')
insert into tb values('1.4.2')
goselect * from tb
order by cast(parsename(reverse(wbsid),1) as int),
cast(parsename(reverse(wbsid),2) as int),
cast(parsename(reverse(wbsid),3) as int)drop table tb /*
wbsid
----------
1.1
1.1.1
1.1.2
1.1.3
1.2
1.2.1
1.2.2
1.2.3
1.3
1.3.1
1.3.2
1.4
1.4.1
1.4.2(所影响的行数为 14 行)*/
wbsid
1.1
1.1.1
1.1.2
1.1.3
1.3
1.3.1
1.3.2
1.2
1.2.1
1.2.2
1.2.3
1.4
1.4.1
1.4.2 问:
如何实现排序?
1.1
1.2
1.3
1.4
1.1.1
1.1.2
1.1.3
1.1.4
1.2.1
1.2.2
1.2.3
1.2.4
1.3.1
1.3.2
1.3.3
1.4.1
1.4.2 不好意思,再发一次
insert @t select '1.1'
insert @t select '1.1.1 '
insert @t select '1.1.2 '
insert @t select '1.1.3 '
insert @t select '1.2'
insert @t select '1.2.1 '
insert @t select '1.2.2 '
insert @t select '1.2.3 '
insert @t select '1.4'
insert @t select '1.4.1 '
insert @t select '1.4.2 '
insert @t select '1.3'
insert @t select '1.3.1 '
insert @t select '1.3.2 'select * from @t order by wbsidwbsid
----------
1.1
1.1.1
1.1.2
1.1.3
1.2
1.2.1
1.2.2
1.2.3
1.3
1.3.1
1.3.2
1.4
1.4.1
1.4.2 (14 行受影响)正常order by 就行了.
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([wbsid] varchar(5))
insert [tb]
select '1.1' union all
select '1.1.1' union all
select '1.1.2' union all
select '1.1.3' union all
select '1.3' union all
select '1.3.1' union all
select '1.3.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 '1.4' union all
select '1.4.1' union all
select '1.4.2'
---查询---
select *
from [tb]
order by cast(replace(wbsid,'.','') as int)---结果---
wbsid
-----
1.1
1.2
1.3
1.4
1.1.1
1.1.2
1.1.3
1.2.1
1.2.2
1.2.3
1.3.1
1.3.2
1.4.1
1.4.2(所影响的行数为 14 行)
declare @t table(wbsid varchar(10))
insert @t select '1.1'
insert @t select '1.1.1 '
insert @t select '1.1.2 '
insert @t select '1.1.3 '
insert @t select '1.2'
insert @t select '1.2.1 '
insert @t select '1.2.2 '
insert @t select '1.2.3 '
insert @t select '1.4'
insert @t select '1.4.1 '
insert @t select '1.4.2 '
insert @t select '1.3'
insert @t select '1.3.1 '
insert @t select '1.3.2 'select * from @t order by len(wbsid),wbsid
/*
wbsid
----------
1.1
1.2
1.3
1.4
1.1.1
1.1.2
1.1.3
1.2.1
1.2.2
1.2.3
1.3.1
1.3.2
1.4.1
1.4.2
*/