IsNull和SQL语句中CASEWHEN用法
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
IsNull和SQL语句中CAS E WHE N用法
1、ISNUL L
使用指定的替换值替换NULL。
语法
I SNULL ( che ck_ex press ion , repla cemen t_val ue )
参数
che ck_ex press ion
将被检查是否为N ULL的表达式。
ch eck_e xpres sion可以是任何类型的。
rep lacem ent_v alue
在c heck_expre ssion为 NUL L时将返回的表达式。
repla cemen t_val ue 必须与chec k_exp resss ion 具有相同的类型。
返回类型
返回与 ch eck_e xpres sion相同的类型。
注释
如果 che ck_ex press ion 不为 NULL，那么返回该表达式的值；否则返
回r eplac ement_valu e。
示例
A. 将ISNUL L 与 AV G 一起使用
下面的示例查找所有书的平均价格，用值$10.00 替换t itles表的 pr ice 列中的所有N ULL 条目。
USE pubs
GO
S ELECT AV G(ISN ULL(p rice, $10.00))
FRO M t itles
GO
下面是结果集：
--------------------------
14.24
(1 row(s) affec ted)
B. 使用ISNUL L
下面的示例为 titl es 表中的所有书选择书名、类型及价格。
如果一个书名的价格是NULL，那么在结果集中显示的价格为 0.00。
USE pu bs
GO
SELE CT SUBST RING(title, 1, 15) AS Title, t ype AS Type,
ISNUL L(pri ce, 0.00) A S P rice
FROM tit les
G O
下面是结果集：
Title Ty pe Pric e
--------------- ------------ --------------------------
T he Busy Exe cut busi ness19.99
Cooki ng with Co bus iness 11.95
You Can Co mbatb usine ss 2.99
S traig ht Talk A busi ness19.99
Silic on Valle y mod_cook19.99
The Gour met Mic mod_cook 2.99
The Ps ychol ogy UN DECID ED 0.00
But Is It Us er pop ular_comp 22.95
Sec rets of Sil i p opula r_com p 20.00
N et Etiqu ette popu lar_c omp 0.00
Com puter Ph obic psy cholo gy 21.59
I s A nger the En ps ychol ogy 10.95
Life Wit hout Fe psy cholo gy 7.00
Prolo nged Dat a psyc holog y 19.99
Em otion al Secur ps ychol ogy 7.99
Onio ns, Leek s, tra d_coo k 20.95
Fifty Ye ars in tr ad_co ok 11.95
Sush i, Anyon e? tra d_coo k 14.99
(18 row(s) affec ted)
2、项目中，联系人详细信息这一个页面中，联系人的性别在数据库字段的保存，男的用0表示，女的用1表示，所以，如果直接就读出来绑定到DataL ist中，性别就显示成0或1了。
所以需要将Sql语句进行改造。
先看看我原先的做法，这样
做，组长叫我重做了，要叫我要CAS E WHE N来写。
不用CAS E WHE N 的做法。
源代码如下：
s tring sql="sele ct *fromCONTA CTPER SON ,CUSTO MER w here
CONTA CTPER SONID='"+s trCon tacti d+"'
and C ONTAC TPERS ON.CU STOME RID=C USTOM ER.CU STOME RID";
Data Set d s=new Data Set();
ds=Commo n.Get DSCom mon(s ql);
i f(ds.Table s[0].Rows[0]["C ONTAC TPERS ONSEX"].To Strin g()=="0")
{
d s.Tab les[0].Row s[0]["CONT ACTPE RSONS EX"]="男";
}
el se
{
d s.Tab les[0].Row s[0]["CONT ACTPE RSONS EX"]="女";
}
使用C ASE W HEN做法。
strin g
sql="sel ect C USTOM ER.*,CONTA CTPER SON.C ONTAC TPERS ONNAM E,CON TAC TP ERSON.CONT ACTPE RSONB IRTHD AY,CO NTACT PERSO N.CON TACTP ERSONPOSIT ION,C ONTAC TPERS ON.CO NTACT PERSO NDEPT NAME,";
sql+="CON TACTP ERSON.TELE PHONE,CONT ACTPE RSON.MAIL,CONTA CTPER SON.C ITY,C ONTAC TPERS ON.PR OVINC E,CON TACTP ERSON.POST CODE,CONTACTPER SON.C OUNTR Y,CON TACTP ERSON.MEMO,";
sql+="CO NTACT PERSO N.DIR ECTOR,";
sq l+="c ase w hen C ONTAC TPERS ON.CO NTACT PERSO NSEX= '0'then'男
' w hen C ONTAC TPERS ON.CO NTACT PERSO NSEX= '1'then'女' e nd as
CONT ACTPE RSONS EX fr om CU STOME R ,CO NTACT PERSO N
wh ere C ONTAC TPERS ONID='"+st rCont actid+"' a nd
CO NTACT PERSO N.CUS TOMER ID=CU STOME R.CUS TOMER ID";
可以看出这样明显加长了，SQL语句，这样做有什么好处呢？
3、SQL条件控制（ca se wh en...then...els e...e nd）
我的语句：（SQL2000）
1:
s elect
ByeT ime,c ase w hen l en(By eTime)>0 t hen s tr(DA TEDIF F(day,Come InTim e,Bye Time)) els e DAT EDIFF(day,ComeI nTime,getd ate()) end as t est
f rom m yUser wher e Del Flag='0' a nd Us erID='cq'
2:
se lectByeTi me,ca se wh en le n(Bye Time)>0 th en
st r(DAT EDIFF(day,ComeI nTime,ByeT ime))+'(已离职)' e nd as test
from myUs er wh ere D elFla g='0' andUserI D='cq'
3:
selec t Bye Time,casewhenlen(B yeTim e)>0then
str(D ATEDI FF(da y,Com eInTi me,By eTime))+'(已离职)'
else DATE DIFF(day,C omeIn Time,getda te()) endas te st
fr om my Userwhere DelF lag='0' an d Use rID='cq'
为什么1和2 执行都没有问题,3 却报“将 va rchar值 '480(已离职)'转换为数据类型为i nt 的列时发生语法错误。
”
sel ect B yeTim e,cas e whe n len(ByeT ime)>0 the n
str(DATE DIFF(day,C omeIn Time,ByeTi me))+'(已离职)'
el se st r(DAT EDIFF(day,ComeI nTime,getd ate())) en d astest
frommyUse r whe re De lFlag='0'and U serID='cq'
这样试一下.语法应该没有问题
4、W HEN T HEN
W HEN T HEN
E LSE
E ND
--------------
selec t cou nt(*) as c t , C ASE
WHEN (Pro_stat e= 1) THEN '待审'
WH EN (P ro_st ate=2) TH EN '已审'
WHEN(Pro_state= 3)THEN'办理中'
WH EN (P ro_st ate=4) TH EN '已申退'
WHEN (Pro_stat e= 5) THEN '来信'
WH EN (P ro_st ate=6) TH EN '办理完成'
WHEN(Pro_state= 7)THEN'未予立案'
endas pr o_sta te
fr om vw_prop osals_quer y whe re 1=1 GRO UP BY pr o_sta te
结果：
ct pro-s tate
30 办理中
7来信
1 办理完成
5 未予立案
解读：
查询总数和pro_state当 Pr o_sta te= 1时以'待审' 替代表示
当Pro_s tate= 2 时以 '已审'替代表示
当 Pro_stat e= 3时以 '办理中' 替代表示
。
。
5、CA SE 可能是 SQL中被误用最多的关键字之一。
虽然你可能以前用过这个关键字来创建字段，但是它还具有更多用法。
例如，你可以在 WH ERE 子句中使用CASE。
首先让我们看一下 CASE的语法。
在一般的SELEC T 中，其语法如下：
SEL ECT <myCol umnSp ec> =
CAS E
WH EN <A> THE N <so methi ngA>
WHEN <B>THEN<some thing B>
E LSE <somet hingE>
EN D
在上面的代码中需要用具体的参数代替尖括号中的内容。
下面是一个简单的例子：USEpubs
GO
SELEC T
Tit le,
'Price Rang e' =
CASE
W HEN p riceIS NU LL TH EN 'U npric ed'
WH EN pr ice < 10 T HEN 'Barga in'
WH EN pr ice B ETWEE N 10and 20 THE N 'Av erage'
ELSE 'Gif t toimpre ss re lativ es'
E ND
F ROM t itles
ORD ER BY pric e
GO
这是CASE的典型用法，但是使用 CASE其实可以做更多的事情。
比方说下面
的G ROUPBY 子句中的 CA SE：
SELEC T 'Nu mberof Ti tles', Cou nt(*)
FRO M tit les
GROUP BY
C ASE
WH EN pr ice I S NUL L THE N 'Un price d'
WHE N pri ce <10 TH EN 'B argai n'
WHE N pri ce BE TWEEN 10 a nd 20 THEN 'Ave rage'
ELSE'Gift to i mpres s rel ative s'
EN D
GO
你甚至还可以组合这些选项，添加一个ORDER BY 子句，如下所示：
U SE pu bs
G O
SE LECT
CASE
W HEN p riceIS NU LL TH EN 'U npric ed'
WH EN pr ice < 10 T HEN 'Barga in'
WH EN pr ice B ETWEE N 10and 20 THE N 'Av erage'
ELSE 'Gif t toimpre ss re lativ es'
E ND AS Rang e,
Ti tle
FROMtitle s
GR OUP B Y
CAS E
WHEN pric e ISNULLTHEN'Unpr iced'
WHENprice < 10 THEN 'Bar gain'
WHENprice BETW EEN 10 and 20 T HEN 'Avera ge'
EL SE 'G ift t o imp ressrelat ives'
END,
Titl e
OR DER B Y
CAS E
WHEN pric e ISNULLTHEN'Unpr iced'
WHENprice < 10 THEN 'Bar gain'
WHENprice BETW EEN 10 and 20 T HEN 'Avera ge'
EL SE 'G ift t o imp ressrelat ives'
END,
Titl e
GO
注意，为了在G ROUPBY 块中使用 CA SE，查询语句需要在 GROU P BY块中重复SELEC T 块中的 CASE块。
除了选择自定义字段之外，在很多情况下C ASE 都非常有用。
再深入一步，你还可以得到你以前认为不可能得到的分组排序结果集。
。