sql ³£ÓþۺϺ¯Êý
8.2 ¾ÛºÏº¯ÊýµÄÓ¦ÓÃ
¾ÛºÏº¯ÊýÔÚÊý¾Ý¿âÊý¾ÝµÄ²éѯ·ÖÎöÖУ¬Ó¦ÓÃÊ®·Ö¹ã·º¡£±¾½Ú½«·Ö±ð¶Ô¸÷¾ÛºÏº¯ÊýµÄÓ¦ÓýøÐÐ˵Ã÷¡£
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 BOYSAL
from TEACHER
WHERE TSEX='ÄÐ'
ÔËÐнá¹ûÈçͼ8.1Ëùʾ¡£
ͼ8.1 TEACHER±íÖÐËùÓÐÄнÌʦµÄ¹¤×Ê×ÜÊý
ʵÀý2 SUMº¯Êý¶ÔNULLÖµµÄ´¦Àí
´ÓTEACHER±íÖвéѯÄêÁä´óÓÚ40ËêµÄ½ÌʦµÄ¹¤×Ê×ÜÊý¡£ÊµÀý´úÂ룺
SELECT SUM(SAL) AS OLDSAL
from TEACHER
WHERE AGE>=40
ÔËÐнá¹ûÈçͼ8.2Ëùʾ¡£
ͼ8.2 TEACHER±íÖÐËùÓÐÄêÁä´óÓÚ40ËêµÄ½ÌʦµÄ¹¤×Ê×ÜÊý
µ±¶ÔijÁÐÊý¾Ý½øÐÐÇóºÍʱ£¬Èç¹û¸ÃÁдæÔÚNULLÖµ£¬ÔòSUMº¯Êý»áºöÂÔ¸ÃÖµ¡£
8.2.2 ¼ÆÊýº¯Êý——COUNT()
COUNT()º¯ÊýÓÃÀ´¼ÆËã±íÖмǼµÄ¸öÊý»òÕßÁÐÖÐÖµµÄ¸öÊý£¬¼ÆËãÄÚÈÝÓÉSELECTÓï¾äÖ¸¶¨¡£Ê¹ÓÃCOUNTº¯Êýʱ£¬±ØÐëÖ¸¶¨Ò»¸öÁеÄÃû³Æ»òÕßʹÓÃÐǺţ¬ÐǺűíʾ¼ÆËãÒ»¸ö±íÖеÄËùÓмǼ¡£Á½ÖÖʹÓÃÐÎʽÈçÏ¡£
COUNT(*)£¬¼ÆËã±íÖÐÐеÄ×ÜÊý£¬¼´Ê¹±íÖÐÐеÄÊý¾ÝΪNULL£¬Ò²±»¼ÆÈëÔÚÄÚ¡£
COUNT(column)£¬¼ÆËãcolumnÁаüº¬µÄÐеÄÊýÄ¿£¬Èç¹û¸ÃÁÐÖÐijÐÐÊý¾ÝΪNULL£¬Ôò¸ÃÐв»¼ÆÈëͳ¼Æ×ÜÊý¡£
1£®Ê¹ÓÃCOUNT(*)º¯Êý¶Ô±íÖеÄÐÐÊý¼ÆÊý
COUNT(*)º¯Êý½«·µ»ØÂú×ãSELECTÓï¾äµÄWHERE×Ó¾äÖеÄËÑË÷Ìõ¼þµÄº¯Êý¡£
ʵÀý3 COUNT(*)º¯ÊýµÄʹÓÃ
²éѯTEACHER±íÖеÄËùÓмǼµÄÐÐÊý¡£ÊµÀý´úÂ룺
SELECT COUNT(*) AS TOTALITEM
from TEACHER
ÔËÐнá¹ûÈçͼ8.3Ëùʾ¡£
ͼ8.3 ʹÓÃCOUNT(*)º¯Êý¶Ô±íÖеÄÐÐÊý¼ÆÊý
ÔÚ¸ÃÀýÖУ¬SELECTÓï¾äÖÐûÓÐWHERE×Ӿ䣬ÄÇôÈÏΪ±íÖеÄËùÓÐÐж¼Âú×ãSELECTÓï¾ä£¬ËùÒÔSELECTÓï¾ä½«·µ»Ø±íÖÐËùÓÐÐеļÆÊý£¬½á¹ûÓë5.2.1½ÚµÄ±í5-1ÁгöµÄTEACHER±íµÄÊý¾ÝÏàÎǺϡ£
Èç¹ûDBMSÔÚÆäϵͳ±íÖд
Ïà¹ØÎĵµ£º
¿Î³ÌÊ®¶þ ±àд¿ØÖƽṹÓï¾ä
¡¡¡¡
¡¡¡¡±¾¿ÎÖص㣺
¡¡¡¡1¡¢½á¹¹¿ØÖƵĵÄÓÃ;ºÍÀàÐÍ
¡¡¡¡2¡¢IF ½á¹¹
¡¡¡¡3¡¢¹¹ÔìºÍ±êʶ²»Í¬µÄÑ»·
¡¡¡¡4¡¢Ê¹ÓÃÂß¼±í
¡¡¡¡5¡¢¿ØÖÆÁ÷ºÍǶÌ×
¡¡¡¡
¡¡¡¡×¢Ò⣺ÒÔÏÂʵÀýÖбêµã¾ùΪӢÎÄ°ë½Ç
¡¡¡¡
¡¡¡¡Ò»¡¢¿ØÖÆÖ´ÐÐÁ÷
¡¡¡¡¿ÉÒÔÊÇ·ÖÖ§ºÍÑ»·£ºIF THEN END IF
¡¡¡¡IF condition THEN ......
½ñÌìÐÇÆÚÌì,ÒòÊý¾Ý¿âÌ«Âý,×îºó¾ö¶¨½«Êý¾Ý¿â½øÐÐÖØÐÂÕûÀí.
(¼Ù¶¨Êý¾Ý¿âÃû³ÆΪ£ºDB_ste)
1¡¢¸ù¾ÝÏÖÔÚµÄÊý¾Ý¿âµÄ½Å±¾´´½¨Ò»¸ö½Å±¾Îļþ£¨FILENAME£ºDB_ste.sql)
2¡¢½¨Á¢ÐµÄÊý¾Ý¿âDB_ste2,ÈôÓÐÎļþ×éµÄÊý¾Ý¿â,ÔòÐèÒª½¨Á¢ÏàͬµÄÎļþ×é¡££¨DB_ste_Group)
3¡¢½«Êý¾ÝÎÄ ......
Microsoft SQL Server ±í²»Ó¦¸Ã°üº¬Öظ´ÐкͷÇΨһÖ÷¼ü¡£Îª¼ò½àÆð¼û£¬ÔÚ±¾ÎÄÖÐÎÒÃÇÓÐʱ³ÆÖ÷¼üΪ“¼ü”»ò“PK”£¬µ«ÕâʼÖÕ±íʾ“Ö÷¼ü”¡£Öظ´µÄ PK Î¥·´ÁËʵÌåÍêÕûÐÔ£¬ÔÚ¹ØϵϵͳÖÐÊDz»ÔÊÐíµÄ¡£SQL Server Óи÷ÖÖÇ¿ÖÆÖ´ÐÐʵÌåÍêÕûÐԵĻúÖÆ£¬°üÀ¨Ë÷Òý¡¢Î¨Ò»Ô¼Êø¡¢Ö÷¼üÔ¼Êøº ......
3¡£±íÄÚÈÝÈçÏÂ
-----------------------------
ID LogTime
1 2008/10/10 10:00:00
1 2008/10/10 10:03:00
1 2008/10/10 10:09:00
2 ¡ ......
×÷Õß Haidong Ji ·Òë GoodKid
ÎÒÃǵ±ÖеĴ󲿷ÖÈ˹¤×÷ÔÚÒ»¸öµ¥Ò»µÄ RDBMS ϵͳÖУ¬Èç MSSQL, Oracle, or IBM DB2¡£È»¶ø£¬ÎÒÃÇÈÕÒæ¸Ð¾õµ½£¬ÎÒÃÇÕý´¦ÓÚ²»Í¬µÄÊý¾Ý¿â»·¾³µ±Öв¢ÇÒÐèÒª½â¾öÊý¾ÝµÄ»¥ÓÃÐÔÎÊÌâ¡£
¾¡¹ÜÖ÷ÒªµÄ RDBMS ³§ÉÌÊÔͼȥ×ñѹØϵÊý¾Ý¿âÄ£ÐÍÔÀí£¬²¢ÇÒÓ÷dz£Ð¡µÄ²îÒìȥʵÏÖËüÃÇ¡£ÁíÍ⣬¼¸ºõÖ÷ÒªµÄ ......