case when 用法

Tag:case when select  

 

case when一般是放在select里面或者where里面的，用来动态生成某些字段用的。

 

1、看一个例子：

 

  select 
  (select domain_text_${def:locale} from t_domain 
  where namespace=(
  case when (${fld:ticketcate}='0' )then 'Inquiry_Category1'
   when (${fld:ticketcate}='1' )then 'Challenge_Category1'
   when (${fld:ticketcate}='2' )then 'Advice_Category1'
  else '' end)
  and domain_value = tc.category1) as category1

from table_a

 

 

2、case when甚至更强大，可以在里面嵌套SELECT语句，看一个例子：

 

select 
		case when nvl((select isshowdownload from t_staff where userlogin='admin002'),'')='1' then 
		'<input type="button" name="downloadbtn" onclick="downloadthisticket(this)" value="下载" class="cmd" style="display: inline;"  >'
		else 
		'<input type="button" name="downloadbtn" onclick="downloadthisticket(this)" value="下载" class="cmd" style="display: inline;" disabled >'
		end as download,
		cc_cue_inquiry.createdby as createdby,cc_cue_inquiry.created as created,cc_cue_inquiry.contact as contact,cc_cue_inquiry.subdealtime as subdealtime,(select d.domain_text_cn from t_domain d where d.namespace='TICKETCATEGORY' and d.domain_value = cc_cue_inquiry.ticketcategory limit 1) as ticketcategory,cc_cue_inquiry.tuid as tuid,(select d.domain_text_cn from t_domain d where d.namespace='CUEDealStatus' and d.domain_value = cc_cue_inquiry.deal_status limit 1) as deal_status
		
from	     

	${table}

 

 3、如果有一个需求，当字段A包含中国这2个字时，将其显示为中国万岁，否则按原样输出，写法：

 

SELECT  
case when LOCATE('bankCardNumber',request)>0 then SUBSTR(request,LOCATE('bankCardNumber',request)+15,19)
when LOCATE('pan',request)>0 then SUBSTR(request,LOCATE('pan',request)+4,16)
when LOCATE('cardNo',request)>0 then SUBSTR(request,LOCATE('cardNo',request)+7,19)
end
as '卡号'
,operator as '绑卡者',operate_time as '绑卡时间',
case when result_msg is null then '绑卡成功'
else result_msg end
 as '绑卡结果'
  FROM tb_pay_record_log WHERE operator IN(
'吴桥'
)

 观察代码中的else 后面的result_msg，直接写原字段名，这样就可以将它原样输出了。

 

 

refurl:http://www.cnblogs.com/traceman/archive/2007/03/14/673954.html