排序问题（二次发问）

Question

现在有点忙，先给你这个参考一下
CASE WHEN LEN(开数1) = 1 THEN 
                 CASE RTRIM(开数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 10 END
               WHEN LEN(开数1) = 2 THEN 
                 CASE RIGHT(RTRIM(开数1),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

Answer 1

create table ai (序号 int,   队名  varchar(100))
go
insert into ai select 1,       '一队'
insert into ai select 2,      '十三队'
insert into ai select 3,      '三队'
insert into ai select 4,      '七队'
goselect * 
from ai
order by charindex(队名,'一队,十三队,三队,七队')

Answer 2

--建立函数
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
--调用得到结果
select * from 表 order by dbo.getint(left(队名,len(队名)-1))

Answer 3

--建立函数，上面的函数有一点漏洞，这个函数可以处理到“九十九”
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 表