博客

1.case  when 的用法

（1）简单Case函数

CASE sex

WHEN ‘1’ THEN ‘男’

WHEN ‘2’ THEN ‘女’

ELSE ‘其他’

END

（2）–Case搜索函数

CASE WHEN sex = ‘1’ THEN ‘男’

WHEN sex = ‘2’ THEN ‘女’

ELSE ‘其他’

END

2.case  when 代码

1.case when 用在取字段名上
select id,username
(case when f.answerer is not null
 then 1 
 else 0
 end) as isAnswer f
from user

2.根据区间进行选择值
select case when type in (1,2) then 1
	when type in (3,4) then 2 
	when type in (5,6,7,8) then 3
	when type in (9,10,11,12,13,14,15) then 4 
	when type in (16,17,18) then 5
	when type in (19,20,21,22,23,24,25) then 6
	when type in (26,27,28,29) then 7
	when type in (30,31) then 8 
	when type in (32) then 9 else 9 end type,userID
from plat_teacher_BonusPoints_record with(nolock)
where  cwareID=#cwareID# and status=1

3.isEqual 的用法（用在where查询条件下）

<isEqual prepend="and" property="selectTime" compareValue="1">
        datediff(week,createTime,getdate())=0
</isEqual>
<isEqual prepend="and" property="selectTime" compareValue="2">
        datediff(month,createTime,GETDATE())=0
</isEqual>