ï¼1.)selectè¯å¥çæ§è¡é¡ºåº
Processing Order of the SELECT statement
The following steps show the processing order for a SELECT statement.
1.FROM
2.ON
3.JOIN
4.WHERE
5.GROUP BY
6.WITH CUBE or WITH ROLLUP
7.HAVING
8.SELECT
9.DISTINCT
10.ORDER BY
11.TOP
(5)SELECT DISTINCT
ï¼7ï¼TOP(<top_specification>) <select_list>
(1)FROM <left_table> <join_type> JOIN <right_table> ON <on_predicate>
(2)WHERE <where_predicate>
(3)GROUP BY <group_by_specification>
(4)HAVING <having_predicate>
(6)ORDER BY <order_by_list>
T-SQLå¨æ¥è¯¢å个é¶çº§åå«å¹²äºä»ä¹ï¼
ï¼1ï¼FROM é¶æ®µ
FROMé¶æ®µæ è¯åºæ¥è¯¢çæ¥æºè¡¨ï¼å¹¶å¤ç表è¿ç®ç¬¦ãå¨æ¶åå°èæ¥è¿ç®çæ¥è¯¢ä¸ï¼åç§joinï¼ï¼ä¸»è¦æ以ä¸å 个æ¥éª¤ï¼
a.æ±ç¬å¡å°ç§¯ãä¸è®ºæ¯ä»ä¹ç±»åçèæ¥è¿ç®ï¼é¦å
é½æ¯æ§è¡äº¤åè¿æ¥ï¼cross joinï¼ï¼æ±ç¬å¡å¿ç§¯ï¼çæèæ表VT1-J1ã
b.ONçéå¨ãè¿ä¸ªé¶æ®µå¯¹ä¸ä¸ªæ¥éª¤çæçVT1-J1è¿è¡çéï¼æ ¹æ®ONåå¥ä¸åºç°çè°è¯è¿è¡çéï¼è®©è°è¯åå¼ä¸ºtrueçè¡éè¿äºèéªï¼æå
¥å°VT1-J2ã
c.æ·»å å¤é¨è¡ãå¦ææå®äºouter joinï¼è¿éè¦å°VT1-J2ä¸æ²¡ææ¾å°å¹é
çè¡ï¼ä½ä¸ºå¤é¨è¡æ·»å å°VT1-J2ä¸ï¼çæVT1-J3ã
ç»è¿ä»¥ä¸æ¥éª¤ï¼FROMé¶æ®µå°±å®æäºãæ¦æ¬å°è®²ï¼FROMé¶æ®µå°±æ¯è¿è¡é¢å¤ççï¼æ ¹æ®æä¾çè¿ç®ç¬¦å¯¹è¯å¥ä¸æå°çå个表è¿è¡å¤çï¼é¤äºjoinï¼è¿æapplyï¼pivotï¼unpivotï¼
ï¼2ï¼WHEREé¶æ®µ
WHEREé¶æ®µæ¯æ ¹æ®<where_predicate>ä¸æ¡ä»¶å¯¹VT1ä¸çè¡è¿è¡çéï¼è®©æ¡ä»¶æç«çè¡æä¼æå
¥å°VT2ä¸ã
ï¼3ï¼GROUP BYé¶æ®µ
GROUPé¶æ®µæç
§æå®çååå表ï¼å°VT2ä¸çè¡è¿è¡åç»ï¼çæVT3ãæåæ¯ä¸ªåç»åªæä¸è¡ã
ï¼4ï¼HAVINGé¶æ®µ
该é¶æ®µæ ¹æ®HAVINGåå¥ä¸åºç°çè°è¯å¯¹VT3çåç»è¿è¡çéï¼å¹¶å°ç¬¦åæ¡ä»¶çç»æå
¥å°VT4ä¸ã
ï¼5ï¼SELECTé¶æ®µ
è¿ä¸ªé¶æ®µæ¯æå½±çè¿ç¨ï¼å¤çSELECTåå¥æå°çå
ç´ ï¼äº§çVT5ãè¿ä¸ªæ¥éª¤ä¸è¬æä¸å顺åºè¿è¡
a.计ç®SELECTå表ä¸ç表达å¼ï¼çæVT5-1ã
b.è¥æDISTINCTï¼åå é¤VT5-1ä¸çéå¤è¡ï¼çæVT5-2
c.è¥æTOPï¼åæ ¹æ®ORDER BYåå¥å®ä¹çé»è¾é¡ºåºï¼ä»VT5-2ä¸éæ©ç¾åæå®æ°éæè
ç¾åæ¯çè¡ï¼çæVT5-3
ï¼6ï¼ORDER BYé¶æ®µ
æ ¹æ®ORDER BYåå¥ä¸æå®çåæå表ï¼å¯¹VT5-3ä¸çè¡ï¼è¿è¡æåºï¼çæ游æ VC6.
å¦æOnåwhereåªè½éå
¶ä¸çè¯ï¼
å
è¿è¡onçè¿æ»¤, èåæè¿è¡join, è¿æ ·å°±é¿å
äºä¸¤ä¸ªå¤§è¡¨äº§çå
¨é¨æ°æ®çç¬å¡å°ç§¯çåºå¤§æ°æ®.
è¿äºæ¥éª¤æ§è¡æ¶, æ¯ä¸ªæ¥éª¤é½ä¼äº§çä¸ä¸ªèæ表ï¼è¯¥èæ表被ç¨ä½ä¸ä¸ä¸ªæ¥éª¤çè¾å
¥ãè¿äºèæ表对è°ç¨è
ï¼å®¢æ·ç«¯åºç¨ç¨åºæè
å¤é¨æ¥è¯¢ï¼ä¸å¯ç¨ãåªæ¯æåä¸æ¥çæç表æä¼è¿å ç»è°ç¨è
ã
å¦æ没æå¨æ¥è¯¢ä¸æå®æä¸åå¥ï¼å°è·³è¿ç¸åºçæ¥éª¤ã
ï¼2ï¼ é£ on åwhere é£ä¸ªæ´é«æå¢
å¦ææ¯inner join, æ¾onåæ¾where产ççç»æä¸æ ·, ä½æ²¡è¯´åªä¸ªæçé度æ´é«? å¦ææouter join (left or right), å°±æåºå«äº, å 为onçæå¨å
, å·²ç»æåè¿æ»¤äºä¸é¨åæ°æ®, èwhereçæå¨å.
综åä¸ä¸, æè§è¿æ¯æ¾å¨onéæ´ææç, å 为å®å
äºwhereæ§è¡.
å
ç¬å¡å°ç§¯, ç¶ååonè¿æ»¤, å¦æjoinæ¯innerç, 就继ç»å¾ä¸èµ°, å¦æjoin æ¯left join, å°±æonè¿æ»¤æç左主表ä¸çæ°æ®åæ·»å åæ¥; ç¶ååæ§è¡whereéçè¿æ»¤;
onä¸ä¸æ¯æç»è¿æ»¤, å 为åé¢left joinè¿å¯è½æ·»å åæ¥, èwhereææ¯æç»è¿æ»¤.
åªæå½ä½¿ç¨å¤è¿æ¥(left, right)æ¶, on å where ææè¿ä¸ªåºå«, å¦æç¨inner join, å¨åªéå¶å®é½ä¸æ ·, å 为on ä¹åå°±æ¯where, ä¸é´æ²¡æå
¶å®æ¥éª¤.
温馨提示:答案为网友推荐,仅供参考