GROUP BY 使用方法详解

概述

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

一、语法

select 聚合函数(字段1)，字段2 from 表名 where 条件 group by 字段2,字段3
或者
select 聚合函数(字段1)，字段2 from 表名 where 条件 group by 字段2,字段3 having 过滤条件
使用了group by 后，要求select出的结果字段都是可汇总的，否则就会出错。
group by 有一个原则,就是 select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by 后面。

二、举个例子

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 

返回结果：

还有一些不经常使用的：count_big()和count()相同作用但返回值的数据类型不同，count()返回 ‘int’ ,count_big()返回 ‘bigint’ ,stdev()求标准偏差，stdevp()求总体标准偏差，var()求方差，varp()求总体统计方差等等这里就不尽数了。

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

1、group by rollup( )

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

返回结果如下：

这里比不加 rollup()的查询结果多了一行总计。

2、group by cube( )

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

返回结果如下：

添加cube()后不但增加了一行总计，还增加了每一组单项统计的合计。group by cube为所有可能的列组合创建组。 对于 group by cube (a, b)，结果具有 (a, b)、(NULL, b)、(a, NULL) 和 (NULL, NULL) 唯一值的组。

3、group by grouping sets ( )

grouping sets选项可将多个 group by子句组合到一个 group by 子句中。 其结果与针对指定的组执行 union all运算等效。
例如：

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)

返回结果如下：

SQL 不会合并为 grouping sets 列表生成的重复组。 上例中，GROUPING SETS ( ROLLUP (region,commodity), CUBE (region,commodity) )，两个元素都返回总计行并且这两行都会列在结果中。