两种方法表示对字段的条件限制:
第一种:select ID,(case when 条件一 and 条件二 then 0 when 条件三 and 条件四 then 1 else 2 end) as count,name from tablename
第二种:select ID,count = (case when 条件一 and 条件二 then 0 when 条件三 and 条件四 then 1 else 2 end),name from tablename
下文来自:http://www.cnblogs.com/kevin2013/archive/2010/07/02/1769682.html
1
2
3
|
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) |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
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 |
下文来自:http://blog.youkuaiyun.com/wuxiaokaixinguo/article/details/8895187
SQLServer2005 CASE WHEN的两种用法
第一种:普通的CASE 函数
case sex
when '1' then '男'
when '2' then '女'
else '人妖'
end as sex,
经典案例
select Number,name,sex,levelNO from TEST;
使用普通的CASE函数
第二种:CASE 搜索函数
case
when sex='1' then '男'
when sex='2' then '女'
else '人妖'
end as sex,
经典案例
select Number,name,sex,levelNO from TEST;
使用CASE搜索函数
到此结束!!!