create table mytb (a varchar(10))insert into mytb values('1-1')
insert into mytb values('1-1-1')
insert into mytb values('1-1-2')
insert into mytb values('10-1-1')
insert into mytb values('11-1')
insert into mytb values('11-1-1')
insert into mytb values('12-1')
insert into mytb values('12-1-2')
insert into mytb values('1-1-3')
insert into mytb values('1-2')
insert into mytb values('1-3')
insert into mytb values('1-3-2')
insert into mytb values('10-1')
create function subvalue( @str varchar(20),@num int)
returns varchar(5)
as
begin
declare @i int
declare @s varchar(5)
declare @flag int
declare @c varchar(1)
set @flag=1
set @i=1
set @s=''
while @i<=len(@str)
begin
set @c=substring(@str,@i,1)
if @c='-'
begin
set @flag=@flag+1
end
if @flag>@num
break
if (@flag=@num and @c<>'-')
begin
set @s=@s +@c
end
set @i=@i+1
end
return @s
end
select *
from mytb
order by cast(dbo.subvalue(a,1) as int)*1000000 +cast(dbo.subvalue(a,2) as int)*1000+cast(dbo.subvalue(a,3) as int)
insert into mytb values('1-1-1')
insert into mytb values('1-1-2')
insert into mytb values('10-1-1')
insert into mytb values('11-1')
insert into mytb values('11-1-1')
insert into mytb values('12-1')
insert into mytb values('12-1-2')
insert into mytb values('1-1-3')
insert into mytb values('1-2')
insert into mytb values('1-3')
insert into mytb values('1-3-2')
insert into mytb values('10-1')
create function subvalue( @str varchar(20),@num int)
returns varchar(5)
as
begin
declare @i int
declare @s varchar(5)
declare @flag int
declare @c varchar(1)
set @flag=1
set @i=1
set @s=''
while @i<=len(@str)
begin
set @c=substring(@str,@i,1)
if @c='-'
begin
set @flag=@flag+1
end
if @flag>@num
break
if (@flag=@num and @c<>'-')
begin
set @s=@s +@c
end
set @i=@i+1
end
return @s
end
select *
from mytb
order by cast(dbo.subvalue(a,1) as int)*1000000 +cast(dbo.subvalue(a,2) as int)*1000+cast(dbo.subvalue(a,3) as int)
解决方案 »
- 请教一个存储过程!
- 急急急
- 多表组合查询带来的超大数据问题(求解)
- 请问有SQL的日志文件,但没做日志备份,能还原数据库到某个指定的时间吗?
- 一段 SQL 语句搞定“限制访问”?
- 如何用js访问sql server中的数据库???
- sql server中的linked server
- 如何将SQLserver的表字段设置成自动编号类型
- SQL Server 2000 单个数据库大小超过51G后无法插入数据的问题
- sqlserver left join on where 查询的速度在数据量较大的情况下非常的慢,求解决,求大神
- 请大家帮忙提个好的方案!这样的操作在SQL中要怎样处理最好!!
- WinXP安装了Sql2K个人版,无法启动MSDTC ?
/*
方法 二
*/
--比较实际一点 我又多加了数据做测试
create table mytb (a varchar(10))
insert into mytb values('1-1')
insert into mytb values('1-1-1')
insert into mytb values('1-1-2')
insert into mytb values('10-1-1')
insert into mytb values('111-1')
insert into mytb values('11-11-1')
insert into mytb values('12-1')
insert into mytb values('12-13-2')
insert into mytb values('1-12-3')
insert into mytb values('1-29')
insert into mytb values('1-3')
insert into mytb values('1-3-2')
insert into mytb values('10-1')drop function subvalue
create function subvalue( @str varchar(20),@num int)
returns varchar(5)
as
begin
declare @i int
declare @s varchar(5)
declare @flag int
declare @c varchar(1)
set @flag=1
set @i=1
set @s=''
while @i<=len(@str)
begin
set @c=substring(@str,@i,1)
if @c='-'
begin
set @flag=@flag+1
end
if @flag>@num
break
if (@flag=@num and @c<>'-')
begin
set @s=@s +@c
end
set @i=@i+1
end
set @s=replicate('0',5-len(@s))+@s
return @s
end
select *
from mytb
order by dbo.subvalue(a,1)+dbo.subvalue(a,2)+dbo.subvalue(a,3)a
----------
1-1
1-1-1
1-1-2
1-3
1-3-2
1-12-3
1-29
10-1
10-1-1
11-11-1
12-1
12-13-2
111-1(所影响的行数为 13 行)