case when then

select     ID,Username,namer=(casewhen(score<='50')    then'实习'

 

                                    when(score>'50' and  score<='500')   then '赤脚医生'    

 

                                    when(score>'500' and score<='1000')   then '村卫生员' 

 

                                    when(score>'1000' and score<='1500')   then '乡卫生员' 

 

                                    when(score>'1500' and score<='2000')   then '镇卫生员'

 

                                    when(score>'2000' and score<='3000')   then '医师'

 

                                    when(score>'3000' and score<='5000')   then '主治医师'

 

                                    when(score>'5000' and score<='10000')   then '副主任医师'

 

                                    when(score>'10000' and score<='20000')   then '主任医师'

 

                                    when(score>'20000' and score<='50000')   then '健康大使'

 

                                    else  '健康大使'  end ), (SELECT count(id)

 

         FROM  jk01_YiWen_Question 

 

         WHERE  UserID =  dbo.jk01_Member.ID) as  questionnum 

 

   from jk01_Member

原文自：http://www.cnblogs.com/kevin2013/archive/2010/07/02/1769682.html