1 group by子句

group by子句**：分組統(tǒng)計(jì)，根據(jù)某個(gè)字段將所有的結(jié)果分類，并進(jìn)行數(shù)據(jù)統(tǒng)計(jì)分析

• 分組的目的不是為了顯示數(shù)據(jù)，一定是為了統(tǒng)計(jì)數(shù)據(jù)
• group by子句一定是出現(xiàn)在where子句之后（如果同時(shí)存在）
• 分組統(tǒng)計(jì)可以進(jìn)行統(tǒng)計(jì)細(xì)分：先分大組，然后大組分小組
• 分組統(tǒng)計(jì)需要使用統(tǒng)計(jì)函數(shù)
  • group_concat()：將組里的某個(gè)字段全部保留
  • any_value()：選擇被分到同一組的數(shù)據(jù)里第一條數(shù)據(jù)的指定列值作為返回?cái)?shù)據(jù)
  • count()：求對(duì)應(yīng)分組的記錄數(shù)量
    • count(字段名)：統(tǒng)計(jì)某個(gè)字段值的數(shù)量（NULL不統(tǒng)計(jì)）
    • count(*)：統(tǒng)計(jì)整個(gè)記錄的數(shù)量（較多）
  • sum()：求對(duì)應(yīng)分組中某個(gè)字段是和
  • max()/min()：求對(duì)應(yīng)分組中某個(gè)字段的最大/最小值
  • avg()：求對(duì)應(yīng)分組中某個(gè)字段的平均值

步驟
1、確定要進(jìn)行數(shù)據(jù)統(tǒng)計(jì)
2、確定統(tǒng)計(jì)對(duì)象：分組字段（可以多個(gè)）
3、確定要統(tǒng)計(jì)的數(shù)據(jù)形式：選擇對(duì)應(yīng)統(tǒng)計(jì)函數(shù)
4、分組統(tǒng)計(jì)

• 原始數(shù)據(jù)

create table t_40(
id int primary key auto_increment,
name varchar(10) not null,
gender enum('男','女','保密'),
age tinyint unsigned not null,
class_name varchar(10) not null comment '班級(jí)名稱'
)charset utf8;insert into t_40 values(null,'鳴人','男',18,'木葉1班'),
(null,'佐助','男',18,'木葉1班'),
(null,'佐井','男',19,'木葉2班'),
(null,'大蛇丸','男',28,'木葉0班'),
(null,'卡卡西','男',29,'木葉0班'),
(null,'小櫻','女',18,'木葉1班'),
(null,'雛田','女',18,'木葉1班'),
(null,'我愛羅','男',19,'木葉1班'),
(null,'向日葵','女',6,'木葉10班'),
(null,'博人','男',8,'木葉10班'),
(null,'鼬','男',28,'木葉0班');

• 統(tǒng)計(jì)每個(gè)班的人數(shù)

select count(*),class_name from t_40 group by class_name;

• 多分組：統(tǒng)計(jì)每個(gè)班的男女學(xué)生數(shù)量

select count(*),class_name,gender from t_40 group by class_name,gender;片

• 統(tǒng)計(jì)每個(gè)班里的人數(shù)，并記錄班級(jí)學(xué)生的名字

select count(*),group_concat(name),class_name from t_40 group by class_name;

select count(*),any_value(name),class_name from t_40 group by class_name;

小結(jié)

1、分組與統(tǒng)計(jì)是不分離的，分組必然要用到統(tǒng)計(jì)，而統(tǒng)計(jì)一旦使用實(shí)際上就進(jìn)行了分組

2、分組統(tǒng)計(jì)使用數(shù)據(jù)數(shù)據(jù)的查詢只能依賴統(tǒng)計(jì)函數(shù)和被分組字段，而不能是其他字段（MySQL7以前可以，不過數(shù)據(jù)沒意義：因?yàn)橄到y(tǒng)只保留組里的第一個(gè)）

3、group by子句有自己明確的位置：在where之后（where可以沒有）

2 回溯統(tǒng)計(jì)

概念

回溯統(tǒng)計(jì)：在進(jìn)行分組時(shí)（通常是多分組），每一次結(jié)果的回溯都進(jìn)行一次匯總統(tǒng)計(jì)

• 回溯統(tǒng)計(jì)語法：在統(tǒng)計(jì)之后使用 with rollup

步驟

1、確定要進(jìn)行分組統(tǒng)計(jì)

2、確定是多分組統(tǒng)計(jì)

3、需要對(duì)每次分組結(jié)果進(jìn)行匯總

4、使用回溯統(tǒng)計(jì)

示例

統(tǒng)計(jì)每個(gè)班的男女同學(xué)數(shù)量，同時(shí)要知道班級(jí)人數(shù)總數(shù)

# 只統(tǒng)計(jì)每個(gè)班的男女同學(xué)數(shù)量，沒有班級(jí)匯總
select count(*),class_name,gender,group_concat(name) from t_40 group by class_name,gender;

# 匯總統(tǒng)計(jì)：回溯
select count(*),class_name,gender,group_concat(name) from t_40 group by class_name,gender with rollup;

小結(jié)

1、回溯統(tǒng)計(jì)一般用在多字段分組中，用來統(tǒng)計(jì)各級(jí)分組的匯總數(shù)據(jù)

2、因?yàn)榛厮萁y(tǒng)計(jì)會(huì)將對(duì)應(yīng)的分組字段置空（不置空無法合并），所以回溯的數(shù)據(jù)還需要經(jīng)過其他程序語言加工處理才能取出數(shù)據(jù)來

3 having子句

概念

having子句：類似于where子句，是用來進(jìn)行條件篩選數(shù)據(jù)的

• having子句本身是針對(duì)分組統(tǒng)計(jì)結(jié)果進(jìn)行條件篩選的

• having子句必須出現(xiàn)在group by子句之后（如果同時(shí)存在）

• having針對(duì)的數(shù)據(jù)是在內(nèi)存里已經(jīng)加載的數(shù)據(jù)

• having幾乎能做where能做的所有事，但是where卻不一定

  • 字段別名（where針對(duì)磁盤數(shù)據(jù)，那時(shí)還沒有）
  • 統(tǒng)計(jì)結(jié)果（where在group by之前）
  • 分組統(tǒng)計(jì)函數(shù)（having通常是針對(duì)group by存在的）

步驟

1、前面有分組統(tǒng)計(jì)

2、需要針對(duì)分組統(tǒng)計(jì)后的結(jié)果進(jìn)行數(shù)據(jù)篩選

3、使用having組織條件進(jìn)行篩選

select count(*) as `count`,class_name,group_concat(name) from t_40 group by class_name having `count` < 3;

小結(jié)

1、having也是用于數(shù)據(jù)篩選的，但是本質(zhì)是針對(duì)分組統(tǒng)計(jì)，如果沒有分組統(tǒng)計(jì)，不要使用having進(jìn)行數(shù)據(jù)篩選

2、能用where解決問題的地方絕不使用having

• where針對(duì)磁盤讀取數(shù)據(jù)，源頭解決問題
• where能夠限制無效數(shù)據(jù)進(jìn)入內(nèi)存，內(nèi)存利用率較高，而having是針對(duì)內(nèi)存數(shù)據(jù)篩選