create table #t(Name varchar(100), Class varchar(100), grade int)
go
insert into #t(Name, Class, grade)
select '张二','一班',20 union all
select '赵二','三班',20 union all
select '李一','二班',30 union all
select '张四','一班',0 union all
select '李四','二班',0 union all
select '张三','一班',10 union all
select '李二','二班',20 union all
select '赵三','三班',10 union all
select '张一','一班',30 union all
select '李三','二班',10 union all
select '赵四','三班',0 union all
select '赵一','三班',30--这里如果有很多班,需要写一个函数:从大写转换为数字
select * from #t
where grade <> 0
order by case left(Class,1) when '一' then 1 when '二' then 2 when '三' then 3 end asc, grade descdrop table #t
go
insert into #t(Name, Class, grade)
select '张二','一班',20 union all
select '赵二','三班',20 union all
select '李一','二班',30 union all
select '张四','一班',0 union all
select '李四','二班',0 union all
select '张三','一班',10 union all
select '李二','二班',20 union all
select '赵三','三班',10 union all
select '张一','一班',30 union all
select '李三','二班',10 union all
select '赵四','三班',0 union all
select '赵一','三班',30--这里如果有很多班,需要写一个函数:从大写转换为数字
select * from #t
where grade <> 0
order by case left(Class,1) when '一' then 1 when '二' then 2 when '三' then 3 end asc, grade descdrop table #t
go
insert into #t(Name, Class, grade)
select '张二','一班',20 union all
select '赵二','三班',20 union all
select '李一','二班',30 union all
select '张四','一班',0 union all
select '李四','二班',0 union all
select '张三','一班',10 union all
select '李二','二班',20 union all
select '赵三','三班',10 union all
select '张一','一班',30 union all
select '李三','二班',10 union all
select '赵四','三班',0 union all
select '赵一','三班',30
select * from #t where grade<>0
order by
case when charindex('一',Class) > 0 then 1
when charindex('二',Class) > 0 then 2
when charindex('三',Class) > 0 then 3
end,
case when charindex('一',Name) > 0 then 1
when charindex('二',Name) > 0 then 2
when charindex('三',Name) > 0 then 3
end
Name(字符串) grade(整型)
李四 1
李四 2
张三 3
王二 4
赵五 5
李四 6
赵五 3
王二 2
张三 2
李四 1
Name(字符串) grade(整型)
李四 6
李四 2
李四 1
李四 1
张三 3
张三 2
王二 4
王二 2
赵五 5
赵五 3