1 group by子句

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

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

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

• 原始數(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 '班級名稱'
)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)計每個班的人數(shù)

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

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

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

• 統(tǒng)計每個班里的人數(shù)，并記錄班級學(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)計是不分離的，分組必然要用到統(tǒng)計，而統(tǒng)計一旦使用實際上就進行了分組

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

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

2 回溯統(tǒng)計

概念

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

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

步驟

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

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

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

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

示例

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

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

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

小結(jié)

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

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

3 having子句

概念

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

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

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

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

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

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

步驟

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

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

3、使用having組織條件進行篩選

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ì)是針對分組統(tǒng)計，如果沒有分組統(tǒng)計，不要使用having進行數(shù)據(jù)篩選

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

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