请教排序方面的问题?

[huwheel]

求教各位大虾

有一个表如下

Name address
张三 花园新村2幢
李四 花园新村14幢
王五 花园新村1幢


如何写一个查询,返回的记录集按幢前面的数值,从小大到排

应返回如下:

Name address
王五 花园新村1幢
张三 花园新村2幢
李四 花园新村14幢

[yujohny]

select Name,address from 表 order by left(right(address,3),2)

[愉快的登山者]

select * from table1
order by cast(substring(address, 5, len(address) - 6) as int)

[yujohny]

select Name,address from 表 order by cast(left(right(address,3),2) as int)

[yujohny]

select Name,address from 表 order by
cast(SUBSTRING(SUBSTRING(address, 5,len(address)-4),1,len(SUBSTRING(address,5,len(address )-4))-1) as int)

[伍子V5]

我觉得大家的方法有欠妥善吧
楼主提的是幢前面的数字
如果人家的记录改成天路园1幢，天路园2幢，好象就不好使了

[huwheel]

小区地址的长度是不固定的,关键有什么方法把幢前的数值提出来

[eminena]

我也有这种要求,但希望有通用解法,因为,假如有表


Name address
张三 花园新村2幢
李四 花园新村14幢
王五 花园新村1幢
王七 舒台村8幢
刘流 解放新别墅7幢1号

即,要求先按地址排序,再按幢数排

[dafu71]

declare @tb table([Name] varchar(10), address varchar(50))
insert @tb values('张三','花园新村2幢')
insert @tb values('李四','花园新村14幢')
insert @tb values('王五','花园新村1幢')

select * from @tb order by cast(right(replace(address,'幢',''),len(address)-5) as int)

[happydreamer]

CREATE function getnumber(@a Nvarchar(100))
returns Nvarchar(2000)
as
begin
declare @i int,@len int,@b Nvarchar(100)
set @i=1
set @b=''
set @len=len(@a)
while @i<=@len
begin
if substring(@a,@i,1) like '[0-9]'
set @b=@b+substring(@a,@i,1)
set @i=@i+1
end
return @b
end


select * from table
order by address,dbo.getnumber(address)

[yujohny]

稍微修改一下：
CREATE function getnumber(@a Nvarchar(100))
returns int
as
begin
declare @i int,@len int,@b Nvarchar(100)
set @i=1
set @b=''
set @len=len(@a)
while @i<=@len
begin
if substring(@a,@i,1) like '[0-9]'
set @b=@b+substring(@a,@i,1)
set @i=@i+1
end
return cast(@b,int)
end


select name,address from table
order by dbo.getnumber(address)

[huwheel]

sql server 7.0 下 可以用户自定义函数?

[CrazyFor]

7.0不能用自定义函数

可以用临时表,加一个ORDERCOL,把这些数值都更新到这个列上,再查询排序

更新参考:

1，建序数表
select top 8000 identity(int,1,1) as N into numtab from
(select top 100 id=1 from sysobjects) as a,
(select top 100 id=1 from sysobjects) as b,
(select top 100 id=1 from sysobjects) as c

2,
declare @a varchar(200),@allNum varchar(20)
set @a='花园新村123幢'
set @allNum=''

select @allNum=@allNum+substring(REVERSE(Address),charindex('幢',REVERSE(Address))+b.n,1)
from (select @a Address)a left join numtab b on substring(REVERSE(Address),charindex('幢',REVERSE(Address))+b.n,1) like '[0-9]'
select reverse( @allNum)

[CrazyFor]

2,更正:
declare @a varchar(200),@allNum varchar(20),@i int
set @a='花园新村96614423幢'
set @allNum=''
set @i=0

select @allNum=case when @i=0 then @allNum+substring(REVERSE(Address),charindex('幢',REVERSE(Address))+b.n,1) else @allNum end
,@i=case when substring(REVERSE(Address),charindex('幢',REVERSE(Address))+b.n,1) not like '[0-9]' then 0 else @i end
from (select @a Address)a left join numtab b on substring(REVERSE(Address),charindex('幢',REVERSE(Address))+b.n,1) like '[0-9]'
select reverse( @allNum)

[fjw2002]

select Name,address from table order by right(address,2)

[runsoft]

把这个号码放在保存到数字类型的字段中，再根据数字类型sort

[huwheel]

有一个表如下

Name address
张三 花园新村2幢
李四 花园新村14幢
王五 花园新村1幢
马六 CSDN134幢

用一条sql语句:



select * from table1 order by

