为啥SQL中select语句中，有时用：case......when …… then …… else …… end as x SQL条件控制（case when...then...els...
\u4e3a\u5565SQL\u4e2dselect\u8bed\u53e5\u4e2d\uff0c\u6709\u65f6\u7528\uff1acase......when \u2026\u2026 then \u2026\u2026 else \u2026\u2026 end as xselectsum(casewhenA.CLOSE_ID='T'then1else0end)from(selectdistinctA.CLOSE_ID,A.MO_NOfromA)T'\u5b8c\u6210\u79cd\u6570'=COUNT(CASEWHENA.CLOSE_ID='T'THENA.MO_NOELSE0END)\u8fd9\u53e5\u7684\u903b\u8f91\u5c31\u6709\u95ee\u9898\uff0c\u4f60\u8981\u6c42A.CLOSE_ID='T'\u65f6\uff0cA.MO_NO\u7684\u4e2a\u6570\u5417\u770b\u90a3\u6837\u4f60\u76f4\u63a5\u5199selectcount(A.MO_NO)fromAwhereA.CLOSE_ID='T'\u5c31\u597d\u4e86\u5c31\u7b97\u4f60\u4e00\u5b9a\u8981\u7528casewhenthen\uff0c\u4f60\u4e5f\u8981\u7528sum\u800c\u4e0d\u662fcount,\u4f60\u7528count\u662f\u6240\u4ee5(CASEWHENA.CLOSE_ID='T'THENA.MO_NOELSE0END)\u7684\u4e2a\u6570\uff0c\u5c31\u7b97\u662f0\uff0c\u5b83\u4e5f\u662f\u4e2a\u503c\uff0c\u4ecd\u7136\u4f1a\u88abcount\u8fdb\u53bb\u554a\uff0c\u4f60\u53ea\u6709sum\u65f6\uff0c0\u624d\u662f\u4e0d\u4f1a\u8d77\u505a\u7528\u7684\u6570\u3002
case when \u6761\u4ef6 then \u6761\u4ef6\u4e3a\u771f\u65f6\u7684\u503c else \u6761\u4ef6\u4e3a\u5047\u65f6\u7684\u503c end
case\u5177\u6709\u4e24\u79cd\u683c\u5f0f\u3002\u7b80\u5355case\u51fd\u6570\u548ccase\u641c\u7d22\u51fd\u6570\u3002
--\u7b80\u5355case\u51fd\u6570
case sex
when '1' then '\u7537'
when '2' then '\u5973'
else '\u5176\u4ed6' end
--case\u641c\u7d22\u51fd\u6570
case when sex = '1' then '\u7537'
when sex = '2' then '\u5973'
else '\u5176\u4ed6' end
\u8fd9\u4e24\u79cd\u65b9\u5f0f\uff0c\u53ef\u4ee5\u5b9e\u73b0\u76f8\u540c\u7684\u529f\u80fd\u3002\u7b80\u5355case\u51fd\u6570\u7684\u5199\u6cd5\u76f8\u5bf9\u6bd4\u8f83\u7b80\u6d01\uff0c\u4f46\u662f\u548ccase\u641c\u7d22\u51fd\u6570\u76f8\u6bd4\uff0c\u529f\u80fd\u65b9\u9762\u4f1a\u6709\u4e9b\u9650\u5236\uff0c\u6bd4\u5982\u5199\u5224\u5b9a\u5f0f\u3002
\u8fd8\u6709\u4e00\u4e2a\u9700\u8981\u6ce8\u91cd\u7684\u95ee\u9898\uff0ccase\u51fd\u6570\u53ea\u8fd4\u56de\u7b2c\u4e00\u4e2a\u7b26\u5408\u6761\u4ef6\u7684\u503c\uff0c\u5269\u4e0b\u7684case\u90e8\u5206\u5c06\u4f1a\u88ab\u81ea\u52a8\u5ffd\u7565\u3002
--\u6bd4\u5982\u8bf4\uff0c\u4e0b\u9762\u8fd9\u6bb5sql\uff0c\u4f60\u6c38\u8fdc\u65e0\u6cd5\u5f97\u5230\u201c\u7b2c\u4e8c\u7c7b\u201d\u8fd9\u4e2a\u7ed3\u679c
case when col_1 in ( 'a', 'b') then '\u7b2c\u4e00\u7c7b'
when col_1 in ('a') then '\u7b2c\u4e8c\u7c7b'
else'\u5176\u4ed6' end
select col col_new from 表名
select col as col_new from 表名
select col_new=col from 表名
select 'col_new'=col from 表名
select [col_new]=col from 表名
在MSSQL中，上述几句SQL都是等同的
绛旓細select col col_new from 琛ㄥ悕select col as col_new from 琛ㄥ悕select col_new=col from 琛ㄥ悕select 'col_new'=col from 琛ㄥ悕select [col_new]=col from 琛ㄥ悕鍦∕SSQL涓锛屼笂杩板嚑鍙QL閮芥槸绛夊悓鐨
绛旓細銆愮瓟妗堛戯細B 鎶曞奖锛氬鍏崇郴R鐨勬姇褰辨搷浣滐紝瀹為檯涓婃槸浠嶳涓夋嫨鍑鸿嫢骞插睘鎬у垪缁勬垚鏂扮殑鍏崇郴銆SELECT鏄夋嫨鐩爣鍒楄〃杈惧紡锛屾墍浠ELECT鐩稿綋浜庢姇褰辨搷浣銆
绛旓細5銆佺粨璁猴紝鎵闇SQL璇彞涓猴細銆select 鎬у埆, count(*) as 浜烘暟, avg锛堝勾榫勶級as 骞冲潎骞撮緞 from 瀛︾敓琛 group by 鎬у埆銆戯紙瑙佷笅鍥撅紝鍦╒FP6涓獙璇侊紝鍥剧墖鍙偣鍑绘斁澶э級銆
绛旓細SELECT璇彞鏄疭QL涓渶甯哥敤鐨勫懡浠や箣涓锛屽畠鎻愪緵浜嗕粠鏁版嵁搴撲腑妫绱㈡暟鎹殑鍩虹缁撴瀯銆傞氳繃缁撳悎WHERE鍜孫RDER BY瀛愬彞绛夊叾浠朣QL鍔熻兘锛岀敤鎴峰彲浠ュ垱寤哄嚭澶嶆潅鐨勬煡璇㈡潵婊¤冻鐗瑰畾鐨勬暟鎹绱㈤渶姹傘備簡瑙ｈ繖浜涘熀鏈蹇靛拰鐢ㄦ硶鏄垚涓虹啛缁冩暟鎹簱鐢ㄦ埛鐨勫叧閿
绛旓細SQL SELECT 璇彞 SELECT 璇彞鐢ㄤ簬浠庤〃涓夊彇鏁版嵁銆傜粨鏋滆瀛樺偍鍦ㄤ竴涓粨鏋滆〃涓紙绉颁负缁撴灉闆嗭級銆係QL SELECT 璇硶 SELECT 鍒楀悕绉 FROM 琛ㄥ悕绉 浠ュ強锛歋ELECT * FROM 琛ㄥ悕绉 娉ㄩ噴锛SQL 璇彞瀵瑰ぇ灏忓啓涓嶆晱鎰熴係ELECT 绛夋晥浜 select銆
绛旓細銆愮瓟妗堛戯細C 鏈鑰冩煡瀵SQL璇彞涓WHERE瀛愬彞鐨勭悊瑙ｃ傚湪SQL璇彞涓紝WHERE鐢ㄦ潵鎻忚堪鏌ヨ鏉′欢锛堝嵆杩涜閫夋嫨杩愮畻锛夛紝SQL璇彞涓病鏈塅OR銆丆ONDITION鎴朩HILE绛夌煭璇垨鍏冲仴瀛椼傛晠閫夐」C涓烘纭瓟妗堛
绛旓細浣滅敤灏辨槸浠庢暟鎹簱涓绱㈡暟鎹紝骞跺皢鏌ヨ缁撴灉杩斿洖缁欑敤鎴枫Select璇彞鐢眘elect瀛愬彞锛堟煡璇㈠唴瀹癸級銆乫rom瀛愬彞锛堟煡璇㈠璞★級銆亀here瀛愭棳锛堟煡璇㈡潯浠讹級銆乷rder by瀛愬彞锛堟帓搴忔柟寮忥級銆乬roup by瀛愬彞锛堝垎缁勬柟寮忥級绛夌粍鎴愩傝绠楀垪鍑哄湪 FROM 涓鐨鎵鏈夊厓绱犮傦紙FROM 涓殑姣忎釜鍏冪礌閮芥槸涓涓湡姝ｇ殑鎴栬呰櫄鎷熺殑琛ㄣ傦級濡傛灉鍦 FROM ...
绛旓細鍥犱负鍗曞紩鍙' 寰堢壒娈,鍦ㄥ瓧绗︿覆涓鐨鍗曞紩鍙疯鐢''涓や釜鍗曞紩鍙锋潵琛ㄧず,鍋氬嚑涓緥瀛愬氨娓呮浜,瀛楃涓 AAA'BBB,鍏朵腑鏈夊崟寮曞彿,鑻ユ寜鐓у埆鐨勫瓧绗︿覆閭ｆ牱鏉ュ啓灏辨槸select 'AAA'BBB'杩欐牱灏变細鎶ラ敊,鍥犱负瀛楃涓茶鍗曞紩鍙锋埅鏂簡,鎵浠ヨ鐢╯elect 'AAA''BBB'璇彞涓2涓崟寮曞彿琛ㄧず瀛楃涓蹭腑鐨勫崟寮曞彿.鎵浠''' 灏辩浉褰撲簬 涓...
绛旓細鏌ュ嚭涓や釜鏃ユ湡涔嬮棿鐨勬暟鎹鐨剆ql绀轰緥锛select * from table_name where date>'2010-1-1' and data<'2010-5-1';鍏朵腑table_name鏄〃鍚嶏紝date鏄棩鏈熷瓧娈碉紝and绗﹀彿琛ㄧず涓旓紝杩炴帴涓や釜鏃ユ湡琛ㄧず鏃㈡弧瓒冲ぇ浜2010-1-1鍙堟弧瓒冲皬浜2010-5-1锛屽湪涓や釜涔嬮棿鐨勬暟鎹
绛旓細stringsql = "select * from people p where p.id = ? and p.name = ?";preparedstatement ps = connection.preparestatement(sql);ps.setint(1,id);ps.setstring(2,name);resultset rs = ps.executequery();浣跨敤preparedstatement鐨勫彟涓涓紭鐐规槸瀛楃涓蹭笉鏄姩鎬佸垱寤虹殑銆備笅闈㈡槸涓涓姩鎬佸垱寤哄瓧绗...
