您現(xiàn)在的位置： 365建站網(wǎng) > 365文章 > MYSQL中Group By的原理和用法

MYSQL中Group By的原理和用法

文章來源：365jz.com 點(diǎn)擊數(shù)：1093 更新時(shí)間：2017-12-28 11:26 參與評論

合計(jì)函數(shù) (比如 SUM) 常常需要添加 GROUP BY 語句。
GROUP BY 語句
GROUP BY 語句用于結(jié)合合計(jì)函數(shù)，根據(jù)一個(gè)或多個(gè)列對結(jié)果集進(jìn)行分組。
SQL GROUP BY 語法

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name

1、概述

“Group By”從字面意義上理解就是根據(jù)“By”指定的規(guī)則對數(shù)據(jù)進(jìn)行分組，所謂的分組就是將一個(gè)“數(shù)據(jù)集”劃分成若干個(gè)“小區(qū)域”，然后針對若干個(gè)“小區(qū)域”進(jìn)行數(shù)據(jù)處理。

2、原始表

3、簡單Group By

示例1

select 類別, sum(數(shù)量) as 數(shù)量之和 from A group by 類別

返回結(jié)果如下表，實(shí)際上就是分類匯總。

4、Group By 和 Order By

示例2

select 類別, sum(數(shù)量) AS 數(shù)量之和 from A group by 類別 order by sum(數(shù)量) desc

返回結(jié)果如下表

在Access中不可以使用“order by 數(shù)量之和 desc”，但在SQL Server中則可以。

5、Group By中Select指定的字段限制

示例3

select 類別, sum(數(shù)量) as 數(shù)量之和, 摘要 from A group by 類別 order by 類別 desc

示例3執(zhí)行后會提示下錯(cuò)誤，如下圖。這就是需要注意的一點(diǎn)，在select指定的字段要么就要包含在Group By語句的后面，作為分組的依據(jù)；要么就要被包含在聚合函數(shù)中。

6、Group By All

示例4

select 類別, 摘要, sum(數(shù)量) as 數(shù)量之和 from A group by all 類別, 摘要

示例4中則可以指定“摘要”字段，其原因在于“多列分組”中包含了“摘要字段”，其執(zhí)行結(jié)果如下表

“多列分組”實(shí)際上就是就是按照多列（類別+摘要）合并后的值進(jìn)行分組，示例4中可以看到“a, a2001, 13”為“a, a2001, 11”和“a, a2001, 2”兩條記錄的合并。

SQL Server中雖然支持“group by all”，但Microsoft SQL Server 的未來版本中將刪除 GROUP BY ALL，避免在新的開發(fā)工作中使用 GROUP BY ALL。Access中是不支持“Group By All”的，但Access中同樣支持多列分組，上述SQL Server中的SQL在Access可以寫成

select 類別, 摘要, sum(數(shù)量) AS 數(shù)量之和 from A group by 類別, 摘要

7、Group By與聚合函數(shù)

在示例3中提到group by語句中select指定的字段必須是“分組依據(jù)字段”，其他字段若想出現(xiàn)在select中則必須包含在聚合函數(shù)中，常見的聚合函數(shù)如下表：

函數(shù)

作用

支持性

sum(列名)

求和　　

max(列名)

最大值　　　　

min(列名)

最小值　　　

avg(列名)

平均值　　　

first(列名)

第一條記錄

僅Access支持

last(列名)

最后一條記錄

僅Access支持

count(列名)

統(tǒng)計(jì)記錄數(shù)

注意和count(*)的區(qū)別

示例5：求各組平均值

select 類別, avg(數(shù)量) AS 平均值 from A group by 類別;

示例6：求各組記錄數(shù)目

select 類別, count(*) AS 記錄數(shù) from A group by 類別;

示例7：求各組記錄數(shù)目

8、Having與Where的區(qū)別

•where 子句的作用是在對查詢結(jié)果進(jìn)行分組前，將不符合where條件的行去掉，即在分組之前過濾數(shù)據(jù)，where條件中不能包含聚組函數(shù)，使用where條件過濾出特定的行。
•having 子句的作用是篩選滿足條件的組，即在分組之后過濾數(shù)據(jù)，條件中經(jīng)常包含聚組函數(shù)，使用having 條件過濾出特定的組，也可以使用多個(gè)分組標(biāo)準(zhǔn)進(jìn)行分組。

示例8

select 類別, sum(數(shù)量) as 數(shù)量之和 from A
group by 類別
having sum(數(shù)量) > 18

示例9：Having和Where的聯(lián)合使用方法

select 類別, SUM(數(shù)量)from A
where 數(shù)量 gt;8
group by 類別
having SUM(數(shù)量) gt; 10

9、Compute 和 Compute By

select * from A where 數(shù)量 > 8

執(zhí)行結(jié)果：

示例10：Compute

select *from A where 數(shù)量>8 ompute max(數(shù)量),min(數(shù)量),avg(數(shù)量)

執(zhí)行結(jié)果如下：

compute子句能夠觀察“查詢結(jié)果”的數(shù)據(jù)細(xì)節(jié)或統(tǒng)計(jì)各列數(shù)據(jù)（如例10中max、min和avg），返回結(jié)果由select列表和compute統(tǒng)計(jì)結(jié)果組成。

示例11：Compute By

select *from A where 數(shù)量>8 order by 類別 compute max(數(shù)量),min(數(shù)量),avg(數(shù)量) by 類別

執(zhí)行結(jié)果如下：