substring(address,1,len(address)-1-len(case

When SubString(substring(address,charindex(right(address,1),address)-4,4 ),1,1)>='0'and SubString(substring(address,charindex(right(address,1),address)-4,4 ),1,1)<='9' then cast(SubString(substring(address,charindex(right(address,1),address)-4,4 ),1,1) as int)*1000

else 0

End +

case

When SubString(substring(address,charindex(right(address,1),address)-4,4 ),2,1)>='0'and SubString(substring(address,charindex(right(address,1),address)-4,4 ),2,1)<='9' then cast(SubString(substring(address,charindex(right(address,1),address)-4,4 ),2,1) as int)*100

else 0

End +

case

When SubString(substring(address,charindex(right(address,1),address)-4,4 ),3,1)>='0'and SubString(substring(address,charindex(right(address,1),address)-4,4 ),3,1)<='9' then cast(SubString(substring(address,charindex(right(address,1),address)-4,4 ),3,1) as int)*10

else 0

End +

case

When SubString(substring(address,charindex(right(address,1),address)-4,4 ),4,1)>='0'and SubString(substring(address,charindex(right(address,1),address)-4,4 ),4,1)<='9' then cast(SubString(substring(address,charindex(right(address,1),address)-4,4 ),4,1) as int)

else 0

End )),

case

When SubString(substring(address,charindex(right(address,1),address)-4,4 ),1,1)>='0'and SubString(substring(address,charindex(right(address,1),address)-4,4 ),1,1)<='9' then cast(SubString(substring(address,charindex(right(address,1),address)-4,4 ),1,1) as int)*1000

else 0

End +

case

When SubString(substring(address,charindex(right(address,1),address)-4,4 ),2,1)>='0'and SubString(substring(address,charindex(right(address,1),address)-4,4 ),2,1)<='9' then cast(SubString(substring(address,charindex(right(address,1),address)-4,4 ),2,1) as int)*100

else 0

End +

case

When SubString(substring(address,charindex(right(address,1),address)-4,4 ),3,1)>='0'and SubString(substring(address,charindex(right(address,1),address)-4,4 ),3,1)<='9' then cast(SubString(substring(address,charindex(right(address,1),address)-4,4 ),3,1) as int)*10

else 0

End +

case

When SubString(substring(address,charindex(right(address,1),address)-4,4 ),4,1)>='0'and SubString(substring(address,charindex(right(address,1),address)-4,4 ),4,1)<='9' then cast(SubString(substring(address,charindex(right(address,1),address)-4,4 ),4,1) as int)

else 0

End

[aierong]

declare @n table (address varchar(100))
insert into @n values('aa1')
insert into @n values('aa4')
insert into @n values('aa3')
insert into @n values('aa11')

select * from @n
order by convert(int,substring(address,PATINDEX('%[1-9]%',address),len(address)-1))

[huwheel]

select * from table1 order by


Substring( address,1,len(Address)-1- len(case

when Patindex('%[0-9][0-9][0-9][0-9]'+SubString(Address,len(address),1),address)<>0 then Substring(Address,Patindex('%[0-9][0-9][0-9][0-9]'+SubString(Address,len(address),1),address),4)

when Patindex('%[0-9][0-9][0-9]'+SubString(Address,len(address),1),address)<>0 then Substring(Address,Patindex('%[0-9][0-9][0-9]'+SubString(Address,len(address),1),address),3)

when Patindex('%[0-9][0-9]'+SubString(Address,len(address),1),address)<>0 then Substring(Address,Patindex('%[0-9][0-9]'+SubString(Address,len(address),1),address),2)

when Patindex('%[0-9]'+SubString(Address,len(address),1),address)<>0 then Substring(Address,Patindex('%[0-9]'+SubString(Address,len(address),1),address),1)

end)),

--select * from table1 order by

convert(int,case

when Patindex('%[0-9][0-9][0-9][0-9]'+SubString(Address,len(address),1),address)<>0 then Substring(Address,Patindex('%[0-9][0-9][0-9][0-9]'+SubString(Address,len(address),1),address),4)

when Patindex('%[0-9][0-9][0-9]'+SubString(Address,len(address),1),address)<>0 then Substring(Address,Patindex('%[0-9][0-9][0-9]'+SubString(Address,len(address),1),address),3)

when Patindex('%[0-9][0-9]'+SubString(Address,len(address),1),address)<>0 then Substring(Address,Patindex('%[0-9][0-9]'+SubString(Address,len(address),1),address),2)

when Patindex('%[0-9]'+SubString(Address,len(address),1),address)<>0 then Substring(Address,Patindex('%[0-9]'+SubString(Address,len(address),1),address),1)

end)