请教排序方面的问题?

Question

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

Answer 1

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

Answer 2

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

Answer 3

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)

Answer 4

我也有这种要求,但希望有通用解法,因为,假如有表
Name    address
   张三    花园新村2幢
   李四    花园新村14幢
   王五    花园新村1幢
   王七    舒台村8幢
   刘流    解放新别墅7幢1号即,要求先按地址排序,再按幢数排

Answer 5

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)
   

Answer 6

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)

Answer 7

稍微修改一下：
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)

Answer 8

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

Answer 9

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 c2,
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)

Answer 10

2,更正:
declare @a varchar(200),@allNum varchar(20),@i int
set @a='花园新村96614423幢'
set @allNum=''
set @i=0select @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)

Answer 11

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

Answer 12

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

Answer 13

有一个表如下   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 

Answer 14

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))

Answer 15

select * from table1 order by
 Substring( address,1,len(Address)-1- len(casewhen  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 byconvert(int,casewhen  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)