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

来源：互联网发布：种子下载软件知乎编辑：程序博客网时间：2024/05/30 23:05

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