急！排序问题

Question

只能写一个函数来转换你的（一、三、七、十三）了
或者建议你用1队、3队、7队、13队
这样的话就可以这样写语句
select * from 表 order by cast(left(队名,len(队名)-1) int)

Answer 1

select * from 表 order by cast(left(队名,len(队名)-1) int)
执行以上语句时，提醒int附近有错误！
能对cast(left(队名,len(队名)-1) int)语句解释一下吗？

Answer 2

能对cast(left(队名,len(队名)-1) int)语句解释一下吗？
少写了一个as,应该是
cast(left(队名,len(队名)-1) as int)

Answer 3

--建立函数
create function getint(@Ds nvarchar(10))
returns int
as
begin
  declare @i int
  select @i=CASE RIGHT(RTRIM(@Ds),1) 
                                   WHEN '一' THEN 1
                                   WHEN '二' THEN 2
                                WHEN '三' THEN 3
                                WHEN '四' THEN 4
                                WHEN '五' THEN 5
                                WHEN '六' THEN 6
                                WHEN '七' THEN 7
                                   WHEN '八' THEN 8
                                   WHEN '九' THEN 9 END
  select @i=@i+(len(@Ds)-1)*10
  return @i
end
--建立测试数据
create table 表 (序号 int,队名 nvarchar(10))
insert into 表 values(1,'一队')
insert into 表 values(1,'十三队')
insert into 表 values(1,'三队')
insert into 表 values(1,'七队')
select * from 表--调用得到结果
select * from 表 order by dbo.getint(left(队名,len(队名)-1))

Answer 4

create function getint(@Ds nvarchar(4))
returns int
as
begin
  declare @i int
  if (Len(@Ds) = 2)
  begin
    select @i=CASE Left(@Ds,1)
                                   WHEN '一' THEN 1
                                   WHEN '二' THEN 2
                                   WHEN '三' THEN 3
                                   WHEN '四' THEN 4
                                   WHEN '五' THEN 5
                                   WHEN '六' THEN 6
                                   WHEN '七' THEN 7
                                   WHEN '八' THEN 8
                                   WHEN '九' THEN 9
                                  END
  end else
  if (Len(@Ds) = 3) and (Left(@Ds,1) ='十')
  begin
    set @i=Case substring(@Ds,2,1)
                                   WHEN '一' THEN 11
                                   WHEN '二' THEN 12
                                   WHEN '三' THEN 13
                                   WHEN '四' THEN 14
                                   WHEN '五' THEN 15
                                   WHEN '六' THEN 16
                                   WHEN '七' THEN 17
                                   WHEN '八' THEN 18
                                   WHEN '九' THEN 19
                                  END
  end else
  if (Len(@Ds) = 4)
  begin
    set @i=(Case substring(@Ds,1,1)
                                   WHEN '一' THEN 10
                                   WHEN '二' THEN 20
                                   WHEN '三' THEN 30
                                   WHEN '四' THEN 40
                                   WHEN '五' THEN 50
                                   WHEN '六' THEN 60
                                   WHEN '七' THEN 70
                                   WHEN '八' THEN 80
                                   WHEN '九' THEN 90
                                  END ) +
             (Case substring(@Ds,3,1)
                                   WHEN '一' THEN 1
                                   WHEN '二' THEN 2
                                   WHEN '三' THEN 3
                                   WHEN '四' THEN 4
                                   WHEN '五' THEN 5
                                   WHEN '六' THEN 6
                                   WHEN '七' THEN 7
                                   WHEN '八' THEN 8
                                   WHEN '九' THEN 9
                                  END )
  end
 
  return @i
end--建立测试数据
create table 表 (序号 int,队名 nvarchar(10))
insert into 表 values(1,'一队')
insert into 表 values(1,'十三队')
insert into 表 values(1,'三队')
insert into 表 values(1,'七队')
select * from 表--调用得到结果
select * from 表 order by dbo.getint(队名)--以上函数只适用于1到99队

Answer 5

--建立函数，上面的函数有一点漏洞，这个函数可以处理到“九十九”
ALTER function getint(@Ds nvarchar(10))
returns int
as
begin
  declare @i int,@y int
  select @i=CASE RIGHT(RTRIM(@Ds),1) 
                                   WHEN '一' THEN 1
                                   WHEN '二' THEN 2
                                WHEN '三' THEN 3
                                WHEN '四' THEN 4
                                WHEN '五' THEN 5
                                WHEN '六' THEN 6
                                WHEN '七' THEN 7
                                   WHEN '八' THEN 8
                                   WHEN '九' THEN 9 
                                   WHEN '十' THEN 0 END
  if len(@ds)=3 or len(@ds)=2
  SELECT @y=CASE LEFT(RTRIM(@Ds),1) 
                                   WHEN '一' THEN 1
                                   WHEN '二' THEN 2
                                WHEN '三' THEN 3
                                WHEN '四' THEN 4
                                WHEN '五' THEN 5
                                WHEN '六' THEN 6
                                WHEN '七' THEN 7
                                   WHEN '八' THEN 8
                                   WHEN '九' THEN 9 
                                   END
  if len(@ds)=1 set @y=0 
  if left(@ds,1)='十' set @y=1  select @i=@i+@y*10
  if @ds='十' set @i=10
  return @i
end--建立测试数据
create table 表 (序号 int,队名 nvarchar(10))
insert into 表 values(1,'一队')
insert into 表 values(1,'十三队')
insert into 表 values(1,'二十队')
insert into 表 values(1,'十队')
insert into 表 values(1,'二十三队')
insert into 表 values(1,'五十三队')
insert into 表 values(1,'三队')
insert into 表 values(1,'七队')--调用得到结果
select dbo.getint(left(队名,len(队名)-1)),队名 from 表 order by dbo.getint(left(队名,len(队名)-1))--释放表
drop table 表

Answer 6

select * from tablename 
order by charindex(队名,'一队,三队,七队,十三队')