select * from 你的表 order by (case when 职业 in('教师','医生') then 0 else 1 end) asc,职业;

------解决方案--------------------
SQL code

--> 测试数据：@T
declare @T table([姓名] varchar(1),[年龄] int,[职业] varchar(4))
insert @T
select 'A',20,'教师' union all
select 'B',22,'医生' union all
select 'C',20,'教师' union all
select 'D',21,'律师' union all
select 'E',23,'教师' union all
select 'F',24,'工人'

SELECT * FROM @T 
ORDER BY CASE [职业] WHEN '教师' THEN 1 WHEN '医生'  THEN 2 ELSE 3 END
/*
姓名   年龄          职业
---- ----------- ----
A    20          教师
C    20          教师
E    23          教师
B    22          医生
D    21          律师
F    24          工人
*/

------解决方案--------------------
SQL code

--> 测试数据：@T
declare @T table([姓名] varchar(1),[年龄] int,[职业] varchar(4))
insert @T
select 'A',20,'教师' union all
select 'B',22,'医生' union all
select 'C',20,'教师' union all
select 'D',21,'律师' union all
select 'E',23,'教师' union all
select 'F',24,'工人'

SELECT * FROM @T 
ORDER BY CHARINDEX([职业],'医生,教师') DESC

/*
姓名   年龄          职业
---- ----------- ----
A    20          教师
C    20          教师
E    23          教师
B    22          医生
D    21          律师
F    24          工人
*/

------解决方案--------------------
象这样按职业排序的,最好建一个职业排序表,
格式如下
职业id号,职业名称,职业排序号

查询时与此表关联对结果根据职业排序号进行排序

------解决方案--------------------
最好是在做一个排序的表。。因为你这个排序内容和顺序可变性很强

或者定义一个排序的字符串
SQL code
declare @o varchar(100)
set @o=',医生,教师,工人,学生,'
select * from tb order by charindex([职业],@o)

------解决方案--------------------
最好再建立一个职业排序的表，否则直接固化到sql语句中去很难维护而且可变性很差，什么时候你增加了一个主席的职业要排在最前面你只能修改sql语句才可以。如果有职业排序表的话只要给主席取个最大的（最小）的序号就可以了。