示例11與示例10相比多了“order by 類別”和“... by 類別”，示例10的執(zhí)行結(jié)果實(shí)際是按照分組（a、b、c）進(jìn)行了顯示，每組都是由改組數(shù)據(jù)列表和改組數(shù)統(tǒng)計(jì)結(jié)果組成，另外：

•compute子句必須與order by子句用一起使用
•compute...by與group by相比，group by 只能得到各組數(shù)據(jù)的統(tǒng)計(jì)結(jié)果，而不能看到各組數(shù)據(jù)

在實(shí)際開發(fā)中compute與compute by的作用并不是很大，SQL Server支持compute和compute by，而Access并不支持

想實(shí)現(xiàn)這樣一種效果

如果使用group by一個(gè)條件的話,得到的結(jié)果會少了很多,如何多個(gè)條件組合篩選呢

 
group by fielda,fieldb,fieldc...

循環(huán)的時(shí)候可以通過判斷后一個(gè)跟前面一個(gè)是否相同來分組，一個(gè)示例

 
$result = mysql_query("SELECT groups,name,goods FROM table GROUP BY groups,name ORDER BY name"); 
$arr = array(); 
$i = 0; 
while($row = mysql_fetch_array($result)){ 
$arr[] = $row; 
} 

$m = $i - 1; 
foreach($arr as $k=>$v){ 
if($v[$i]['groups'] <> $v[$m]['groups']){ 
echo '分割符號'; 
} 
echo $v['name'],$v['goods']; 
}

group by

(1) group by的含義:將查詢結(jié)果按照1個(gè)或多個(gè)字段進(jìn)行分組，字段值相同的為一組
(2) group by可用于單個(gè)字段分組，也可用于多個(gè)字段分組

select * from employee;
+------+------+--------+------+------+-------------+
| num  | d_id | name   | age  | sex  | homeaddr    |
+------+------+--------+------+------+-------------+
|    1 | 1001 | 張三   |   26 | 男   | beijinghdq  |
|    2 | 1002 | 李四   |   24 | 女   | beijingcpq  |
|    3 | 1003 | 王五   |   25 | 男   | changshaylq |
|    4 | 1004 | Aric   |   15 | 男   | England     |
+------+------+--------+------+------+-------------+

select * from employee group by d_id,sex;

select * from employee group by sex;
+------+------+--------+------+------+------------+
| num  | d_id | name   | age  | sex  | homeaddr   |
+------+------+--------+------+------+------------+
|    2 | 1002 | 李四   |   24 | 女   | beijingcpq |
|    1 | 1001 | 張三   |   26 | 男   | beijinghdq |
+------+------+--------+------+------+------------+
根據(jù)sex字段來分組，sex字段的全部值只有兩個(gè)('男'和'女')，所以分為了兩組
當(dāng)group by單獨(dú)使用時(shí)，只顯示出每組的第一條記錄
所以group by單獨(dú)使用時(shí)的實(shí)際意義不大

group by + group_concat()

(1) group_concat(字段名)可以作為一個(gè)輸出字段來使用，
(2) 表示分組之后，根據(jù)分組結(jié)果，使用group_concat()來放置每一組的某字段的值的集合

select sex from employee group by sex;
+------+
| sex  |
+------+
| 女   |
| 男   |
+------+

select sex,group_concat(name) from employee group by sex;
+------+--------------------+
| sex  | group_concat(name) |
+------+--------------------+
| 女   | 李四               |
| 男   | 張三,王五,Aric     |
+------+--------------------+

select sex,group_concat(d_id) from employee group by sex;
+------+--------------------+
| sex  | group_concat(d_id) |
+------+--------------------+
| 女   | 1002               |
| 男   | 1001,1003,1004     |
+------+--------------------+

group by + 集合函數(shù)

(1) 通過group_concat()的啟發(fā)，我們既然可以統(tǒng)計(jì)出每個(gè)分組的某字段的值的集合，那么我們也可以通過集合函數(shù)來對這個(gè)"值的集合"做一些操作

select sex,group_concat(age) from employee group by sex;
+------+-------------------+
| sex  | group_concat(age) |
+------+-------------------+
| 女   | 24                |
| 男   | 26,25,15          |
+------+-------------------+

分別統(tǒng)計(jì)性別為男/女的人年齡平均值
select sex,avg(age) from employee group by sex;
+------+----------+
| sex  | avg(age) |
+------+----------+
| 女   |  24.0000 |
| 男   |  22.0000 |
+------+----------+

分別統(tǒng)計(jì)性別為男/女的人的個(gè)數(shù)
select sex,count(sex) from employee group by sex;
+------+------------+
| sex  | count(sex) |
+------+------------+
| 女   |          1 |
| 男   |          3 |
+------+------------+

group by + having

(1) having 條件表達(dá)式：用來分組查詢后指定一些條件來輸出查詢結(jié)果
(2) having作用和where一樣，但having只能用于group by

select sex,count(sex) from employee group by sex having count(sex)>2;
+------+------------+
| sex  | count(sex) |
+------+------------+
| 男   |          3 |
+------+------------+

group by + with rollup

(1) with rollup的作用是：在最后新增一行，來記錄當(dāng)前列里所有記錄的總和

select sex,count(age) from employee group by sex with rollup;
+------+------------+
| sex  | count(age) |
+------+------------+
| 女   |          1 |
| 男   |          3 |
| NULL |          4 |
+------+------------+

select sex,group_concat(age) from employee group by sex with rollup;
+------+-------------------+
| sex  | group_concat(age) |
+------+-------------------+
| 女   | 24                |
| 男   | 26,25,15          |
| NULL | 24,26,25,15       |
+------+-------------------+