两种方法表示对字段的条件限制:
第一种: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.csdn.net/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搜索函数
到此结束!!!