理解group by語義

個人認為sql中的group by和join是兩大難點，因為它們轉(zhuǎn)換了原來的表結(jié)構(gòu)，group把表按某些字段統(tǒng)計縮小，join則使用笛卡爾積將多個表連接展開。

咱們回到group by，顧名思義group即為分組，即將原來的一整塊數(shù)據(jù)分成幾小塊。分組是聚合的前提，聚合是在每個分組內(nèi)進行一些統(tǒng)計，如在分組內(nèi)的最大值，最小值，平均值，個數(shù)等。未分組時查詢返回的行直接與數(shù)據(jù)庫表中的行對應(yīng)，分組后分為多少組這個查詢就會返回多少條記錄，返回的記錄中只能包含分組字段和在這個分組上的聚合操作的值（MySQL是例外見后面）一般形式為：語法為：

select t.sex, count(t.id) as cnt group by sex having cnt>2

查詢中有三種字段：查詢字段：在select中出現(xiàn)的表中的字段聚合字段：對表中的字段施加了聚合函數(shù)后形成的字段分組字段：group by后的字段

無分組字段時

sql允許無分組字段即無group by直接在select用各聚合函數(shù)，這時表示整塊數(shù)據(jù)為一個分組（單分組），聚合操作則是在整塊全局上進行聚合，表示為本查詢中所有記錄的最大值，最小值，平均值，個數(shù)等如果沒有分組字段的聚合，則select后的字段只能全部都是帶聚合的，不能直接是字段名否則Orcle會報錯：ORA-00937: not a single-group group function（MySQL是個例外后面詳述）

MySQL的坑

MySQL有一個讓人不解的地方：在聚合查詢中select可以出現(xiàn)非分組字段，語義上group by后的結(jié)果已經(jīng)進行了分組變換，此時的數(shù)據(jù)均應(yīng)對應(yīng)于分組而不再是原表記錄，在一個分組語義的記錄里加入一個表的字段，語義上不太嚴謹，實際上MySQL會將非分組字段隨便取一個值，不得不說算一個坑比如下面的查詢Oracle報錯而MySQL不報錯

select id, count(t.id) from(

select 1 as id, 2 as age, 'F' as sex from dual union

select 2 as id, 2 as age, 'M' as sex from dual union

select 3 as id, 3 as age, 'F' as sex from dual union

select 4 as id, 4 as age, 'M' as sex from dual

)；

在MySQL中返回

1 4

該查詢是單分組查詢，4是總的記錄條數(shù)沒有問題，此時1是什么意思呢？它和計數(shù)有什么關(guān)系呢？

對null的處理

count(*)統(tǒng)計包含null在內(nèi)的行數(shù)，count后面跟上字段名則表示統(tǒng)計這個字段非null的行數(shù)聚合函數(shù)后面可以跟一個復(fù)合表達式，如多個字段相加，復(fù)合時如果其中一個字段為null則整個表達式為null，比如統(tǒng)計兩個字段都非null的行數(shù)，可以count(f1+f2)count,avg,sum后面跟上字段名時如果為null值將會忽略，比如計算平均值時null的那一行并不會記入總行數(shù)如果帶有g(shù)roup by則select后的字段要么是分組字段要么是聚合字段（MySQL仍然可以出現(xiàn)非分組字段）

關(guān)于having

與where不同的是having在group后進行進行過濾，where在group前進行過濾其寫在group關(guān)鍵字前，having后過濾的字段可以是分組字段或聚合字段如：select sex, count(*) as cnt having sex='F'select sex, count(*) as cnt having cnt>1

小技巧

有時需要測試sql語法但又覺得創(chuàng)建表麻煩，可以使用創(chuàng)建一個臨時表，如下：

select id, count(t.id) from(

select 1 as id, 2 as age, 'F' as sex from dual union

select 2 as id, 2 as age, 'M' as sex from dual union

select 3 as id, 3 as age, 'F' as sex from dual union

select 4 as id, 4 as age, 'M' as sex from dual

)；