软件开发培训班 >> 编程开发 >> MySQL


MySQL数据库基础篇SQL窗口函数示例解析教程

鏈枃绠�浠?/h2>

鍓嶆鏃堕棿锛岄粍鍚屽鍐欎簡涓�绡囥�奙ySQL绐楀彛瀹炴垬銆嬫枃绔狅紙鏂囩珷濡備笅锛夛紝浣嗘槸閲岄潰澶у鏁版槸浠ュ疄鎴樼粌涔犱负涓伙紝娌℃湁鍋氳缁嗙殑瑙ｉ噴銆?/p>

浼犻�侀棬锛?a target="_blank" href="//www.jb51.net/article/226313.htm">MySQL瀹炴垬绐楀彛鍑芥暟SQL鍒嗘瀽鐝骇瀛︾敓鑰冭瘯鎴愮哗鍙婄敓娲绘秷璐?/a>

浜庢槸锛岀淇′簡鏈堢墮缇庡コ锛岀湅鐪嬪ス鑳藉惁鍐欎竴绡囥�婄獥鍙ｅ嚱鏁板熀纭�绡囥�嬶紝姝ｅソ鍜屼箣鍓嶉偅绡囨枃绔犻厤濂椼�傝繖涓嶏紝寰堝揩濂瑰氨鍐欏ソ浜嗭紝浠婂ぉ灏辩粰澶у鍋氫竴涓垎浜紝鏃ㄥ湪鍜屽ぇ瀹朵氦娴佸涔犲摝锛?/p>

涓嬮潰鏄湀鐗欑殑鐜╃瑧绠�浠嬶紝闄勫甫澶у浘涓�寮犲摝銆?/p>

姝ｆ枃浠嬬粛

绐楀彛鍑芥暟锛屼篃琚О涓?鈥滃紑绐楀嚱鏁扳�濓紝MySQL8.0浠ュ悗锛屽氨鍙互浣跨敤杩欎簺鍑芥暟浜嗐�?/p>

鎴戜滑鍦ㄥ姏鎵ｅ埛棰樼殑鏃跺�欙紝涓嶉毦鍙戠幇锛屾瘮杈冨洶闅剧殑棰樼洰缁忓父浼氭秹鍙婂埌绐楀彛鍑芥暟鐨勫簲鐢紝鍙互璇寸獥鍙ｅ嚱鏁帮紝鏄楠屾垜浠殑SQL姘村钩鏄惁鍒拌揪鐔熺粌姘村钩鐨勪竴涓爣灏恒�?/p>

绐楀彛鍑芥暟鐨勬牸寮忎负锛?鑱氬悎鍑芥暟+over()

绐楀彛鏄弿杩皁ver()鎷彿鍐呭垝瀹氱殑鍐呭锛岃繖涓唴瀹瑰氨鏄獥鍙ｅ嚱鏁扮殑浣滅敤鍩燂紝鍗虫搷浣滅殑鏁版嵁閮藉湪over()鐨勮寖鍥村唴銆?/p>

瀵逛簬绐楀彛鍑芥暟锛屾垜涓汉鐨勭悊瑙ｆ槸缁欐簮鏁版嵁寮�涓�鎵囧彲浠ユ粦鍔ㄧ殑绐楀彛锛屽湪绐楀彛绉诲姩鐨勬椂鍊欏彲浠ュ鍏朵腑鐨勬暟鎹繘琛岄檮鍔犺绠楋紝濡傜Щ鍔ㄥ钩鍧囥�佸垎缁勬帓搴忕瓑锛岀獥鍙ｅ彲浠ユ槸涓�琛屽琛岀敋鑷虫槸鎵�鏈夎銆?/p>

绐楀彛鍑芥暟锛岃繕鍙互瀵瑰缁勬暟鎹繘琛屽悓姝ユ帓搴忋�佽仛鍚堢瓑杩愮畻锛岄拡瀵筭roup by瀛愬彞鎴杦here澶勭悊鍚庣殑缁撴灉杩涜鎿嶄綔锛屽彧鑳藉啓鍏elect瀛愬彞閲屻�?/p>

鐏甸瓊鐢绘墜涓婄嚎锛岀敤Excel绠�鍗曞仛浜嗕釜绠�鏄撶増鐨勭獥鍙ｅ嚱鏁扮殑婕旂ず鍥撅細

鍦ㄨ繖閲屾彃鍏ュ浘鐗囨弿杩? src=

鑱氬悎鍑芥暟 + over()

鍩烘湰璇硶锛?/p>

