如题所述
å¾å¤äººä¸ç¥éSQLè¯å¥å¨SQLSERVERä¸æ¯å¦ä½æ§è¡çï¼ä»ä»¬æ
å¿èªå·±æåçSQLè¯å¥ä¼è¢«SQLSERVER误解ãæ¯å¦ï¼ããselect*fromtable1wherename='zhangsan'andtID>10000ããåæ§è¡:ããselect*fromtable1wheretID>10000andname='zhangsan'ããä¸äºäººä¸ç¥é以ä¸ä¸¤æ¡è¯å¥çæ§è¡æçæ¯å¦ä¸æ ·ï¼å 为å¦æç®åçä»è¯å¥å
åä¸çï¼è¿ä¸¤ä¸ªè¯å¥çç¡®æ¯ä¸ä¸æ ·ï¼å¦ætIDæ¯ä¸ä¸ªèåç´¢å¼ï¼é£ä¹åä¸å¥ä»
ä»
ä»è¡¨ç10000æ¡ä»¥åçè®°å½ä¸æ¥æ¾å°±è¡äºï¼èåä¸å¥åè¦å
ä»å
¨è¡¨ä¸æ¥æ¾çæå 个name='zhangsan'çï¼èååæ ¹æ®éå¶æ¡ä»¶æ¡ä»¶tID>10000æ¥æåºæ¥è¯¢ç»æãããäºå®ä¸ï¼è¿æ ·çæ
å¿æ¯ä¸å¿
è¦çãSQLSERVERä¸æä¸ä¸ªâæ¥è¯¢åæä¼åå¨âï¼å®å¯ä»¥è®¡ç®åºwhereåå¥ä¸çæç´¢æ¡ä»¶å¹¶ç¡®å®åªä¸ªç´¢å¼è½ç¼©å°è¡¨æ«æçæ索空é´ï¼ä¹å°±æ¯è¯´ï¼å®è½å®ç°èªå¨ä¼åãããè½ç¶æ¥è¯¢ä¼åå¨å¯ä»¥æ ¹æ®whereåå¥èªå¨çè¿è¡æ¥è¯¢ä¼åï¼ä½å¤§å®¶ä»ç¶æå¿
è¦äºè§£ä¸ä¸âæ¥è¯¢ä¼åå¨âçå·¥ä½åçï¼å¦éè¿æ ·ï¼ææ¶æ¥è¯¢ä¼åå¨å°±ä¼ä¸æç
§æ¨çæ¬æè¿è¡å¿«éæ¥è¯¢ãããå¨æ¥è¯¢åæé¶æ®µï¼æ¥è¯¢ä¼åå¨æ¥çæ¥è¯¢çæ¯ä¸ªé¶æ®µå¹¶å³å®éå¶éè¦æ«æçæ°æ®éæ¯å¦æç¨ãå¦æä¸ä¸ªé¶æ®µå¯ä»¥è¢«ç¨ä½ä¸ä¸ªæ«æåæ°ï¼SARGï¼ï¼é£ä¹å°±ç§°ä¹ä¸ºå¯ä¼åçï¼å¹¶ä¸å¯ä»¥å©ç¨ç´¢å¼å¿«éè·å¾æéæ°æ®ãããSARGçå®ä¹ï¼ç¨äºéå¶æç´¢çä¸ä¸ªæä½ï¼å 为å®é常æ¯æä¸ä¸ªç¹å®çå¹é
ï¼ä¸ä¸ªå¼å¾èå´å
çå¹é
æè
两个以ä¸æ¡ä»¶çANDè¿æ¥ãå½¢å¼å¦ä¸ï¼ããååæä½ç¬¦ããæããæä½ç¬¦ååååå¯ä»¥åºç°å¨æä½ç¬¦çä¸è¾¹ï¼è常æ°æåéåºç°å¨æä½ç¬¦çå¦ä¸è¾¹ãå¦ï¼ããName='å¼ ä¸'ããä»·æ ¼>5000ãã50005000ããå¦æä¸ä¸ªè¡¨è¾¾å¼ä¸è½æ»¡è¶³SARGçå½¢å¼ï¼é£å®å°±æ æ³éå¶æç´¢çèå´äºï¼ä¹å°±æ¯SQLSERVERå¿
须对æ¯ä¸è¡é½å¤æå®æ¯å¦æ»¡è¶³WHEREåå¥ä¸çæææ¡ä»¶ãæ以ä¸ä¸ªç´¢å¼å¯¹äºä¸æ»¡è¶³SARGå½¢å¼ç表达å¼æ¥è¯´æ¯æ ç¨çãããä»ç»å®SARGåï¼æ们æ¥æ»ç»ä¸ä¸ä½¿ç¨SARG以åå¨å®è·µä¸éå°çåæäºèµæä¸ç»è®ºä¸åçç»éªï¼ãã1ãLikeè¯å¥æ¯å¦å±äºSARGåå³äºæ使ç¨çéé
符çç±»åãã--å¦ï¼namelike'å¼ %'--ï¼è¿å°±å±äºSARGãã--èï¼namelike'%å¼ '--,å°±ä¸å±äºSARGãããåå æ¯éé
符%å¨å符串çå¼é使å¾ç´¢å¼æ æ³ä½¿ç¨ããã2ãorä¼å¼èµ·å
¨è¡¨æ«æããName='å¼ ä¸'andä»·æ ¼>5000符å·SARGï¼èï¼Name='å¼ ä¸'orä»·æ ¼>5000åä¸ç¬¦åSARGã使ç¨orä¼å¼èµ·å
¨è¡¨æ«æããã3ãéæä½ç¬¦ãå½æ°å¼èµ·çä¸æ»¡è¶³SARGå½¢å¼çè¯å¥ããä¸æ»¡è¶³SARGå½¢å¼çè¯å¥æå
¸åçæ
åµå°±æ¯å
æ¬éæä½ç¬¦çè¯å¥ï¼å¦ï¼NOTã!=ãã!ãNOTEXISTSãNOTINãNOTLIKEçï¼å¦å¤è¿æå½æ°ãä¸é¢å°±æ¯å 个ä¸æ»¡è¶³SARGå½¢å¼çä¾åï¼ããABS(ä»·æ ¼)5000ãã--SQLSERVERä¹ä¼è®¤ä¸ºæ¯SARGï¼SQLSERVERä¼å°æ¤å¼è½¬å为ï¼WHEREä»·æ ¼>2500/2ããä½æ们ä¸æ¨èè¿æ ·ä½¿ç¨ï¼å 为ææ¶SQLSERVERä¸è½ä¿è¯è¿ç§è½¬åä¸åå§è¡¨è¾¾å¼æ¯å®å
¨çä»·çããã4ãINçä½ç¨ç¸å½ä¸ORããè¯å¥ï¼ããSelect*fromtable1wheretidin(2,3)ãã--åããSelect*fromtable1wheretid=2ortid=3ããæ¯ä¸æ ·çï¼é½ä¼å¼èµ·å
¨è¡¨æ«æï¼å¦ætidä¸æç´¢å¼ï¼å
¶ç´¢å¼ä¹ä¼å¤±æããã5ãå°½éå°ç¨NOTãã6ãexistsåinçæ§è¡æçæ¯ä¸æ ·çããå¾å¤èµæä¸é½æ¾ç¤ºè¯´ï¼existsè¦æ¯inçæ§è¡æçè¦é«ï¼åæ¶åºå°½å¯è½çç¨notexistsæ¥ä»£æ¿notinãä½äºå®ä¸ï¼æè¯éªäºä¸ä¸ï¼åç°äºè
æ 论æ¯åé¢å¸¦ä¸å¸¦notï¼äºè
ä¹é´çæ§è¡æçé½æ¯ä¸æ ·çãå 为æ¶ååæ¥è¯¢ï¼æ们è¯éªè¿æ¬¡ç¨SQLSERVERèªå¸¦çpubsæ°æ®åºãè¿è¡åæ们å¯ä»¥æSQLSERVERçstatisticsI/Oç¶ææå¼ï¼ããï¼1ï¼selecttitle,pricefromtitleswheretitle_idin(selecttitle_idfromsaleswhereqty>30)ãã该å¥çæ§è¡ç»æ为ï¼ãã表'sales'ãæ«æ计æ°18ï¼é»è¾è¯»56次ï¼ç©ç读0次ï¼é¢è¯»0次ã表'titles'ãæ«æ计æ°1ï¼é»è¾è¯»2次ï¼ç©ç读0次ï¼é¢è¯»0次ãããï¼2ï¼selecttitle,pricefromtitlesããwhereexists(select*fromsalesããwheresales.title_id=titles.title_idandqty>30)ãã第äºå¥çæ§è¡ç»æ为ï¼ãã表'sales'ãæ«æ计æ°18ï¼é»è¾è¯»56次ï¼ç©ç读0次ï¼é¢è¯»0次ã表'titles'ãæ«æ计æ°1ï¼é»è¾è¯»2次ï¼ç©ç读0次ï¼é¢è¯»0次ãããæ们ä»æ¤å¯ä»¥çå°ç¨existsåç¨inçæ§è¡æçæ¯ä¸æ ·çããã7ãç¨å½æ°charindex()ååé¢å éé
符%çLIKEæ§è¡æçä¸æ ·ããåé¢ï¼æ们è°å°ï¼å¦æå¨LIKEåé¢å ä¸éé
符%ï¼é£ä¹å°ä¼å¼èµ·å
¨è¡¨æ«æï¼æ以å
¶æ§è¡æçæ¯ä½ä¸çãä½æçèµæä»ç»è¯´ï¼ç¨å½æ°charindex()æ¥ä»£æ¿LIKEé度ä¼æ大çæåï¼ç»æè¯éªï¼åç°è¿ç§è¯´æä¹æ¯é误çï¼ããselectgid,title,fariqi,readerfromtgongwenãããwherecharindex('å侦æ¯é',reader)>0andfariqi>'2004-5-5'ããç¨æ¶ï¼7ç§ï¼å¦å¤ï¼æ«æ计æ°4ï¼é»è¾è¯»7155次ï¼ç©ç读0次ï¼é¢è¯»0次ãããselectgid,title,fariqi,readerfromtgongwenãããwherereaderlike'%'+'å侦æ¯é'+'%'andfariqi>'2004-5-5'ããç¨æ¶ï¼7ç§ï¼å¦å¤ï¼æ«æ计æ°4ï¼é»è¾è¯»7155次ï¼ç©ç读0次ï¼é¢è¯»0次ããã8ãunion并ä¸ç»å¯¹æ¯orçæ§è¡æçé«ããæ们åé¢å·²ç»è°å°äºå¨whereåå¥ä¸ä½¿ç¨orä¼å¼èµ·å
¨è¡¨æ«æï¼ä¸è¬çï¼ææè§è¿çèµæé½æ¯æ¨èè¿éç¨unionæ¥ä»£æ¿orãäºå®è¯æï¼è¿ç§è¯´æ³å¯¹äºå¤§é¨åé½æ¯éç¨çãããselectgid,fariqi,neibuyonghu,reader,titlefromTgongwenãããwherefariqi='2004-9-16'orgid>9990000ããç¨æ¶ï¼68ç§ãæ«æ计æ°1ï¼é»è¾è¯»404008次ï¼ç©ç读283次ï¼é¢è¯»392163次ãããselectgid,fariqi,neibuyonghu,reader,titlefromTgongwenwherefariqi='2004-9-16'unionselectgid,fariqi,neibuyonghu,reader,titlefromTgongwenwheregid>9990000ããç¨æ¶ï¼9ç§ãæ«æ计æ°8ï¼é»è¾è¯»67489次ï¼ç©ç读216次ï¼é¢è¯»7499次ãããçæ¥ï¼ç¨unionå¨é常æ
åµä¸æ¯ç¨orçæçè¦é«çå¤ãããä½ç»è¿è¯éªï¼ç¬è
åç°å¦æor两边çæ¥è¯¢åæ¯ä¸æ ·çè¯ï¼é£ä¹ç¨unionååååç¨orçæ§è¡é度差å¾å¤ï¼è½ç¶è¿éunionæ«æçæ¯ç´¢å¼ï¼èoræ«æçæ¯å
¨è¡¨ãããselectgid,fariqi,neibuyonghu,reader,titlefromTgongwenãããwherefariqi='2004-9-16'orfariqi='2004-2-5'ããç¨æ¶ï¼6423毫ç§ãæ«æ计æ°2ï¼é»è¾è¯»14726次ï¼ç©ç读1次ï¼é¢è¯»7176次ãããselectgid,fariqi,neibuyonghu,reader,titlefromTgongwenwherefariqi='2004-9-16'unionselectgid,fariqi,neibuyonghu,reader,titlefromTgongwenwherefariqi='2004-2-5'ããç¨æ¶ï¼11640毫ç§ãæ«æ计æ°8ï¼é»è¾è¯»14806次ï¼ç©ç读108次ï¼é¢è¯»1144次ããã9ãå段æåè¦æç
§âéå¤å°ãæå¤å°âçååï¼é¿å
âselect*âããæ们æ¥åä¸ä¸ªè¯éªï¼ããselecttop10000gid,fariqi,reader,titlefromtgongwenorderbygiddescç¨æ¶ï¼4673毫ç§selecttop10000gid,fariqi,titlefromtgongwenorderbygiddescç¨æ¶ï¼1376毫ç§selecttop10000gid,fariqifromtgongwenorderbygiddescç¨æ¶ï¼80毫ç§ããç±æ¤çæ¥ï¼æ们æ¯å°æåä¸ä¸ªå段ï¼æ°æ®çæåé度就ä¼æç¸åºçæåãæåçé度è¿è¦çæ¨èå¼çå段ç大å°æ¥å¤æããã10ãcount(*)ä¸æ¯count(å段)æ
¢ããæäºèµæä¸è¯´ï¼ç¨*ä¼ç»è®¡ææåï¼æ¾ç¶è¦æ¯ä¸ä¸ªä¸ççååæçä½ãè¿ç§è¯´æ³å
¶å®æ¯æ²¡ææ ¹æ®çãæ们æ¥çï¼ããselectcount(*)fromTgongwenç¨æ¶ï¼1500毫ç§ããselectcount(gid)fromTgongwenç¨æ¶ï¼1483毫ç§ããselectcount(fariqi)fromTgongwenç¨æ¶ï¼3140毫ç§ããselectcount(title)fromTgongwenç¨æ¶ï¼52050毫ç§ããä»ä»¥ä¸å¯ä»¥çåºï¼å¦æç¨count(*)åç¨count(主é®)çé度æ¯ç¸å½çï¼ècount(*)å´æ¯å
¶ä»ä»»ä½é¤ä¸»é®ä»¥å¤çå段æ±æ»é度è¦å¿«ï¼èä¸å段è¶é¿ï¼æ±æ»çé度就è¶æ
¢ãææ³ï¼å¦æç¨count(*)ï¼SQLSERVERå¯è½ä¼èªå¨æ¥æ¾æå°å段æ¥æ±æ»çãå½ç¶ï¼å¦ææ¨ç´æ¥åcount(主é®)å°ä¼æ¥çæ´ç´æ¥äºããã11ãorderbyæèéç´¢å¼åæåºæçæé«ããæ们æ¥çï¼ï¼gidæ¯ä¸»é®ï¼fariqiæ¯èåç´¢å¼åï¼ï¼ããselecttop10000gid,fariqi,reader,titlefromtgongwenããç¨æ¶ï¼196毫ç§ãæ«æ计æ°1ï¼é»è¾è¯»289次ï¼ç©ç读1次ï¼é¢è¯»1527次ãããselecttop10000gid,fariqi,reader,titlefromtgongwenorderbygidascããç¨æ¶ï¼4720毫ç§ãæ«æ计æ°1ï¼é»è¾è¯»41956次ï¼ç©ç读0次ï¼é¢è¯»1287次ãããselecttop10000gid,fariqi,reader,titlefromtgongwenorderbygiddescç¨æ¶ï¼4736毫ç§ãæ«æ计æ°1ï¼é»è¾è¯»55350次ï¼ç©ç读10次ï¼é¢è¯»775次ãããselecttop10000gid,fariqi,reader,titlefromtgongwenorderbyfariqiascç¨æ¶ï¼173毫ç§ãæ«æ计æ°1ï¼é»è¾è¯»290次ï¼ç©ç读0次ï¼é¢è¯»0次ãããselecttop10000gid,fariqi,reader,titlefromtgongwenorderbyfariqidescç¨æ¶ï¼156毫ç§ãæ«æ计æ°1ï¼é»è¾è¯»289次ï¼ç©ç读0次ï¼é¢è¯»0次ãããä»ä»¥ä¸æ们å¯ä»¥çåºï¼ä¸æåºçé度以åé»è¾è¯»æ¬¡æ°é½æ¯åâorderbyèéç´¢å¼åâçé度æ¯ç¸å½çï¼ä½è¿äºé½æ¯âorderbyéèéç´¢å¼åâçæ¥è¯¢é度æ¯å¿«å¾å¤çãããåæ¶ï¼æç
§æ个å段è¿è¡æåºçæ¶åï¼æ 论æ¯æ£åºè¿æ¯ååºï¼é度æ¯åºæ¬ç¸å½çããã12ãé«æçTOPããäºå®ä¸ï¼å¨æ¥è¯¢åæåè¶
大容éçæ°æ®éæ¶ï¼å½±åæ°æ®åºååºæ¶é´çæ大å ç´ ä¸æ¯æ°æ®æ¥æ¾ï¼èæ¯ç©ççI/0æä½ãå¦ï¼ããselecttop10*from(selecttop10000gid,fariqi,titlefromtgongwenwhereneibuyonghu='å
¬å®¤'orderbygiddesc)asaorderbygidascããè¿æ¡è¯å¥ï¼ä»ç论ä¸è®²ï¼æ´æ¡è¯å¥çæ§è¡æ¶é´åºè¯¥æ¯åå¥çæ§è¡æ¶é´é¿ï¼ä½äºå®ç¸åãå 为ï¼åå¥æ§è¡åè¿åçæ¯10000æ¡è®°å½ï¼èæ´æ¡è¯å¥ä»
è¿å10æ¡è¯å¥ï¼æ以影åæ°æ®åºååºæ¶é´æ大çå ç´ æ¯ç©çI/Oæä½ãèéå¶ç©çI/Oæä½æ¤å¤çææææ¹æ³ä¹ä¸å°±æ¯ä½¿ç¨TOPå
³é®è¯äºãTOPå
³é®è¯æ¯SQLSERVERä¸ç»è¿ç³»ç»ä¼åè¿çä¸ä¸ªç¨æ¥æååå æ¡æåå 个ç¾åæ¯æ°æ®çè¯ãç»ç¬è
å¨å®è·µä¸çåºç¨ï¼åç°TOPç¡®å®å¾å¥½ç¨ï¼æçä¹å¾é«ãä½è¿ä¸ªè¯å¨å¦å¤ä¸ä¸ªå¤§åæ°æ®åºORACLEä¸å´æ²¡æï¼è¿ä¸è½è¯´ä¸æ¯ä¸ä¸ªéæ¾ï¼è½ç¶å¨ORACLEä¸å¯ä»¥ç¨å
¶ä»æ¹æ³ï¼å¦ï¼rownumberï¼æ¥è§£å³ãå¨ä»¥åçå
³äºâå®ç°åä¸çº§æ°æ®çå页æ¾ç¤ºåå¨è¿ç¨âç讨论ä¸ï¼æ们就å°ç¨å°TOPè¿ä¸ªå
³é®è¯ãããå°æ¤ä¸ºæ¢ï¼æ们ä¸é¢è®¨è®ºäºå¦ä½å®ç°ä»å¤§å®¹éçæ°æ®åºä¸å¿«éå°æ¥è¯¢åºæ¨æéè¦çæ°æ®æ¹æ³ãå½ç¶ï¼æ们ä»ç»çè¿äºæ¹æ³é½æ¯â软âæ¹æ³ï¼å¨å®è·µä¸ï¼æ们è¿è¦èèåç§â硬âå ç´ ï¼å¦ï¼ç½ç»æ§è½ãæå¡å¨çæ§è½ãæä½ç³»ç»çæ§è½ï¼çè³ç½å¡ã交æ¢æºçã
温馨提示:答案为网友推荐,仅供参考