1.?GROUP BY（聚合）函數(shù)
本章論述了用于一組數(shù)值操作的?group (集合)函數(shù)。除非另作說明，?group?函數(shù)會忽略?null?值。
假如你在一個不包含?ROUP BY子句的語句中使用一個?group函數(shù) ，它相當(dāng)于對所有行進(jìn)行分組。
AVG([DISTINCT]?expr)
返回expr?的平均值。?DISTINCT?選項可用于返回?expr的不同值的平均值。
若找不到匹配的行，則AVG()返回?NULL?。
mysql>?SELECT?student_name,?AVG(test_score) ->?FROM?student ->?GROUP?BY?student_name;
BIT_AND(expr)
返回expr中所有比特的?bitwise AND?。計算執(zhí)行的精確度為64比特(BIGINT)?。
若找不到匹配的行，則這個函數(shù)返回?18446744073709551615?。(這是無符號?BIGINT?值，所有比特被設(shè)置為?1）。
BIT_OR(expr)
返回expr?中所有比特的bitwise OR。計算執(zhí)行的精確度為64比特(BIGINT)?。
若找不到匹配的行，則函數(shù)返回?0?。
BIT_XOR(expr)
返回expr?中所有比特的bitwise XOR。計算執(zhí)行的精確度為64比特(BIGINT)?。
若找不到匹配的行，則函數(shù)返回?0?。
COUNT(expr)
返回SELECT語句檢索到的行中非NULL值的數(shù)目。
若找不到匹配的行，則COUNT()?返回?0?。
mysql>?SELECT?student.student_name,COUNT(*)->?FROM?student,course->?WHERE?student.student_id=course.student_id->?GROUP?BY?student_name;
COUNT(*)?的稍微不同之處在于，它返回檢索行的數(shù)目， 不論其是否包含?NULL值。
SELECT?從一個表中檢索，而不檢索其它的列，并且沒有?WHERE子句時，?COUNT(*)被優(yōu)化到最快的返回速度。例如：
mysql>?SELECT?COUNT(*)?FROM?student;
這個優(yōu)化僅適用于?MyISAM表,?原因是這些表類型會儲存一個函數(shù)返回記錄的精確數(shù)量，而且非常容易訪問。對于事務(wù)型的存儲引擎(InnoDB, BDB),?存儲一個精確行數(shù)的問題比較多，原因是可能會發(fā)生多重事物處理,?而每個都可能會對行數(shù)產(chǎn)生影響。
COUNT(DISTINCT?expr,[expr…])
返回不同的非NULL值數(shù)目。
若找不到匹配的項，則COUNT(DISTINCT)返回?0?。
mysql>?SELECT?COUNT(DISTINCT?results)?FROM?student;
在MySQL中,?你通過給定一個表達(dá)式列表而獲取不包含NULL?不同表達(dá)式組合的數(shù)目。在標(biāo)準(zhǔn)?SQL中,你將必須在COUNT(DISTINCT …)中連接所有表達(dá)式。
GROUP_CONCAT(expr)
該函數(shù)返回帶有來自一個組的連接的非NULL值的字符串結(jié)果。其完整的語法如下所示：
GROUP_CONCAT([DISTINCT]?expr?[,expr?...] [ORDER?BY?{unsigned_integer?|?col_name?|?expr} [ASC?|?DESC]?[,col_name?...]] [SEPARATOR?str_val]) mysql>?SELECT?student_name,->?GROUP_CONCAT(test_score)->?FROM?student->?GROUP?BY?student_name; Or: mysql>?SELECT?student_name,->?GROUP_CONCAT(DISTINCT?test_score->?ORDER?BY?test_score?DESC?SEPARATOR?'?')->?FROM?student->?GROUP?BY?student_name;
在MySQL中,你可以獲取表達(dá)式組合的連接值。你可以使用DISTINCT刪去重復(fù)值。假若你希望多結(jié)果值進(jìn)行排序，則應(yīng)該使用? ORDER BY子句。若要按相反順序排列，將?DESC (遞減)?關(guān)鍵詞添加到你要用ORDER BY?子句進(jìn)行排序的列名稱中。默認(rèn)順序為升序；可使用ASC將其明確指定。?? SEPARATOR?后面跟隨應(yīng)該被插入結(jié)果的值中間的字符串值。默認(rèn)為逗號?(‘,’)。通過指定SEPARATOR ”?，你可以刪除所有分隔符。
使用group_concat_max_len系統(tǒng)變量，你可以設(shè)置允許的最大長度。??程序中進(jìn)行這項操作的語法如下，其中?val?是一個無符號整數(shù)：
SET?[SESSION?|?GLOBAL]?group_concat_max_len?=?val;
若已經(jīng)設(shè)置了最大長度， 則結(jié)果被截至這個最大長度。
MIN([DISTINCT]?expr), MAX([DISTINCT]?expr)
返回expr?的最小值和最大值。?MIN()?和?MAX()?的取值可以是一個字符串參數(shù)；在這些情況下， 它們返回最小或最大字符串值。DISTINCT關(guān)鍵詞可以被用來查找expr?的不同值的最小或最大值，然而，這產(chǎn)生的結(jié)果與省略DISTINCT?的結(jié)果相同。
若找不到匹配的行，MIN()和MAX()返回?NULL?。
mysql>?SELECT?student_name,?MIN(test_score),?MAX(test_score)->?FROM?student->?GROUP?BY?student_name;
對于MIN()、?MAX()和其它集合函數(shù)，?MySQL當(dāng)前按照它們的字符串值而非字符串在集合中的相關(guān)位置比較?ENUM和SET?列。這同ORDER BY比較二者的方式有所不同。這一點應(yīng)該在MySQL的未來版本中得到改善。
STD(expr) STDDEV(expr)
返回expr?的總體標(biāo)準(zhǔn)偏差。這是標(biāo)準(zhǔn)?SQL?的延伸。這個函數(shù)的STDDEV()?形式用來提供和Oracle?的兼容性。可使用標(biāo)準(zhǔn)SQL函數(shù)?STDDEV_POP()?進(jìn)行代替。
若找不到匹配的行，則這些函數(shù)返回?NULL?。
STDDEV_POP(expr)
返回expr?的總體標(biāo)準(zhǔn)偏差(VAR_POP()的平方根)。你也可以使用? STD()?或STDDEV(),?它們具有相同的意義，然而不是標(biāo)準(zhǔn)的?SQL。
若找不到匹配的行，則STDDEV_POP()返回?NULL。
STDDEV_SAMP(expr)
返回expr?的樣本標(biāo)準(zhǔn)差?( VAR_SAMP()的平方根)。
若找不到匹配的行，則STDDEV_SAMP()?返回?NULL?。
SUM([DISTINCT]?expr)
返回expr?的總數(shù)。 若返回集合中無任何行，則?SUM()?返回NULL。DISTINCT?關(guān)鍵詞可用于?MySQL 5.1?中，求得expr不同值的總和。
若找不到匹配的行，則SUM()返回?NULL。
VAR_POP(expr)
返回expr?總體標(biāo)準(zhǔn)方差。它將行視為總體，而不是一個樣本， 所以它將行數(shù)作為分母。你也可以使用?VARIANCE(),它具有相同的意義然而不是 標(biāo)準(zhǔn)的?SQL。
若找不到匹配的項，則VAR_POP()返回NULL。
VAR_SAMP(expr)
返回expr?的樣本方差。更確切的說，分母的數(shù)字是行數(shù)減去1。
若找不到匹配的行，則VAR_SAMP()返回NULL。
VARIANCE(expr)
返回expr?的總體標(biāo)準(zhǔn)方差。這是標(biāo)準(zhǔn)SQL?的延伸。可使用標(biāo)準(zhǔn)SQL?函數(shù)?VAR_POP()?進(jìn)行代替。
若找不到匹配的項，則VARIANCE()返回NULL。
2.?GROUP BY修改程序
GROUP BY子句允許一個將額外行添加到簡略輸出端?WITH ROLLUP?修飾符。這些行代表高層(或高聚集)簡略操作。ROLLUP?因而允許你在多層分析的角度回答有關(guān)問詢的問題。例如，它可以用來向OLAP (聯(lián)機分析處理)?操作提供支持。
設(shè)想一個名為sales?的表具有年份、國家、產(chǎn)品及記錄銷售利潤的利潤列：
CREATE?TABLE?sales ( ????year????INT?NOT?NULL, ????country?VARCHAR(20)?NOT?NULL, ????product?VARCHAR(32)?NOT?NULL, ????profit??INT );
可以使用這樣的簡單GROUP BY，每年對表的內(nèi)容做一次總結(jié)：
mysql>?SELECT?year,?SUM(profit)?FROM?sales?GROUP?BY?year;+------+-------------+ |?year?|?SUM(profit)?| +------+-------------+ |?2000?|????????4525?| |?2001?|????????3010?| +------+-------------+
這個輸出結(jié)果顯示了每年的總利潤， 但如果你也想確定所有年份的總利潤，你必須自己累加每年的單個值或運行一個加法詢問。
或者你可以使用?ROLLUP,?它能用一個問詢提供雙層分析。將一個?WITH ROLLUP修飾符添加到GROUP BY?語句，使詢問產(chǎn)生另一行結(jié)果，該行顯示了所有年份的總價值：
mysql>?SELECT?year,?SUM(profit)?FROM?sales?GROUP?BY?year?WITH?ROLLUP;+------+-------------+ |?year?|?SUM(profit)?| +------+-------------+ |?2000?|????????4525?| |?2001?|????????3010?| |?NULL?|????????7535?| +------+-------------+
總計高聚集行被年份列中的NULL值標(biāo)出。
當(dāng)有多重?GROUP BY?列時，ROLLUP產(chǎn)生的效果更加復(fù)雜。這時，每次在除了最后一個分類列之外的任何列出現(xiàn)一個 “break”?(值的改變)?，則問訊會產(chǎn)生一個高聚集累計行。
例如，在沒有?ROLLUP的情況下，一個以年、國家和產(chǎn)品為基礎(chǔ)的關(guān)于?sales?表的一覽表可能如下所示：
mysql>?SELECT?year,?country,?product,?SUM(profit)->?FROM?sales->?GROUP?BY?year,?country,?product;+------+---------+------------+-------------+ |?year?|?country?|?product????|?SUM(profit)?| +------+---------+------------+-------------+ |?2000?|?Finland?|?Computer???|????????1500?| |?2000?|?Finland?|?Phone??????|?????????100?| |?2000?|?India???|?Calculator?|?????????150?| |?2000?|?India???|?Computer???|????????1200?| |?2000?|?USA?????|?Calculator?|??????????75?| |?2000?|?USA?????|?Computer???|????????1500?| |?2001?|?Finland?|?Phone??????|??????????10?| |?2001?|?USA?????|?Calculator?|??????????50?| |?2001?|?USA?????|?Computer???|????????2700?| |?2001?|?USA?????|?TV?????????|?????????250?| +------+---------+------------+-------------+
表示總值的輸出結(jié)果僅位于年/國家/產(chǎn)品的分析級別。當(dāng)添加了?ROLLUP后， 問詢會產(chǎn)生一些額外的行：
mysql>?SELECT?year,?country,?product,?SUM(profit) ????->?FROM?sales ????->?GROUP?BY?year,?country,?product?WITH?ROLLUP;+------+---------+------------+-------------+ |?year?|?country?|?product????|?SUM(profit)?| +------+---------+------------+-------------+ |?2000?|?Finland?|?Computer???|????????1500?| |?2000?|?Finland?|?Phone??????|?????????100?| |?2000?|?Finland?|?NULL???????|????????1600?| |?2000?|?India???|?Calculator?|?????????150?| |?2000?|?India???|?Computer???|????????1200?| |?2000?|?India???|?NULL???????|????????1350?| |?2000?|?USA?????|?Calculator?|??????????75?| |?2000?|?USA?????|?Computer???|????????1500?| |?2000?|?USA?????|?NULL???????|????????1575?| |?2000?|?NULL????|?NULL???????|????????4525?| |?2001?|?Finland?|?Phone??????|??????????10?| |?2001?|?Finland?|?NULL???????|??????????10?| |?2001?|?USA?????|?Calculator?|??????????50?| |?2001?|?USA?????|?Computer???|????????2700?| |?2001?|?USA?????|?TV?????????|?????????250?| |?2001?|?USA?????|?NULL???????|????????3000?| |?2001?|?NULL????|?NULL???????|????????3010?| |?NULL?|?NULL????|?NULL???????|????????7535?| +------+---------+------------+-------------+
對于這個問詢， 添加ROLLUP?子句使村輸出結(jié)果包含了四層分析的簡略信息，而不只是一個下面是怎樣解釋? ROLLUP輸出：
一組給定的年份和國家的每組產(chǎn)品行后面,?會產(chǎn)生一個額外的總計行， 顯示所有產(chǎn)品的總值。這些行將產(chǎn)品列設(shè)置為?NULL。
一組給定年份的行后面，會產(chǎn)生一個額外的總計行，顯示所有國家和產(chǎn)品的總值。這些行將國家和產(chǎn)品列設(shè)置為? NULL。
最后,?在所有其它行后面，會產(chǎn)生一個額外的總計列，顯示所有年份、國家及產(chǎn)品的總值。 這一行將年份、國家和產(chǎn)品列設(shè)置為?NULL。
使用ROLLUP?時的其它注意事項
以下各項列出了一些MySQL執(zhí)行ROLLUP的特殊狀態(tài)：
當(dāng)你使用?ROLLUP時,?你不能同時使用?ORDER BY子句進(jìn)行結(jié)果排序。換言之，?ROLLUP?和ORDER BY?是互相排斥的。然而，你仍可以對排序進(jìn)行一些控制。在?MySQL中，?GROUP BY?可以對結(jié)果進(jìn)行排序，而且你可以在GROUP BY列表指定的列中使用明確的?ASC和DESC關(guān)鍵詞，從而對個別列進(jìn)行排序。?(不論如何排序被ROLLUP添加的較高級別的總計行仍出現(xiàn)在它們被計算出的行后面）。
LIMIT可用來限制返回客戶端的行數(shù)。LIMIT?用在?ROLLUP后面,?因此這個限制 會取消被ROLLUP添加的行。例如:
mysql>?SELECT?year,?country,?product,?SUM(profit) ????->?FROM?sales ????->?GROUP?BY?year,?country,?product?WITH?ROLLUP ????->?LIMIT?5;+------+---------+------------+-------------+ |?year?|?country?|?product????|?SUM(profit)?| +------+---------+------------+-------------+ |?2000?|?Finland?|?Computer???|????????1500?| |?2000?|?Finland?|?Phone??????|?????????100?| |?2000?|?Finland?|?NULL???????|????????1600?| |?2000?|?India???|?Calculator?|?????????150?| |?2000?|?India???|?Computer???|????????1200?| +------+---------+------------+-------------+
將ROLLUP同?LIMIT一起使用可能會產(chǎn)生更加難以解釋的結(jié)果，原因是對于理解高聚集行，你所掌握的上下文較少。
在每個高聚集行中的NULL?指示符會在該行被送至客戶端時產(chǎn)生。服務(wù)器會查看最左邊的改變值后面的GROUP BY子句指定的列。對于任何結(jié)果集合中的，有一個詞匹配這些名字的列,?其值被設(shè)為?NULL。(若你使用列數(shù)字指定了分組列，則服務(wù)器會通過數(shù)字確定將哪個列設(shè)置為?NULL）。
由于在高聚集行中的?NULL值在問詢處理階段被放入結(jié)果集合中，你無法將它們在問詢本身中作為NULL值檢驗。例如，你無法將?HAVING product IS NULL?添加到問詢中，從而在輸出結(jié)果中刪去除了高聚集行以外的部分。
另一方面, NULL值在客戶端不以?NULL?的形式出現(xiàn)， 因而可以使用任何MySQL客戶端編程接口進(jìn)行檢驗。
3.?具有隱含字段的GROUP BY
MySQL?擴展了?GROUP BY的用途，因此你可以使用SELECT?列表中不出現(xiàn)在GROUP BY語句中的列或運算。這代表 “對該組的任何可能值 ”。你可以通過避免排序和對不必要項分組的辦法得到它更好的性能。例如，在下列問詢中，你無須對customer.name?進(jìn)行分組：
mysql>?SELECT?order.custid,?customer.name,?MAX(payments) ????->?FROM?order,customer ????->?WHERE?order.custid?=?customer.custid ????->?GROUP?BY?order.custid;
在標(biāo)準(zhǔn)SQL中,?你必須將?customer.name添加到?GROUP BY子句中。在MySQL中,?假如你不在ANSI模式中運行，則這個名字就是多余的。
假如你從?GROUP BY?部分省略的列在該組中不是唯一的，那么不要使用這個功能!?你會得到非預(yù)測性結(jié)果。
在有些情況下,你可以使用MIN()和MAX()?獲取一個特殊的列值，即使他不是唯一的。下面給出了來自包含排序列中最小值的列中的值：
SUBSTR(MIN(CONCAT(RPAD(sort,6,’ ‘),column)),7)
注意，假如你正在嘗試遵循標(biāo)準(zhǔn)?SQL,?你不能使用GROUP BY或?ORDER BY子句中的表達(dá)式。你可以通過使用表達(dá)式的別名繞過這一限制：
mysql>?SELECT?id,FLOOR(value/100)?AS?val ????->?FROM?tbl_name ????->?GROUP?BY?id,?val?ORDER?BY?val;
然而, MySQL允許你使用GROUP BY?及?ORDER BY?子句中的表達(dá)式。例如：
mysql>?SELECT id, FLOOR(value/100) FROM?tbl_name?ORDER BY RAND();
