case when then...else end sql 语句用法

select f.AGENTID as 'ID',
          f.AGENTNAME as '姓名',
          f.COMNAME as '公司简称',
          c.REGISTDATE as '注册时间',
          c.VALIDDATE as '截至时间',
          c.DISTRICT as '区县',
          c.COMAREA as '负责区域',

          c.category as '等级',
         case when  c.agentcardflag = 1 then '待审核'
                 when c.agentcardflag = 2 then '审核通过'
                 when c.agentcardflag = 3 then '未通过'
                 else '未通过'
                 end as '身份审核状态'
from  MagentInf f ,MagentCon c where c.AGENTID = f.AGENTID and c.Ispay = 0

 

下面为转载

 

SELECT

CASE WHEN USERID=10 THEN '社区用户：'+SELECT NAME FROM(SELECT NAME FROM USER_INFO UI WHERE UI.[ID]=A.USERID)
    WHEN USERID=20 THEN '注册用户：'+SELECT NAME FROM(SELECT NAME FROM REGISTER_INFO UI WHERE UI.[ID]=A.USERID)
    WHEN UERID=30 THEN '普通用户：'+SELECT NAME FROM(SELECT NAME FROM SIMPLE_INFO UI WHERE UI.[ID]=A.USERID)
    --.....还有好几个WHEN
    ELSE '' END,
    A.TITLE,A.TETENT
FROM BASICINFO A