sum/avg(琚姞宸ョ殑瀛楁鍚? over(partition by 鍒嗙粍鐨勫瓧娈靛悕 order by 鎺掑簭鐨勫瓧娈靛悕 rows between 鈥?and 鈥?

鍚箟锛?琛ㄧず鐢╬artition by鍒嗙粍鍚庨拡瀵规瘡涓粍鍒繘琛屾眰鍜屾垨鑰呮眰鍧囧�笺�?/p> 

--鍖呮嫭鏈浠ュ唴鍜屽墠3琛岋細rows between 6 preceding and current row
--鍖呮嫭鏈浠ュ唴鍜屽悗3琛岋細rows between current row and 3 following
--鍖呮嫭鏈鍜屼箣鍓嶆墍鏈夌殑琛岋細rows between unbounded preceding and current row
--鍖呮嫭鏈鍜屼箣鍚庢墍鏈夌殑琛岋細rows between current row and unbounded following
--浠庡墠3琛屽埌涓?琛岋紙鎬诲叡鍖呭惈5琛屾暟鎹級锛歳ows between 3 preceding and 1 following

杩樻湁涓�浜涘叾瀹冪殑鑱氬悎鍑芥暟锛屼緥濡俶ax銆乵in銆乧ount锛屽畠浠殑璇硶缁撴瀯閮界被浼笺�?/p>

鎺掑簭鍑芥暟 + over()

row_number()銆乺ank()銆乨ense_rank()杩欎笁涓嚱鏁帮紝閮芥槸瀵箂elect鏌ヨ鍒扮殑缁撴灉杩涜鎺掑簭锛屾垜浠潵鐪嬬湅杩欎笁鑰呯殑鍖哄埆銆?/p>

row_number()锛?涓轰笉閲嶅鐨勮繛缁帓搴忥紝浠?寮�濮嬶紝涓烘煡璇㈠埌鐨勬暟鎹緷娆＄敓鎴愪笉閲嶅鐨勫簭鍙疯繘琛屾帓搴?/p>

鍩烘湰璇硶鈥斺�攔ow_number() over(order by 闇�瑕佹帓搴忕殑瀛楁asc/desc)锛?/p>

rank()锛?涓鸿烦璺冩帓搴忥紝缁撴灉鐩稿悓鐨勪袱涓暟鎹苟鍒楋紝涓轰笅涓�涓暟鎹┖鍑烘墍鍗犵殑鍚嶆锛屽嵆鐩稿悓鎺掑悕浼氬崰浣?/p>

鍩烘湰璇硶鈥斺�攔ank() over(order by 闇�瑕佹帓搴忕殑瀛楁 asc/desc)锛?/p>

dense_rank()锛?涓烘湁閲嶅鐨勮繛缁帓搴忥紝缁撴灉鐩稿悓鐨勪袱涓暟鎹苟鍒楋紝涓嶄负涓嬩竴涓暟鎹┖鍑烘墍鍗犵殑鍚嶆锛屽嵆鐩稿悓鎺掑悕涓嶅崰浣?/p>

鍩烘湰璇硶鈥斺�攄ense_rank() over(order by 闇�瑕佹帓搴忕殑瀛楁 asc/desc)锛?/p>

鎴戜滑鐢ㄤ竴寮犲浘鏉ヨ〃绀鸿繖涓夎�呴棿鐨勫叧绯伙細

鍦ㄨ繖閲屾彃鍏ュ浘鐗囨弿杩? src=

ntile()鍑芥暟 + over()

鍩烘湰璇硶锛?ntile(n) over(partition by鈥rder by鈥?鍏朵腑n琛ㄧず琚垏鍒嗙殑娈垫暟銆?/p>

ntile(n)鐢ㄤ簬灏嗗垎缁勬暟鎹钩鍧囧垏鍒嗘垚n鍧楋紝濡傛灉鍒囧垎鐨勬瘡缁勬暟閲忎笉鍧囩瓑锛屽垯绗竴缁勫垎寰楃殑鏁版嵁鏇村銆?/p>

ntile()鍑芥暟閫氬父鐢ㄤ簬姣斿姹傚勾绾у墠10%鎴愮哗鐨勫鐢燂紝鍒檔鍙栧�间负10锛岀敤where绛涢�夊嚭绗竴缁勭殑鏁版嵁銆?/p>

鍋忕Щ鍑芥暟 + over()

鍩烘湰璇硶1锛氬墠N琛岋細lead(str, n, default) over(partition by 鈥rder by 鈥?

鍩烘湰璇硶2锛氬悗N琛岋細lag(str, n, default) over(partition by 鈥rder by 鈥?

str琛ㄧず瀛楁鍚嶏紝n琛ㄧず鍓?鍚巒琛屾暟鎹紝榛樿鍊间负1锛宒efault琛ㄧず濡傛灉鍙栧�艰寖鍥村凡缁忚秴杩囨暣涓〃鐨勮繑鍥炲�硷紝鍙互涓嶅～锛屼笉濉粯璁よ繑鍥濶/A銆?/p>

鍋忕Щ鍑芥暟锛岀敤浜庡彇鍑哄悓涓�瀛楁鐨勫墠N琛屾暟鎹垨鍚嶯琛屾暟鎹紝浣滀负鍗曠嫭鐨勫垪锛岃繖閲岄渶瑕佺壒鍒敞鎰忎竴涓嬬殑鏄紝lead浠ｈ〃鍓峃琛岋紝lag浠ｈ〃鍚嶯琛屻�?/p>

浠ヤ笂灏辨槸MySQL鏁版嵁搴撳熀纭�绡囩獥鍙ｅ嚱鏁扮ず渚嬭В鏋愭暀绋嬬殑璇︾粏鍐呭锛屾洿澶氬叧浜嶮ySQL绐楀彛鍑芥暟鍩虹鐨勮祫鏂欒鍏虫敞鑴氭湰涔嬪鍏跺畠鐩稿叧鏂囩珷锛?/p>