Select top 100 State,JoinState, (case when State=1 and Joinstate=0 then 2 when State=1 and JoinState=1 then 1 else 0 end)
as usestate from UserInfo (2) select ID,Username,namer=(case when(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