8.2.1 æ±åå½æ°ââSUM()æ±åå½æ°SUM( )ç¨äºå¯¹æ°æ®æ±åï¼è¿åéåç»æéä¸ææå¼çæ»åãè¯æ³å¦ä¸ãSELECT SUM(column_name) FROM table_name说æï¼SUM()å½æ°åªè½ä½ç¨äºæ°å¼åæ°æ®ï¼å³åcolumn_nameä¸çæ°æ®å¿
é¡»æ¯æ°å¼åçãå®ä¾1 SUMå½æ°ç使ç¨ä»TEACHER表ä¸æ¥è¯¢ææç·æå¸çå·¥èµæ»æ°ãTEACHER表çç»æåæ°æ®å¯åè§5.2.1èç表5-1ï¼ä¸åãå®ä¾ä»£ç ï¼SELECT SUM(SAL) AS BOYSALFROM TEACHERWHERE TSEX='ç·'è¿è¡ç»æå¦å¾8.1æ示ãå¾8.1 TEACHER表ä¸ææç·æå¸çå·¥èµæ»æ°å®ä¾2 SUMå½æ°å¯¹NULLå¼çå¤çä»TEACHER表ä¸æ¥è¯¢å¹´é¾å¤§äº40å²çæå¸çå·¥èµæ»æ°ãå®ä¾ä»£ç ï¼SELECT SUM(SAL) AS OLDSALFROM TEACHERWHERE AGE>=40è¿è¡ç»æå¦å¾8.2æ示ãå¾8.2 TEACHER表ä¸ææå¹´é¾å¤§äº40å²çæå¸çå·¥èµæ»æ°å½å¯¹æåæ°æ®è¿è¡æ±åæ¶ï¼å¦æ该ååå¨NULLå¼ï¼åSUMå½æ°ä¼å¿½ç¥è¯¥å¼ã8.2.2 计æ°å½æ°ââCOUNT()COUNT()å½æ°ç¨æ¥è®¡ç®è¡¨ä¸è®°å½ç个æ°æè
åä¸å¼ç个æ°ï¼è®¡ç®å
容ç±SELECTè¯å¥æå®ã使ç¨COUNTå½æ°æ¶ï¼å¿
é¡»æå®ä¸ä¸ªåçå称æè
使ç¨æå·ï¼æå·è¡¨ç¤ºè®¡ç®ä¸ä¸ªè¡¨ä¸çææè®°å½ã两ç§ä½¿ç¨å½¢å¼å¦ä¸ã COUNT(*)ï¼è®¡ç®è¡¨ä¸è¡çæ»æ°ï¼å³ä½¿è¡¨ä¸è¡çæ°æ®ä¸ºNULLï¼ä¹è¢«è®¡å
¥å¨å
ã COUNT(column)ï¼è®¡ç®columnåå
å«çè¡çæ°ç®ï¼å¦æ该åä¸æè¡æ°æ®ä¸ºNULLï¼å该è¡ä¸è®¡å
¥ç»è®¡æ»æ°ã1ï¼ä½¿ç¨COUNT(*)å½æ°å¯¹è¡¨ä¸çè¡æ°è®¡æ°COUNT(*)å½æ°å°è¿å满足SELECTè¯å¥çWHEREåå¥ä¸çæç´¢æ¡ä»¶çå½æ°ãå®ä¾3 COUNT(*)å½æ°ç使ç¨æ¥è¯¢TEACHER表ä¸çææè®°å½çè¡æ°ãå®ä¾ä»£ç ï¼SELECT COUNT(*) AS TOTALITEMFROM TEACHERè¿è¡ç»æå¦å¾8.3æ示ãå¾8.3 使ç¨COUNT(*)å½æ°å¯¹è¡¨ä¸çè¡æ°è®¡æ°å¨è¯¥ä¾ä¸ï¼SELECTè¯å¥ä¸æ²¡æWHEREåå¥ï¼é£ä¹è®¤ä¸ºè¡¨ä¸çææè¡é½æ»¡è¶³SELECTè¯å¥ï¼æ以SELECTè¯å¥å°è¿å表ä¸ææè¡ç计æ°ï¼ç»æä¸5.2.1èç表5-1ååºçTEACHER表çæ°æ®ç¸å»åãå¦æDBMSå¨å
¶ç³»ç»è¡¨ä¸åå¨äºè¡¨çè¡æ°ï¼COUNT(*)å°å¾å¿«å°è¿å表çè¡æ°ï¼å 为è¿æ¶ï¼DBMSä¸å¿
ä»å¤´å°å°¾è¯»å表ï¼å¹¶å¯¹ç©ç表ä¸çè¡è®¡æ°ï¼èç´æ¥ä»ç³»ç»è¡¨ä¸æåè¡ç计æ°ãèå¦æDBMS没æå¨ç³»ç»è¡¨åå¨è¡¨çè¡æ°ï¼å°å
·æNOT NULL约æçåä½ä¸ºåæ°ï¼ä½¿ç¨COUNT()å½æ°ï¼åå¯è½æ´å¿«å°å¯¹è¡¨è¡è®¡æ°ã 注æ COUNT(*)å½æ°å°åç¡®å°è¿å表ä¸çæ»è¡æ°ï¼èä»
å½COUNT()å½æ°çåæ°å没æNULLå¼æ¶ï¼æè¿å表ä¸æ£ç¡®çè¡è®¡æ°ï¼æ以ä»
å½åNOT NULLéå¶çåä½ä¸ºåæ°æ¶ï¼æå¯ä½¿ç¨COUNT( )å½æ°ä»£æ¿COUNT(*)å½æ°ã 2ï¼ä½¿ç¨COUNT( )å½æ°å¯¹ä¸åä¸çæ°æ®è®¡æ°COUNT( )å½æ°å¯ç¨äºå¯¹ä¸åä¸çæ°æ®å¼è®¡æ°ãä¸å¿½ç¥äºææåçCOUNT(*)å½æ°ä¸åï¼COUNT( )å½æ°éä¸æ£æ¥ä¸åï¼æå¤åï¼ä¸çå¼ï¼å¹¶å¯¹é£äºå¼ä¸æ¯NULLçè¡è®¡æ°ãå®ä¾4 æ¥è¯¢å¤åä¸ææè®°å½çè¡æ°æ¥è¯¢TEACHER表ä¸çTNOåãTNAMEå以åSALåä¸å
å«çæææ°æ®è¡æ°ãå®ä¾ä»£ç ï¼SELECT COUNT(TNO) AS TOTAL_TNO, COUNT(TNAME) AS TOTAL_TNAME, COUNT(SAL) AS TOTAL_SALFROM TEACHERè¿è¡ç»æå¦å¾8.4æ示ãå¾8.4 使ç¨COUNT( )å½æ°å¯¹ä¸åä¸çæ°æ®è®¡æ°å¯è§ï¼TNOåä¸TNAMEåç±äºå
¶ä¸ä¸å«æNULLå¼ï¼æ以å
¶è®¡æ°ä¸ä½¿ç¨COUNT(*)å½æ°å¯¹TEACHER表ä¸çè®°å½è®¡æ°ç»æç¸ä¸è´ï¼èSALåç±äºå
¶ä¸æ两è¡æ°æ®ä¸ºNULLï¼æ以è¿ä¸¤å没æ被计å
¥å¨å
ï¼è®¡æ°ç»æä¹å°±æ¯8ã3ï¼ä½¿ç¨COUNT( )å½æ°å¯¹å¤åä¸çæ°æ®è®¡æ°COUNT( )å½æ°ä¸ä»
å¯ç¨äºå¯¹ä¸åä¸çæ°æ®å¼è®¡æ°ï¼ä¹å¯ä»¥å¯¹å¤åä¸çæ°æ®å¼è®¡æ°ãå¦æ对å¤å计æ°ï¼åéè¦å°è¦è®¡æ°çå¤åéè¿è¿æ¥ç¬¦è¿æ¥åï¼ä½ä¸ºCOUNT( )å½æ°çåæ°ãä¸é¢å°ç»åå
·ä½çå¤å计æ°çå®ä¾ï¼è¯´æå
¶ä½¿ç¨è¿ç¨ã 说æ å
³äºå¦ä½ä½¿ç¨è¿æ¥ç¬¦è¿æ¥å¤åå¯åè§æ¬ä¹¦ç7.2èã å®ä¾5 使ç¨COUNT( )å½æ°å¯¹å¤åä¸çæ°æ®è®¡æ°ç»è®¡TEACHER表ä¸çTNOåãTNAMEååSALåä¸åå«å
å«çæ°æ®è¡æ°ï¼ä»¥åTNOååTNAMEåãTNAMEååSALåä¸èµ·å
å«çæ°æ®è¡æ°ãå®ä¾ä»£ç ï¼SELECT COUNT(TNO) AS TOTAL_TNO, COUNT(TNAME) AS TOTAL_TNAME, COUNT(SAL) AS TOTAL_SAL, COUNT(CAST(TNO AS VARCHAR(5)) + TNAME) AS T_NONAME, COUNT(TNAME + CAST(SAL AS VARCHAR(5))) AS T_NAMESALFROM TEACHERè¿è¡ç»æå¦å¾8.5æ示ãå¾8.5 使ç¨COUNT( )å½æ°å¯¹å¤åä¸çæ°æ®è®¡æ°å¨è¿è¡ä¸¤åçè¿æ¥æ¶ï¼ç±äºå®ä»¬çæ°æ®ç±»åä¸ä¸è´ï¼å æ¤è¦ä½¿ç¨CAST表达å¼å°å®ä»¬è½¬æ¢æç¸åçæ°æ®ç±»åãå¨7.2.1èå·²ç»è®²è¿ï¼å¦æå¨è¢«è¿æ¥çåä¸çä»»ä½ä¸åæNULLå¼æ¶ï¼é£ä¹è¿æ¥çç»æ为NULLï¼å该åä¸ä¼è¢«COUNT( )å½æ°è®¡æ°ã 注æ COUNT( )å½æ°åªå¯¹é£äºä¼ éå°å½æ°ä¸çåæ°ä¸æ¯NULLçè¡è®¡æ°ã 4ï¼ä½¿ç¨COUNTå½æ°å¯¹æ»¡è¶³æç§æ¡ä»¶çè®°å½è®¡æ°ä¹å¯ä»¥å¨SELECTè¯å¥ä¸æ·»å ä¸äºåå¥çº¦ææ¥æå®è¿åè®°å½ç个æ°ãå®ä¾6 使ç¨COUNTå½æ°å¯¹æ»¡è¶³æç§æ¡ä»¶çè®°å½è®¡æ°æ¥è¯¢TEACHER表ä¸å¥³æå¸è®°å½çæ°ç®ãå®ä¾ä»£ç ï¼SELECT COUNT(*) AS TOTALWOMENFROM TEACHERWHERE TSEX='女'è¿è¡ç»æå¦å¾8.6æ示ãå¾8.6 使ç¨COUNTå½æ°å¯¹æ»¡è¶³æç§æ¡ä»¶çè®°å½è®¡æ°è¿æ¶ç»æ为6èä¸æ¯åé¢çææè®°å½10ãä¹æ以å¯ä»¥éè¿WHEREåå¥å®ä¹COUNT()å½æ°ç计æ°æ¡ä»¶ï¼è¿ä¸SELECTè¯å¥å个åå¥çæ§è¡é¡ºåºæ¯åä¸å¼çãåé¢å·²ç»è®²è¿ï¼DBMSé¦å
æ§è¡FROMåå¥ï¼èåæ¯WHEREåå¥ï¼æåæ¯SELECTåå¥ãæ以COUNT()å½æ°åªè½ç¨äºæ»¡è¶³WHEREåå¥å®ä¹çæ¥è¯¢æ¡ä»¶çè®°å½ã没æå
æ¬å¨WHEREåå¥çæ¥è¯¢ç»æä¸çè®°å½ï¼é½ä¸ç¬¦åCOUNT()å½æ°ã8.2.3 æ大/æå°å¼å½æ°âMAX()/MIN()å½éè¦äºè§£ä¸åä¸çæ大å¼æ¶ï¼å¯ä»¥ä½¿ç¨MAX()å½æ°ï¼åæ ·ï¼å½éè¦äºè§£ä¸åä¸çæå°å¼æ¶ï¼å¯ä»¥ä½¿ç¨MIN()å½æ°ãè¯æ³å¦ä¸ãSELECT MAX (column_name) / MIN (column_name)FROM table_name说æï¼åcolumn_nameä¸çæ°æ®å¯ä»¥æ¯æ°å¼ãå符串ææ¯æ¥ææ¶é´æ°æ®ç±»åãMAX()/MIN()å½æ°å°è¿åä¸è¢«ä¼ éçååä¸æ°æ®ç±»åçåä¸å¼ãå®ä¾7 MAX()å½æ°ç使ç¨æ¥è¯¢TEACHER表ä¸æå¸çæ大年é¾ãå®ä¾ä»£ç ï¼SELECT MAX (AGE) AS MAXAGEFROM TEACHERè¿è¡ç»æå¦å¾8.7æ示ãå¾8.7 TEACHER表ä¸æå¸çæ大年é¾ç¶èï¼å¨å®é
åºç¨ä¸å¾å°è¿ä¸ªç»æ并ä¸æ¯ç¹å«æç¨ï¼å 为ç»å¸¸æ³è¦è·å¾çä¿¡æ¯æ¯å
·ææ大年é¾çæå¸çæå·¥å·ãå§åãæ§å«çä¿¡æ¯ãç¶èSQLä¸æ¯æå¦ä¸çSELECTè¯å¥ãSELECT TNAME, DNAME, TSEX, MAX (AGE)FROM TEACHERå 为èåå½æ°å¤ççæ¯æ°æ®ç»ï¼å¨æ¬ä¾ä¸ï¼MAXå½æ°å°æ´ä¸ªTEACHER表çæä¸ç»ï¼èTNAMEãDNAMEåTSEXçæ°æ®é½æ²¡æè¿è¡ä»»ä½åç»ï¼å æ¤SELECTè¯å¥æ²¡æé»è¾æä¹ãåæ ·çéçï¼ä¸é¢ç代ç ä¹æ¯æ æçãSELECT TNAME, DNAME, TSEX,SAL ,AGEFROM TEACHERWHERE AGEï¼MAX (AGE)解å³è¿ä¸ªé®é¢çæ¹æ³ï¼å°±æ¯å¨WHEREåå¥ä¸ä½¿ç¨åæ¥è¯¢æ¥è¿åæ大å¼ï¼ç¶åååºäºè¿ä¸ªè¿åçæ大å¼ï¼æ¥è¯¢ç¸å
³ä¿¡æ¯ãå®ä¾8 å¨WHEREåå¥ä¸ä½¿ç¨åæ¥è¯¢è¿åæ大å¼æ¥è¯¢TEACHER表ä¸å¹´çºªæ大çæå¸çæå·¥å·ãå§åãæ§å«çä¿¡æ¯ãå®ä¾ä»£ç ï¼SELECT TNAME, DNAME, TSEX, SAL, AGEFROM TEACHERWHERE AGEï¼(SELECT MAX (AGE) FROM TEACHER)è¿è¡ç»æå¦å¾8.8æ示ãå¾8.8 å¨WHEREåå¥ä¸ä½¿ç¨åæ¥è¯¢è¿åæ大å¼MAX()åMIN()å½æ°ä¸ä»
å¯ä»¥ä½ç¨äºæ°å¼åæ°æ®ï¼ä¹å¯ä»¥ä½ç¨äºå符串ææ¯æ¥ææ¶é´æ°æ®ç±»åçæ°æ®ãå®ä¾9 MAX()å½æ°ç¨äºå符åæ°æ®å¦ä¸é¢ä»£ç ï¼SELECT MAX (TNAME) AS MAXNAMEFROM TEACHERè¿è¡ç»æå¦å¾8.9æ示ãå¾8.9 å¨å符串æ°æ®ç±»åä¸ä½¿ç¨MAXçç»æå¯è§ï¼å¯¹äºå符串ä¹å¯ä»¥æ±å
¶æ大å¼ã 说æ 对å符åæ°æ®çæ大å¼ï¼æ¯æç
§é¦åæ¯ç±Aï½Zç顺åºæåï¼è¶å¾åï¼å
¶å¼è¶å¤§ãå½ç¶ï¼å¯¹äºæ±ååæ¯æç
§å
¶å
¨æ¼æ¼é³æåçï¼è¥é¦å符ç¸åï¼åæ¯è¾ä¸ä¸ä¸ªå符ï¼ä»¥æ¤ç±»æ¨ã å½ç¶ï¼å¯¹ä¸æ¥ææ¶é´ç±»åçæ°æ®ä¹å¯ä»¥æ±å
¶æ大/æå°å¼ï¼å
¶å¤§å°æåå°±æ¯æ¥ææ¶é´çæ©æï¼è¶æ©è®¤ä¸ºå
¶å¼è¶å°ï¼å¦ä¸é¢çå®ä¾ãå®ä¾10 MAX()ãMIN()å½æ°ç¨äºæ¶é´åæ°æ®ä»COURSE表ä¸æ¥è¯¢ææ©åææèè¯è¯¾ç¨çèè¯æ¶é´ãå
¶ä¸COURSE表çç»æåæ°æ®å¯åè§æ¬ä¹¦6.1èç表6-1ãå®ä¾ä»£ç ï¼SELECT MIN (CTEST) AS EARLY_DATE, MAX (CTEST) AS LATE_DATEFROM COURSEè¿è¡ç»æå¦å¾8.10æ示ãå¾8.10 COURSE表ä¸ææ©åææèè¯è¯¾ç¨çèè¯æ¶é´å¯è§ï¼è¿åç»æçæ°æ®ç±»åä¸è¯¥åå®ä¹çæ°æ®ç±»åç¸åã 注æ ç¡®å®åä¸çæ大å¼ï¼æå°å¼ï¼æ¶ï¼MAX( )ï¼MIN( )ï¼å½æ°å¿½ç¥NULLå¼ãä½æ¯ï¼å¦æå¨è¯¥åä¸ï¼ææè¡çå¼é½æ¯NULLï¼åMAX( )/MIN( )å½æ°å°è¿åNULLå¼ã 8.2.4 åå¼å½æ°ââAVG()å½æ°AVG()ç¨äºè®¡ç®ä¸åä¸æ°æ®å¼çå¹³åå¼ãè¯æ³å¦ä¸ãSELECT AVG (column_name)FROM table_name说æï¼AVG()å½æ°çæ§è¡è¿ç¨å®é
ä¸æ¯å°ä¸åä¸çå¼å èµ·æ¥ï¼åå°å
¶åé¤ä»¥éNULLå¼çæ°ç®ãæ以ï¼ä¸SUM( )å½æ°ä¸æ ·ï¼AVG()å½æ°åªè½ä½ç¨äºæ°å¼åæ°æ®ï¼å³åcolumn_nameä¸çæ°æ®å¿
é¡»æ¯æ°å¼åçãå®ä¾11 AVG()å½æ°çåºç¨ä»TEACHER表ä¸æ¥è¯¢æææå¸çå¹³åå¹´é¾ãå®ä¾ä»£ç ï¼SELECT AVG (AGE) AS AVG_AGEFROM TEACHERè¿è¡ç»æå¦å¾8.11æ示ãå¾8.11 TEACHER表ä¸æææå¸çå¹³åå¹´é¾å¨è®¡ç®å¹³åå¼æ¶ï¼AVG()å½æ°å°å¿½ç¥NULLå¼ãå æ¤ï¼å¦æè¦è®¡ç®å¹³åå¼çåä¸æNULLå¼ï¼è®¡ç®åå¼æ¶ï¼è¦ç¹å«æ³¨æãå®ä¾12 AVG()å½æ°å¯¹NULLå¼çå¤çä»TEACHER表ä¸æ¥è¯¢æææå¸çå¹³åå·¥èµãå®ä¾ä»£ç ï¼SELECT AVG (SAL) AS AVG_AGE1ï¼SUM(SAL)/COUNT(*) AS AVG_AGE2, SUM(SAL)/COUNT(SAL) AS AVG_AGE3 FROM TEACHERè¿è¡ç»æå¦å¾8.12æ示ãå¾8.12 TEACHER表ä¸æææå¸çå¹³åå·¥èµå¯ä»¥åç°å¾å°äºä¸åçç»æãå®é
ä¸ï¼âAVG(SAL)âä¸âSUM(SAL)/COUNT(SAL)âè¯å¥æ¯çä»·çãå 为AVG(SAL)è¯å¥çæ§è¡è¿ç¨å®é
ä¸æ¯å°SALåä¸çå¼å èµ·æ¥ï¼åå°å
¶åï¼ä¹å°±çä»·äºSUM(SAL)ï¼é¤ä»¥éNULLå¼çæ°ç®ï¼ä¹å°±çä»·äºCOUNT(SAL)ï¼ãèè¯å¥âSUM(SAL)/COUNT(*)âåä¸ç¶ï¼å 为COUNT(*)è¿åçæ¯è¡¨ä¸ææè®°å½ç个æ°ï¼èä¸ç®¡SALåä¸çæ°å¼æ¯å¦ä¸ºNULLã 注æ AVG()å½æ°å¨è®¡ç®ä¸åçå¹³åå¼æ¶ï¼å¿½ç¥NULLå¼ãä½æ¯ï¼å¦æå¨è¯¥åä¸ï¼ææè¡çå¼é½æ¯NULLï¼åAVG()å½æ°å°è¿åNULLå¼ã å¦æä¸æ³å¯¹åä¸çææå¼æ±å¹³åï¼åå¯å¨WHEREåå¥ä¸ä½¿ç¨æç´¢æ¡ä»¶æ¥éå¶ç¨äºè®¡ç®åå¼çè¡ãå®ä¾13 å¨WHEREåå¥ä¸ä½¿ç¨æç´¢æ¡ä»¶æ¥éå¶ç¨äºè®¡ç®åå¼çè¡ä»TEACHER表ä¸æ¥è¯¢ææ计ç®æºç³»æå¸çå¹³åå¹´é¾ãå®ä¾ä»£ç ï¼SELECT AVG (AGE) AS AVGCOMPUTER_AGEFROM TEACHERWHERE DNAME = '计ç®æº'è¿è¡ç»æå¦å¾8.13æ示ãå¾8.13 TEACHER表ä¸ææ计ç®æºç³»æå¸çå¹³åå¹´é¾å½æ§è¡SELECTè¯å¥æ¶ï¼DBMSå°è¡¨ä¸çæ¯è¡å¯¹WHEREåå¥ä¸çæç´¢æ¡ä»¶âDNAME = '计ç®æº'âæ±å¼ãåªæé£äºæç´¢æ¡ä»¶ä¸ºTrueæ¶ï¼è¡ä¸çAGEå¼æä¼ å°åå¼å½æ°AVG (AGE)ä¸ãå½ç¶ï¼é¤äºæ¾ç¤ºè¡¨ä¸æåçå¹³åå¼ï¼è¿å¯ç¨AVG()å½æ°ä½ä¸ºWHEREåå¥çä¸é¨åãä¸åé¢ä»ç»çMAX()å½æ°ä¸æ ·ï¼ä¸è½ç´æ¥ç¨äºWHEREåå¥ï¼å¿
须以åæ¥è¯¢çå½¢å¼ãå®ä¾14 AVG()å½æ°ä½ä¸ºWHEREåå¥ä¸æç´¢æ¡ä»¶çä¸é¨åä»TEACHER表ä¸æ¥è¯¢ææå¹´é¾é«äºå¹³åå¹´é¾çæå¸çä¿¡æ¯ãå®ä¾ä»£ç ï¼SELECT *FROM TEACHERWHERE AGE >= (SELECT AVG (AGE) FROM TEACHER)ORDER BY AGEè¿è¡ç»æå¦å¾8.14æ示ãå¾8.14 TEACHER表ä¸ææå¹´é¾é«äºå¹³åå¹´é¾çæå¸çä¿¡æ¯8.2.5 èååæçéå¼å¤çåé¢ä»ç»ç5ç§èåå½æ°ï¼å¯ä»¥ä½ç¨äºæéåä¸çæææ°æ®ï¼ä¸ç®¡åä¸çæ°æ®æ¯å¦æéç½®ï¼ï¼ä¹å¯ä»¥åªå¯¹åä¸çééå¼è¿è¡å¤çï¼å³æéå¤çå¼åªåä¸æ¬¡è¿è¡èååæãå½ç¶ï¼å¯¹äºMAX()/MIN()å½æ°æ¥è®²ï¼éå¼å¤çæä¹ä¸å¤§ãå¯ä»¥ä½¿ç¨ALLå
³é®åææ对æéåä¸çæææ°æ®è¿è¡å¤çï¼ä½¿ç¨DISTINCTå
³é®åææ对æéåä¸çééå¼æ°æ®è¿è¡å¤çã以AVG()å½æ°ä¸ºä¾ï¼è¯æ³å¦ä¸ãSELECT AVG ([ALL/DISTINCT] column_name)FROM table_name说æï¼[ALL/DISTINCT]å¨ç¼ºçç¶æä¸ï¼é»è®¤æ¯ALLå
³é®åï¼å³ä¸ç®¡æ¯å¦æéå¼ï¼å¤çæææ°æ®ãå
¶ä»èåå½æ°çç¨æ³ä¸æ¤ç¸åã 注æ Microsoft Accessæ°æ®åºä¸æ¯æå¨èåå½æ°ä¸ä½¿ç¨DISTINCTå
³é®åã å®ä¾15 èååæçéå¼å¤çä»TEACHER表ä¸æ¥è¯¢å·¥èµSALåä¸åå¨çææè®°å½æ°ãå®ä¾ä»£ç ï¼SELECT COUNT(ALL SAL) AS ALLSAL_COUNTFROM TEACHERè¿è¡ç»æå¦å¾8.15æ示ãå¾8.15 TEACHER表ä¸å·¥èµSALåä¸åå¨çææè®°å½æ°å½ç¶ï¼å¨ä»£ç ä¸å»é¤ALLå
³é®åï¼ä¹å¯ä»¥å¾å°ç¸åçç»æãèå¦æä»TEACHER表ä¸ï¼æ¥è¯¢å·¥èµSALåä¸åå¨çä¸åè®°å½çæ°ç®ï¼å¯éç¨å¦ä¸ä»£ç ãSELECT COUNT(DISTINCT SAL) AS DISTINCTSAL_COUNTFROM TEACHERè¿è¡ç»æå¦å¾8.16æ示ãå¾8.16 TEACHER表ä¸SALååå¨çä¸åè®°å½çæ°ç®å¯¹æ¯ä¸¤ä¸ªç»æï¼ä½¿ç¨DISTINCTå
³é®ååï¼å·¥èµSALåä¸çéå¼å¹¶æ²¡æåå
¥ç»è®¡çèå´ä¹å
ãå¦å¤è¿è¦å¼ºè°ä¸ç¹ï¼å¨ææ5ç§èåå½æ°ä¸ï¼é¤äºCOUNT(*)å½æ°å¤ï¼å
¶ä»çå½æ°å¨è®¡ç®è¿ç¨ä¸é½å¿½ç¥NULLå¼ï¼å³æNULLå¼çè¡æé¤å¨å¤ï¼ä¸è¿è¡åæã8.2.6 èåå½æ°çç»å使ç¨åé¢ä»ç»çå®ä¾ä¸ï¼èåå½æ°é½æ¯åç¬ä½¿ç¨çãèåå½æ°ä¹å¯ä»¥ç»å使ç¨ï¼å³å¨ä¸æ¡SELECTè¯å¥ä¸ï¼å¯ä»¥ä½¿ç¨å¤ä¸ªèåå½æ°ãå®ä¾16 使ç¨å¤ä¸ªèåå½æ°å¦ä¸é¢ç代ç ï¼SELECT COUNT(*) AS num_items, MAX(SAL) AS max_sal, Min(AGE) AS min_age, SUM(SAL)/COUNT(SAL) AS avg_sal, AVG(DISTINCT SAL) AS disavg_salFROM TEACHERè¿è¡ç»æå¦å¾8.17æ示ãå¾8.17 èåå½æ°çç»ååºç¨è¯¥ä¾å¨ä¸æ¡SELECTè¯å¥ä¸ï¼å ä¹ç¨å°äºææçèåå½æ°ãå
¶ä¸num_items为TEACHER表ææè®°å½çæ¡ç®ï¼max_sal为TEACHER表ä¸è®°å½çæé«å·¥èµï¼min_age为TEACHER表ä¸è®°å½çæå°å¹´é¾ï¼avg_sal为ææTEACHER表ä¸çå·¥èµè®°å½çå¹³åå¼ï¼disavg_sal为TEACHER表ä¸ææä¸åçå·¥èµè®°å½çå¹³åå¼ã
温馨提示:答案为网友推荐,仅供参考