发表于 2009-1-5 19:48
| 
sqlserver 绌哄煎垽鏂拰绌哄艰繑鍥炲 isnull 鍜 is null
鍙戝竷: 2008-7-01 14:16 | 浣滆: 缃戠粶杞浇 | 鏉ユ簮: 缃戠粶杞浇 | 鏌ョ湅: 267娆
sqlserver 绌哄煎垽鏂拰绌哄艰繑鍥炲 isnull 鍜 is null
ISNULL
浣跨敤鎸囧畾鐨勬浛鎹㈠兼浛鎹 NULL銆
璇硶
ISNULL ( check_expression , replacement_value )
鍙傛暟
check_expression
灏嗚妫鏌ユ槸鍚︿负 NULL鐨勮〃杈惧紡銆俢heck_expression 鍙互鏄换浣曠被鍨嬬殑銆
replacement_value
鍦 check_expression 涓 NULL鏃跺皢杩斿洖鐨勮〃杈惧紡銆俽eplacement_value 蹇呴』涓 check_expresssion 鍏锋湁鐩稿悓鐨勭被鍨嬨
杩斿洖绫诲瀷
杩斿洖涓 check_expression 鐩稿悓鐨勭被鍨嬨
娉ㄩ噴
濡傛灉 check_expression 涓嶄负 NULL锛岄偅涔堣繑鍥炶琛ㄨ揪寮忕殑鍊硷紱鍚﹀垯杩斿洖 replacement_value銆
绀轰緥
A. 灏 ISNULL 涓 AVG 涓璧蜂娇鐢
涓嬮潰鐨勭ず渚嬫煡鎵炬墍鏈変功鐨勫钩鍧囦环鏍硷紝鐢ㄥ $10.00 鏇挎崲 titles 琛ㄧ殑 price 鍒椾腑鐨勬墍鏈 NULL 鏉＄洰銆
USE pubs
GO
SELECT AVG(ISNULL(price, $10.00))
FROM titles
GO
涓嬮潰鏄粨鏋滈泦锛
--------------------------
14.24
(1 row(s) affected)
B. 浣跨敤 ISNULL
涓嬮潰鐨勭ず渚嬩负 titles 琛ㄤ腑鐨勬墍鏈変功閫夋嫨涔﹀悕銆佺被鍨嬪強浠锋牸銆傚鏋滀竴涓功鍚嶇殑浠锋牸鏄 NULL锛岄偅涔堝湪缁撴灉闆嗕腑鏄剧ず鐨勪环鏍间负 0.00銆
USE pubs
GO
SELECT SUBSTRING(title, 1, 15) AS Title, type AS Type,
ISNULL(price, 0.00) AS Price
FROM titles
GO
涓嬮潰鏄粨鏋滈泦锛
Title Type Price
--------------- ------------ --------------------------
The Busy Execut business 19.99
Cooking with Co business 11.95
You Can Combat business 2.99
Straight Talk A business 19.99
Silicon Valley mod_cook 19.99
The Gourmet Mic mod_cook 2.99
The Psychology UNDECIDED 0.00
But Is It User popular_comp 22.95
Secrets of Sili popular_comp 20.00
Net Etiquette popular_comp 0.00
Computer Phobic psychology 21.59
Is Anger the En psychology 10.95
Life Without Fe psychology 7.00
Prolonged Data psychology 19.99
Emotional Secur psychology 7.99
Onions, Leeks, trad_cook 20.95
Fifty Years in trad_cook 11.95
Sushi, Anyone? trad_cook 14.99
(18 row(s) affected)
IS [NOT] NULL
纭畾涓涓粰瀹氱殑琛ㄨ揪寮忔槸鍚︿负 NULL銆傝娉
expression IS [ NOT ] NULL鍙傛暟
expression
鏄换浣曟湁鏁堢殑 Microsoft庐 SQL Server" 琛ㄨ揪寮忋
NOT
鎸囧畾甯冨皵缁撴灉鍙栧弽銆傝皳璇嶅弽杞叾杩斿洖鍊硷紝鍊间笉涓 NULL 鏃惰繑鍥 TRUE锛屽间负 NULL 鏃惰繑鍥 FALSE銆傜粨鏋滅被鍨
甯冨皵鍨嬭繑鍥炰唬鐮佸
濡傛灉 expression 鐨勫兼槸 NULL锛屽垯 IS NULL 杩斿洖 TRUE锛涘惁鍒欙紝杩斿洖 FALSE銆
濡傛灉 expression 鐨勫兼槸 NULL锛屽垯 IS NOT NULL 杩斿洖 FALSE锛涘惁鍒欙紝杩斿洖 TRUE銆傛敞閲
鑻ヨ纭畾琛ㄨ揪寮忔槸鍚︿负 NULL锛岃浣跨敤 IS NULL 鎴 IS NOT NULL锛岃屼笉瑕佺敤姣旇緝杩愮畻绗︼紙渚嬪 = 鎴 !=锛夈傚鏋滄湁涓涓弬鏁颁负 NULL 鎴栦袱涓弬鏁伴兘涓 NULL锛岄偅涔堟瘮杈冭繍绠楃杩斿洖 UNKNOWN銆 绀轰緥
鍦ㄤ笅闈㈢殑绀轰緥涓紝瀵逛簬鎵鏈夐浠樻灏戜簬 $5,000 鎴栬呴浠樻鏈煡锛堟垨涓 NULL锛夌殑涔︼紝杩斿洖瀹冧滑鐨勪功鍙峰強棰勪粯娆俱傝娉ㄦ剰锛屾樉绀虹殑缁撴灉鏄湪绀轰緥 C 鎵ц鍚庢墍杩斿洖鐨勯偅浜涚粨鏋溿
USE pubs
SELECT title_id, advance
FROM titles
WHERE advance < $5000 OR advance IS NULL
ORDER BY title_id
涓嬮潰鏄粨鏋滈泦锛
title_id advance
-------- --------------------------
MC2222 0.0000
MC3026 NULL
PC9999 NULL
PS2091 2275.0000
PS3333 2000.0000
PS7777 4000.0000
TC4203 4000.0000
(7 row(s) affected)
