聽
聽聽 聽1銆佹暟鎹?簱涓?煇涓?〃涓?殑鏁版嵁寰堝?銆傚緢澶氭槸浠?涔堟?蹇碉紵涓?涓囨潯锛熶袱涓囨潯锛熻繕鏄?崄涓囨潯銆佷竴鐧句竾鏉★紵杩欎釜锛屾垜瑙夊緱鏄?粊鑰呰?浠併?佹櫤鑰呰?鏅虹殑闂??銆傚綋鐒舵暟鎹?〃涓?殑鏁?鎹??鍒版煡璇㈡椂鏄庢樉鎰熻?鍒版暟鎹?緢鎱?簡锛岄偅涔堬紝浣犲氨鍙?互鑰冭檻浣跨敤鍒嗗尯琛ㄤ簡銆傚?鏋滈潪瑕佹垜璇翠竴涓?暟鍊肩殑璇濓紝鎴戣?涓烘槸100涓囨潯銆?/p>
聽聽 聽2銆佷絾鏄?紝鏁版嵁澶氫簡骞朵笉鏄?垱寤哄垎鍖鸿〃鐨勬儫涓?鏉′欢锛屽摢鎬曚綘鏈変竴鍗冧竾鏉¤?褰曪紝浣嗘槸杩欎竴鍗冧竾鏉¤?褰曢兘鏄?父鐢ㄧ殑璁板綍锛岄偅涔堟渶濂戒篃涓嶈?浣跨敤鍒嗗尯琛?紝璇翠笉瀹氫細寰椾笉鍋垮け銆傚彧鏈変綘鐨勬暟鎹?槸鍒嗘?鐨勬暟鎹?紝閭ｄ箞鎵嶈?鑰冭檻鍒版槸鍚﹂渶瑕佷娇鐢ㄥ垎鍖鸿〃銆?/p>
聽聽 聽3銆佷粈涔堝彨鏁版嵁鏄?垎娈电殑锛熻繖涓??娉曡櫧鐒跺緢涓嶄笓涓氾紝浣嗗緢濂界悊瑙ｃ?傛瘮濡傝?锛屼綘鐨勬暟鎹?槸浠ュ勾涓哄垎闅旂殑锛屽?浜庝粖骞寸殑鏁版嵁鑰岃█锛屼綘甯歌繘琛岀殑鎿嶄綔鏄?坊鍔犮?佷慨鏀广?佸垹闄?鍜屾煡璇?紝鑰屽?浜庡線骞寸殑鏁版嵁鑰岃█锛屼綘鍑犱箮涓嶉渶瑕佹搷浣滐紝鎴栬?呬綘鐨勬搷浣滃線寰?鍙?檺浜庢煡璇?紝閭ｄ箞鎭?枩浣狅紝浣犲彲浠ヤ娇鐢ㄥ垎鍖鸿〃銆傛崲鍚嶈瘽璇达紝浣犲?鏁版嵁鐨勬搷浣滃線寰?鍙?秹鍙婂埌涓? 閮ㄥ垎鏁版嵁鑰屼笉鏄?墍鏈夋暟鎹?殑璇濓紝閭ｄ箞浣犲氨鍙?互鑰冭檻浠?涔堝垎鍖鸿〃浜嗐??/p>
聽
聽聽 聽閭ｄ箞锛屼粈涔堟槸鍒嗗尯琛ㄥ憿锛?/p>
聽聽 聽绠?鍗曚竴鐐硅?锛屽垎鍖鸿〃灏辨槸灏嗕竴涓?ぇ琛ㄥ垎鎴愯嫢骞蹭釜灏忚〃銆傚亣璁撅紝浣犳湁涓?涓?攢鍞??褰曡〃锛岃?褰曠潃姣忎釜姣忎釜鍟嗗満鐨勯攢鍞?儏鍐碉紝閭ｄ箞浣犲氨鍙?互鎶婅繖涓?攢鍞??褰曡〃鎸夋椂闂村垎鎴?鍑犱釜灏忚〃锛屼緥濡傝?5涓?皬琛ㄥ惂銆?span>2009骞翠互鍓嶇殑璁板綍浣跨敤涓?涓?〃锛?span>2010骞寸殑璁板綍浣跨敤涓?涓?〃锛?span>2011骞寸殑璁板綍浣跨敤涓?涓?〃锛?span>2012骞寸殑璁板綍浣跨敤涓?涓?琛?紝2012骞翠互鍚庣殑璁板綍浣跨敤涓?涓?〃銆傞偅涔堬紝浣犳兂鏌ヨ?鍝?釜骞翠唤鐨勮?褰曪紝灏卞彲浠ュ幓鐩稿?搴旂殑琛ㄩ噷鏌ヨ?锛岀敱浜庢瘡涓?〃涓?殑璁板綍鏁板皯浜嗭紝鏌ヨ?璧锋潵鏃堕棿鑷?劧涔熶細鍑忓皯銆?/p>
聽聽 聽浣嗗皢涓?涓?ぇ琛ㄥ垎鎴愬嚑涓?皬琛ㄧ殑澶勭悊鏂瑰紡锛屼細缁欑▼搴忓憳澧炲姞缂栫▼涓婄殑闅惧害銆備互娣诲姞璁板綍涓轰緥锛屼互涓?span>5涓?〃鏄?嫭绔嬬殑5涓?〃锛屽湪涓嶅悓鏃堕棿娣诲姞璁板綍鐨勬椂鍊欙紝绋嬪簭鍛樿?浣?鐢ㄤ笉鍚岀殑SQL璇?彞锛屼緥濡傚湪2011骞存坊鍔犺?褰曟椂锛岀▼搴忓憳瑕佸皢璁板綍娣诲姞鍒?span>2011骞撮偅涓?〃閲岋紱鍦?span>2012骞存坊鍔犺?褰曟椂锛岀▼搴忓憳瑕佸皢璁板綍娣诲姞鍒?span>2012骞寸殑 閭ｄ釜琛ㄩ噷銆傝繖鏍凤紝绋嬪簭鍛樼殑宸ヤ綔閲忎細澧炲姞锛屽嚭閿欑殑鍙?兘鎬т篃浼氬?鍔犮??span>聽
聽聽 聽浣跨敤鍒嗗尯琛ㄥ氨鍙?互寰堝ソ鐨勮В鍐充互涓婇棶棰樸?傚垎鍖鸿〃鍙?互浠庣墿鐞嗕笂灏嗕竴涓?ぇ琛ㄥ垎鎴愬嚑涓?皬琛?紝浣嗘槸浠庨?昏緫涓婃潵鐪嬶紝杩樻槸涓?涓?ぇ琛ㄣ??/p>
聽聽 聽鎺ョ潃涓婇潰鐨勪緥瀛愶紝鍒嗗尯琛ㄥ彲浠ュ皢涓?涓?攢鍞??褰曡〃鍒嗘垚浜斾釜鐗╃悊涓婄殑灏忚〃锛屼絾鏄??浜庣▼搴忓憳鑰岃█锛屼粬鎵?闈㈠?鐨勪緷鐒舵槸涓?涓?ぇ琛?紝鏃犺?鏄?span>2010骞存坊鍔犺?褰曡繕鏄?span> 2012骞存坊鍔犺?褰曪紝瀵逛簬绋嬪簭鍛樿?岃█鏄?笉闇?瑕佽?冭檻鐨勶紝浠栧彧瑕佸皢璁板綍鎻掑叆鍒伴攢鍞??褰曡〃鈥斺??/span>杩欎釜閫昏緫涓?殑澶ц〃閲屽氨琛屼簡銆?span>SQL Server浼氳嚜鍔ㄥ湴灏嗗畠鏀惧湪瀹冨簲璇ュ憜鍦ㄧ殑閭ｄ釜鐗╃悊涓婄殑灏忚〃閲屻??/p>
聽聽 聽鍚屾牱锛屽?浜庢煡璇㈣?岃█锛岀▼搴忓憳涔熷彧闇?瑕佽?缃?ソ鏌ヨ?鏉′欢锛?span>OK锛?span>SQL Server浼氳嚜鍔ㄥ皢鍘荤浉搴旂殑琛ㄩ噷鏌ヨ?锛屼笉鐢ㄧ?澶??浜嬩簡銆?/p>
聽
聽聽 聽杩欎竴鍒囨槸涓嶆槸寰堣?浜猴紵
聽聽 聽鐨勭‘锛岄偅涔堟垜浠?氨鍙?互寮?濮嬪姩鎵嬪垱寤哄垎鍖鸿〃浜嗐??/p>
聽
聽聽 聽绗?竴銆佸垱寤哄垎鍖鸿〃鐨勭?涓?姝ワ紝鍏堝垱寤烘暟鎹?簱鏂囦欢缁勶紝浣嗚繖涓?姝ュ彲浠ョ渷鐣ワ紝鍥犱负浣犲彲浠ョ洿鎺ヤ娇鐢?span>PRIMARY鏂囦欢銆備絾鎴戜釜浜鸿?涓猴紝涓轰簡鏂逛究绠＄悊锛岃繕鏄?彲浠ュ厛鍒?寤哄嚑涓?枃浠剁粍锛岃繖鏍峰彲浠ュ皢涓嶅悓鐨勫皬琛ㄦ斁鍦ㄤ笉鍚岀殑鏂囦欢缁勯噷锛屾棦渚夸簬鐞嗚В鍙堝彲浠ユ彁楂樿繍琛岄?熷害銆傚垱寤烘枃浠剁粍鐨勬柟娉曞緢绠?鍗曪紝鎵撳紑SQL Server Management Studio锛屾壘鍒板垎鍖鸿〃鎵?鍦ㄦ暟鎹?簱锛屽彸閿?崟鍑伙紝鍦ㄥ脊鍑虹殑鑿滃崟閲岄?夋嫨鈥?/span>灞炴??span>鈥?/span>銆傜劧鍚庨?夋嫨鈥?/span>鏂囦欢缁?span>鈥?/span>閫夐」锛屽啀鍗曞嚮涓嬮潰鐨?span>鈥?/span>娣诲姞鈥?/span>鎸夐挳锛屽?涓嬪浘鎵?绀猴細
聽
聽
聽
聽聽 聽绗?簩锛屽垱寤轰簡鏂囦欢缁勪箣鍚庯紝杩樿?鍐嶅垱寤哄嚑涓?暟鎹?簱鏂囦欢銆備负浠?涔堣?鍒涘缓鏁版嵁搴撴枃浠讹紝杩欏緢濂界悊瑙ｏ紝鍥犱负鍒嗗尯鐨勫皬琛ㄥ繀椤昏?鏀惧湪纭?洏涓婏紝鑰屾斁鍦ㄧ‖鐩樹笂鐨勪粈涔堝湴鏂?鍛?紵褰撶劧鏄?枃浠堕噷鍟︺?傚啀璇翠簡锛屾枃浠剁粍涓?病鏈夋枃浠讹紝鏂囦欢缁勮繕瑕佹潵鏈夊暐鐢ㄥ憿锛熻繕鏄?湪涓婂浘鐨勯偅涓?晫闈?紝閫夋嫨鈥?/span>鏂囦欢鈥?/span>閫夐」锛岀劧鍚庢坊鍔犲嚑涓?枃浠躲?傚湪娣诲姞鏂囦欢鐨勬椂鍊欒? 娉ㄦ剰浠ヤ笅鍑犵偣锛?/p> 聽聽 聽1銆佷笉瑕佸繕璁板皢涓嶅悓鐨勬枃浠舵斁鍦ㄦ枃浠剁粍涓??傚綋鐒朵竴涓?枃浠剁粍涓?篃鍙?互鍖呭惈澶氫釜涓嶅悓鐨勬枃浠躲??/p> 聽聽 聽2銆佸?鏋滃彲浠ョ殑璇濓紝灏嗕笉鍚岀殑鏂囦欢鏀惧湪涓嶅悓鐨勭‖鐩樺垎鍖洪噷锛屾渶濂芥槸鏀惧湪涓嶅悓鐨勭嫭绔嬬‖鐩橀噷銆傝?鐭ラ亾IQ鐨勯?熷害寰?寰?鏄?奖鍝?span>SQL Server 聽聽 聽鍦ㄦ湰渚嬩腑锛屼负浜嗘柟渚胯捣瑙侊紝灏嗘墍鏈夋暟鎹?簱鏂囦欢閮芥斁鍦ㄤ簡鍚屼竴涓?‖鐩樹笅锛屽苟涓旀瘡涓?枃浠剁粍涓?彧鏈変竴涓?枃浠躲?傚?涓嬪浘鎵?绀恒??/p> 聽 聽聽 聽绗?笁銆佸垱寤轰竴涓?垎鍖哄嚱鏁般?傝繖涓?姝ユ槸蹇呴』鐨勪簡锛屽垱寤哄垎鍖哄嚱鏁扮殑鐩?殑鏄?憡璇?span>SQL Server
聽
聽聽 聽绗?span>1涓?皬琛?細2010-1-1浠ュ墠鐨勬暟鎹?紙涓嶅寘鍚?span>2010-1-1锛夈??/p>
聽聽 聽绗?span>2涓?皬琛?細2010-1-1锛堝寘鍚?span>2010-1-1锛夊埌2010-12-31涔嬮棿鐨勬暟鎹???/p>
聽聽 聽绗?span>3涓?皬琛?細2011-1-1锛堝寘鍚?span>2011-1-1锛夊埌2011-12-31涔嬮棿鐨勬暟鎹???/p>
聽聽 聽绗?span>4涓?皬琛?細2012-1-1锛堝寘鍚?span>2012-1-1锛夊埌2012-12-31涔嬮棿鐨勬暟鎹???/p>
聽聽 聽绗?span>5涓?皬琛?細2013-1-1锛堝寘鍚?span>2013-1-1锛変箣鍚庣殑鏁版嵁銆?/p>
聽聽 聽閭ｄ箞鍒嗗尯鍑芥暟鐨勪唬鐮佸?涓嬫墍绀猴細
[c-sharp] view plaincopy
- CREATE聽PARTITION聽FUNCTION聽partfunSale聽(datetime)聽聽
- AS聽RANGE聽RIGHT聽FOR聽VALUES聽('20100101','20110101','20120101','20130101')聽聽
聽
聽聽 聽鍏朵腑锛?/p>
聽聽 聽1銆?span>CREATE PARTITION FUNCTION鎰忔?濇槸鍒涘缓涓?涓?垎鍖哄嚱鏁般??/p>
聽聽 聽2銆?span>partfunSale涓哄垎鍖哄嚱鏁板悕绉般??/p>
聽聽 聽3銆?span>AS RANGE RIGHT涓鸿?缃?垎鍖鸿寖鍥寸殑鏂瑰紡涓?span>Right锛屼篃灏辨槸鍙崇疆鏂瑰紡銆?/p>
聽聽 聽4銆?span>FOR VALUES ('20100101','20110101','20120101','20130101')涓烘寜杩欏嚑涓??兼潵鍒嗗尯銆?/p>
聽聽 聽杩欓噷闇?瑕佽?鏄庣殑涓?涓嬶紝鍦?span>Values涓?紝'20100101'銆?span>'20110101'銆?span>'20120101'銆?span>'20130101'锛岃繖浜涢兘鏄?垎鍖虹殑鏉?浠躲??span>鈥溌?0100101鈥?/span>浠ｈ〃2010骞?span>1鏈?span>1鏃ワ紝鍦ㄥ皬浜庤繖涓??肩殑璁板綍锛岄兘浼氬垎鎴愪竴涓?皬琛ㄤ腑锛屽?琛?span>1锛涜?屽皬浜庢垨绛変簬'20100101'骞朵笖灏忎簬 '20110101'鐨勫?硷紝浼氭斁鍦ㄥ彟涓?涓?〃涓?紝濡傝〃2銆備互姝ょ被鎺?紝鍒版渶鍚庯紝鎵?鏈夊ぇ灏忔垨绛変簬'20130101'鐨勫?间細鏀惧湪鍙︿竴涓?〃涓?紝濡傝〃5銆?/p>
聽聽 聽涔熻?鏈変汉浼氶棶锛屼负浠?涔堝??span>鈥溌?0100101鈥?/span>浼氭斁鍦ㄨ〃2涓?紝鑰屼笉鏄?〃1涓?憿锛熻繖鏄?敱AS RANGE RIGHT涓?殑RIGHT鎵?鍐冲畾鐨勶紝RIGHT鐨勬剰鎬濇槸灏嗙瓑浜庤繖涓??肩殑鏁版嵁鏀惧湪鍙宠竟鐨勯偅涓?〃閲岋紝涔熷氨鏄?〃2涓??傚?鏋滄偍鐨?span>SQL璇?彞涓?娇鐢ㄧ殑鏄?span>Left鑰屼笉 鏄?span>RIGHT锛岄偅涔堝氨浼氭斁鍦ㄥ乏杈圭殑琛ㄤ腑锛屼篃灏辨槸琛?span>1涓???/p>
聽
聽聽 聽绗?洓銆佸垱寤轰竴涓?垎鍖烘柟妗堛?傚垎鍖烘柟妗堢殑浣滅敤鏄?皢鍒嗗尯鍑芥暟鐢熸垚鐨勫垎鍖烘槧灏勫埌鏂囦欢缁勪腑鍘汇?傚垎鍖哄嚱鏁扮殑浣滅敤鏄?憡璇?span>SQL Server锛屽?浣曞皢鏁版嵁杩涜?鍒嗗尯锛岃?屽垎鍖烘柟妗堢殑浣滅敤鍒欐槸鍛婅瘔SQL Server灏嗗凡鍒嗗尯鐨勬暟鎹?斁鍦ㄥ摢涓?枃浠剁粍涓??傚垎鍖烘柟妗堢殑浠ｇ爜濡備笅鎵?绀猴細
[c-sharp] view plaincopy
- CREATE聽PARTITION聽SCHEME聽partschSale聽聽
- AS聽PARTITION聽partfunSale聽聽
- TO聽(聽聽
- 聽聽聽聽Sale2009,聽聽
- 聽聽聽聽Sale2010,聽聽
- 聽聽聽聽Sale2011,聽聽
- 聽聽聽聽Sale2012,聽聽
- 聽聽聽聽Sale2013)聽聽
聽
聽聽 聽鍏朵腑锛?/p>
聽聽 聽1銆?span>CREATE PARTITION SCHEME鎰忔?濇槸鍒涘缓涓?涓?垎鍖烘柟妗堛??/p>
聽聽 聽2銆?span>partschSale涓哄垎鍖烘柟妗堝悕绉般??/p>
聽聽 聽3銆?span>AS PARTITION partfunSale璇存槑璇ュ垎鍖烘柟妗堟墍浣跨敤鐨勬暟鎹?垝鍒嗘潯浠讹紙涔熷氨鏄?墍浣跨敤鐨勫垎鍖哄嚱鏁帮級涓?span>partfunSale銆?/p>
聽聽 聽4銆?span>TO鍚庨潰鐨勫唴瀹规槸鎸?span>partfunSale鍒嗗尯鍑芥暟鍒掑垎鍑烘潵鐨勬暟鎹??搴斿瓨鏀剧殑鏂囦欢缁勩??/p>
聽
聽聽 聽鍒版?涓烘?锛屽垎鍖哄嚱鏁板拰鍒嗗尯鏂规?灏卞垱寤哄畬姣曚簡銆傚垱寤哄悗鐨勫垎鍖哄嚱鏁板拰鍒嗗尯鏂规?鍦ㄦ暟鎹?簱鐨?span>鈥?/span>瀛樺偍鈥?/span>涓?彲浠ョ湅鍒帮紝濡備笅鍥炬墍绀猴細
聽
聽
聽
聽聽 聽鏈?鍚庯紝鍒涘缓鍒嗗尯琛?紝鍒涘缓鏂瑰紡鍜屽垱寤烘櫘閬嶈〃绫讳技锛屽?涓嬫墍绀猴細
[c-sharp] view plaincopy
- CREATE聽TABLE聽Sale(聽聽
- 聽聽聽聽[Id]聽[int]聽IDENTITY(1,1)聽NOT聽NULL,聽聽
- 聽聽聽聽[Name]聽[varchar](16)聽NOT聽NULL,聽聽
- 聽聽聽聽[SaleTime][datetime]聽NOT聽NULL聽聽
- )聽ON聽partschSale([SaleTime])聽聽
聽
聽聽 聽鍏朵腑锛?/p>
聽聽 聽1銆?span>CREATE TABLE 鎰忔?濇槸鍒涘缓涓?涓?暟鎹?〃銆?/p>
聽聽 聽2銆?span>Sale涓烘暟鎹?〃鍚嶃??/p>
聽聽 聽3銆?span>()涓?负琛ㄤ腑鐨勫瓧娈碉紝杩欓噷鐨勫唴瀹瑰拰鍒涘缓鏅??氭暟鎹?〃娌℃湁浠?涔堝尯鍒?紝鎯熶竴闇?瑕佹敞鎰忕殑鏄?笉鑳藉啀鍒涘缓鑱氶泦绱㈠紩浜嗐?傞亾鐞嗗緢绠?鍗曪紝鑱氶泦绱㈠紩鍙?互灏嗚?褰曞湪鐗╃悊涓婇『 搴忓瓨鍌ㄧ殑锛岃?屽垎鍖鸿〃鏄?皢鏁版嵁鍒嗗埆瀛樺偍鍦ㄤ笉鍚岀殑琛ㄤ腑锛岃繖涓や釜姒傚康鏄?啿绐佺殑锛屾墍浠ワ紝鍦ㄥ垱寤哄垎鍖鸿〃鐨勬椂鍊欏氨涓嶈兘鍐嶅垱寤鸿仛闆嗙储寮曚簡銆?/p>
聽聽 聽4銆?span>ON partschSale()璇存槑浣跨敤鍚嶄负partschSale鐨勫垎鍖烘柟妗堛??/p>
聽聽 聽5銆?span>partschSale()鎷?彿涓?负鐢ㄤ簬鍒嗗尯鏉′欢鐨勫瓧娈垫槸SaleTime銆?/p>
聽
聽聽 聽OK锛屼竴涓?墿鐞嗕笂鏄?垎绂荤殑锛岄?昏緫涓婃槸涓?浣撶殑鍒嗗尯琛ㄥ氨鍒涘缓瀹屾瘯浜嗐?傛煡鐪嬭?琛ㄧ殑灞炴?э紝鍙?互鐪嬪埌璇ヨ〃宸茬粡灞炰簬鍒嗗尯琛ㄤ簡銆?/p>
聽
聽
聽