GROUP BY 使用方法详解

大家好，欢迎来到IT知识分享网。

概述

group by是开发中经常用到的SQL语句，从字面意思来看就是根据哪个字段或者哪几个字段对查询到的数据进行分组统计，既然是分组统计那如何分组呢？所以group by通常都是和聚合函数还有having一起使用。

一、语法

二、举个例子

CREATE TABLE Sales (commodity VARCHAR(50), region VARCHAR(50), salesVolume INT); INSERT INTO Sales VALUES (N'电视机', N'河北省', 10000); INSERT INTO Sales VALUES (N'电视机', N'山东省', 20000); INSERT INTO Sales VALUES (N'电视机', N'山东省', 30000); INSERT INTO Sales VALUES (N'冰箱', N'浙江省', 15000); SELECT * FROM Sales 

SELECT region,SUM(salesVolume) 销量 from Sales GROUP BY region 

SELECT region,commodity ,SUM(salesVolume) 销量 from Sales GROUP BY region,commodity 

SELECT region,commodity ,SUM(salesVolume) 销量 from Sales GROUP BY region 

三、where和having的使用

1、where+group by

where 限定的条件查询会在group by 分组之前执行。

SELECT region,SUM(salesVolume) 销量 from Sales WHERE commodity='电视机' GROUP BY region 

2、group by+having

having限定的条件查询会在group by分组之后查询。

SELECT region,SUM(salesVolume) 销量 from Sales GROUP BY region HAVING SUM(salesVolume)>10000 

3、where+group by+having

where和having在一起使用的时候，会先执行where的限定条件查询，最后再执行having的限定条件查询。

SELECT region,SUM(salesVolume) 销量 from Sales WHERE commodity='电视机' GROUP BY region HAVING SUM(salesVolume)>10000 

四、聚合函数

常用的聚合函数有：count() 计数, sum() 求和 , avg() 求平均值, max() 求最大值, min()求最小值。

实例如下：

1、count( )

SELECT region,COUNT(*) 记录数 FROM Sales GROUP BY region 

2、avg( )

SELECT region,AVG(salesVolume) 平均销量 FROM Sales GROUP BY region 

3、max( )

SELECT region,MAX(salesVolume) 销售最多 FROM Sales GROUP BY region 

4、min( )

SELECT region,MIN(salesVolume) 销售最少 FROM Sales GROUP BY region 

五、group by的其他可用参数。

1、group by rollup( )

SELECT region,SUM(salesVolume)销量 FROM Sales GROUP BY ROLLUP (region ) 

2、group by cube( )

SELECT region,commodity,SUM(salesVolume)销量 FROM Sales GROUP BY CUBE (region,commodity ) 

3、group by grouping sets ( )

SELECT region,commodity,SUM(salesVolume) 销量 FROM Sales GROUP BY GROUPING SETS ( ROLLUP (region,commodity), CUBE (region,commodity) ) 

返回结果与两个 group by语句的联合查询相同。

SELECT region,commodity,SUM(salesVolume) 销量 FROM Sales GROUP BY ROLLUP (region,commodity) UNION ALL SELECT region,commodity,SUM(salesVolume) 销量 FROM Sales GROUP BY CUBE (region,commodity)