group by功能
在SQL中group by主要用來進行分組統計,分組字段放在group by的後面;分組結果一般需要借助聚合函數實作。
group by文法結構
1、常用文法
文法結構
SELECT
column_name1,column_name2, ......
聚合函數1,聚合函數2 , ......
FROM table_name
GROUP BY column_name1,column_name2, ......
說明:1、group by中的分組字段和select後的字段要保持一緻;2、通常group by和聚合函數一起使用,但也可以不包含聚合函數,根據業務需求決定;3、常用的聚合函數有max()、min()、avg()、sum()、count()等。
文法舉例
首先建立銷售表:
CREATE TABLE sale_order_item
(
ID int primary key NOT NULL,
area_name varchar(10),
mall_name varchar(10),
product_count int
);
然後向表中插入資料:
insert into sale_order_item values(1,'銷售一區','城東店',20);
insert into sale_order_item values(2,'銷售二區','城西店',15);
insert into sale_order_item values(3,'銷售一區','城南店',10);
insert into sale_order_item values(4,'銷售一區','城東店',4);
insert into sale_order_item values(5,'銷售二區','城西店',6);
insert into sale_order_item values(6,'銷售一區','城南店',11);
insert into sale_order_item values(7,'銷售一區','城東店',15);
insert into sale_order_item values(8,'銷售二區','城西店',16);
insert into sale_order_item values(9,'銷售一區','城南店',18);
insert into sale_order_item values(10,'銷售一區','城東店',22);
insert into sale_order_item values(11,'銷售二區','城西店',21);
insert into sale_order_item values(12,'銷售一區','城南店',25);
按照門店名稱進行分組,查詢各門店的銷售量之和:
select
mall_name 門店名稱,
sum(product_count) 銷售量
from sale_order_item
group by mall_name;
查詢結果如下所示:
2、having過濾
having需要和group by一起用,放在group by語句的後面;同時,having中的判斷對象,必須為group by子句中的分組字段或者聚合函數。
文法結構(假設隻有兩個分組字段)
SELECT
column_name1,column_name2,
聚合函數1,聚合函數2 , ......
FROM table_name
GROUP BY column_name1,column_name2 having 過濾條件
文法舉例(過濾對象為GROUP BY 子句中的分組字段)
select
mall_name 門店名稱,
sum(product_count) 銷售量
from sale_order_item
group by mall_name having mall_name not like '城西店';
查詢結果:
通常上面的SQL語句寫成下面這樣:先進行條件過濾,再進行分組計算
select
mall_name 門店名稱,
sum(product_count) 銷售量
from sale_order_item
where mall_name not like '城西店'
group by mall_name ;
文法舉例(過濾對象為聚合函數)
select
mall_name 門店名稱,
sum(product_count) 銷售量
from sale_order_item
group by mall_name having sum(product_count) < 60;
查詢結果:
3、rollup的使用
我們在使用group by功能時,發現隻能對分組字段的每個子項進行統計計算,而無法對整體進行彙總計算。通過使用rollup,就可以實作對分組字段進行小計或總計。
文法結構(假設隻有兩個分組字段)
SELECT
column_name1,column_name2,
聚合函數1,聚合函數2 , ......
FROM table_name
GROUP BY column_name1,column_name2 with rollup
文法舉例
SELECT
area_name 區域名稱,
mall_name 門店名稱,
sum(product_count) 銷售量
FROM sale_order_item
GROUP BY area_name,mall_name with rollup;
查詢結果:
需要注意的是,group by後面分組字段的順序不同時,得到的小計或總計的結果也不一樣。
易錯點和難點補充
1、分組字段和查詢字段不一緻,産生錯誤結果
正确寫法:
SELECT
(case when mall_name in ('城東店','城南店') then '銷售一區' when mall_name = '城西店' then '銷售二區' else null end) 銷售大區,
sum(product_count) 銷量
FROM sale_order_item
GROUP BY (case when mall_name in ('城東店','城南店') then '銷售一區' when mall_name = '城西店' then '銷售二區' else null end);
查詢結果:
錯誤寫法:分組字段和查詢字段不完全一緻,導緻銷售大區的字段值錯誤
SELECT
mall_name 銷售大區,
sum(product_count) 銷量
FROM sale_order_item
GROUP BY (case when mall_name in ('城東店','城南店') then '銷售一區' when mall_name = '城西店' then '銷售二區' else null end);
錯誤的查詢結果:
這裡的錯誤示例是在MySQL資料庫下運作的;如果使用的是PgSQL資料庫的話,會直接報文法錯誤,因為PgSQL資料庫要求查詢字段和分組字段必須完全一緻。
本人強烈建議,我們在使用group by時,一定要確定查詢字段和分組字段的完全一緻,否則可能會産生非預期結果。
2、字元串連接配接
這裡是在MySQL資料庫下實作的,其它資料庫實作方式可能會不一樣。
我們在使用group by時,通常情況下是進行數值的統計,但有時需要對字元串進行處理,将多行字元串拼接成單行字元串。
這裡使用的聚合函數是group_concat,其使用方法如下所示:
GROUP_CONCAT([DISTINCT] column1 [ORDER BY column2 ASC\DESC] [SEPARATOR seq]).
文法結構(假設隻有兩個分組字段)
SELECT
column_name1,column_name2,
group_concat()
FROM table_name
GROUP BY column_name1,column_name2
文法舉例
SELECT
area_name 銷售大區,
group_concat(distinct mall_name separator ';') 有銷售門店
FROM sale_order_item
GROUP BY area_name;
查詢結果:
#SQL##mysql面試#