;
帖子
3919 
精华
积分
11710 
鱼币
0 ¥ 
来自
大连 
在线时间
1858 小时 
注册时间
2007-6-11 
最后登录
2009-10-20 

贴图大师勋章新人进步勋章宣传大使勋章论坛元老勋章

3
发表于 2009-1-5 19:48 | 只看该作者
sqlserver 绌哄煎垽鏂拰绌哄艰繑鍥炲 isnull 鍜 is null - 缂栫▼搴 - Powered by SupeSite

浣犵殑浣嶇疆锛缂栫▼搴 >> 璧勮 >> 鏁版嵁搴撶鐞 >> SQL璇硶 >> 璇︾粏鍐呭 鍦ㄧ嚎鎶曠ǹ

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)

1_200807011416592medc1_200807011416592medc

鎵撳嵃 | 鏀惰棌姝ら〉 |  鎺ㄨ崘缁欏ソ鍙 | 涓炬姤
涓婁竴绡 涓嬩竴绡
 

璇勫垎锛0

鍙戣〃璇勮
鏌ョ湅鍏ㄩ儴鍥炲銆愬凡鏈0浣嶇綉鍙嬪彂琛ㄤ簡鐪嬫硶銆