原文地址：https://www.cnblogs.com/snsdzjlz320/p/5738226.html

• group by
• group by + group_concat()
• group by + 集合函數(shù)
• group by + having
• group by + with rollup

?

group by

(1) group by的含義:將查詢(xún)結(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字段來(lái)分組，sex字段的全部值只有兩個(gè)('男'和'女')，所以分為了兩組 當(dāng)group by單獨(dú)使用時(shí)，只顯示出每組的第一條記錄 所以group by單獨(dú)使用時(shí)的實(shí)際意義不大

?

group by + group_concat()

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

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) 通過(guò)group_concat()的啟發(fā)，我們既然可以統(tǒng)計(jì)出每個(gè)分組的某字段的值的集合，那么我們也可以通過(guò)集合函數(shù)來(lái)對(duì)這個(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á)式：用來(lái)分組查詢(xún)后指定一些條件來(lái)輸出查詢(xún)結(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的作用是：在最后新增一行，來(lái)記錄當(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 |
+–----±------------------+

?

?

總結(jié)

以上是生活随笔為你收集整理的mysql语句group by用法详解的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。