SQL数据库关键字之group by 小结

                    SQL数据库关键字之group by 小结

1、解释：Group By语句从英文的字面意义上理解就是“根据(by)一定的规则进行分组(Group)”。它的作用是根据给定数据列的每个成员对查询结果进行分组统计，最终得到一个分组汇总表

2、常见用法：

       创建客户信息库（customers）

create table customers( 

    customersid int identity(1,1) not null, 

    name nvarchar(50), 

    city nvarchar(50), 

    customerstype nvarchar(50), 

    addtime date, 

    adddepartment  nvarchar(10), 

    quantity int 

)

 插入数据语句

insert into customers(name,city,customerstype,addtime,adddepartment,quantity) values('张三','中国','普通客户','2010-10-23','财务部',3) 

insert into customers(name,city,customerstype,addtime,adddepartment,quantity) values('李四','法国','主要客户','2012-11-1','销售部',2) 

insert into customers(name,city,customerstype,addtime,adddepartment,quantity) values('王五','中国','普通客户','2011-1-12','编辑部',12) 

insert into customers(name,city,customerstype,addtime,adddepartment,quantity) values('王六','德国','特殊客户','2011-1-12','编辑部',5) 

insert into customers(name,city,customerstype,addtime,adddepartment,quantity) values('马七','中国','主要客户','2012-2-29','财务部',3) 

insert into customers(name,city,customerstype,addtime,adddepartment,quantity) values('赵八','德国','特殊客户','2010-4-23','财务部',6) 

insert into customers(name,city,customerstype,addtime,adddepartment,quantity) values('钱九','美国','特殊客户','2011-6-16','编辑部',2) 

insert into customers(name,city,customerstype,addtime,adddepartment,quantity) values('孙十','中国','主要客户','2012-12-2','销售部',7) 

insert into customers(name,city,customerstype,addtime,adddepartment,quantity) values('张四','法国','特殊客户','2010-11-2','编辑部',2) 

insert into customers(name,city,customerstype,addtime,adddepartment,quantity) values('张五','中国','普通客户','2012-11-23','销售部',1)

表查询结果如下

customersid    name       city  customerstype addtime adddepartment        quantity

1     张三       中国       普通客户       2010-10-23     财务部    3

2     李四       法国       主要客户       2012-11-01     销售部    2

3     王五       中国       普通客户       2011-01-12     编辑部    12

4     王六       德国       特殊客户       2011-01-12     编辑部    5

5     马七       中国       主要客户       2012-02-29     财务部    3

6     赵八       德国       特殊客户       2010-04-23     财务部    6

7     钱九       美国       特殊客户       2011-06-16     编辑部    2

8     孙十       中国       主要客户       2012-12-02     销售部    7

9     张四       法国       特殊客户       2010-11-02     编辑部    2

10    张五       中国       普通客户       2012-11-23     销售部    1

2.1 group by [ ]

       这个是 group by 最常见的用法 ，group by [分组的字段1,分组字段2….]

       例如：对customers 根据 customerstype 进行分组

语句： 

select customerstype  

from customers  

group by customerstype 

运行结果如下  

customerstype 

普通客户 

特殊客户 

主要客户

 解释：1、SELECT子句中的列名必须为分组列或列函数

　　　 2、列函数对于GROUP BY子句定义的每个组各返回一个结果”，根据customerstype分组，对每个类型返回一个结果

2.2 group by 常和 sum，max，min ，count 等聚合函数一起使用

例如：对 customers根据 customerstype 进行分组 统计每个类别中的客户个

语句： 

select customerstype,COUNT(*)  

from customers  

group by customerstype 

运行结果如下 

customerstype   number 

普通客户                  3 

特殊客户                  4 

主要客户                  3

 例如：对 customers根据 customerstype 进行分组 获取每组的最大customersid

语句： 

select customerstype,MAX(customersid) as number  

from customers  

group by customerstype 

运行结果如下 

customerstype   number 

普通客户                  10 

特殊客户                   9 

主要客户                   8

 2.3 group by 字句和where 字句一起使用

       在SQL中where字句的运行顺序是先于 group by 字句的，where 字句会会在形成组和计算列函数之前消除不符合条件的行

例如：查询由财务部门添加的用户各个类型的最大customersid

语句： 

select customerstype,MAX(customersid) as number  

from customers  

where adddepartment='财务部' 

group by customerstype 

运行结果如下 

customerstype   number 

普通客户                    1 

特殊客户                    6 

主要客户                    5

 解释：where 字句过滤掉了不是 财务部 添加的用户信息，再对之后的结果进行 group by 操作

2.4 group by 字句和having() 字句一起使用

       在SQL 中 having() 字句的运行顺序是后于 group by 字句的， having() 字句的的作用是筛选满足条件的组

例如：查询客户数超过1个的国家和客户数量

语句： 

select city, count(*) number  

from customers   

GROUP by  city  

having count(*)>1 

运行结果如下 

city    number 

德国    2 

法国    2 

中国    5

 解释：系统会先对customers 根据 city 分组，生产虚拟表，之后having 生产的虚拟表进行筛选，将数量不大于1的剔除

2.5 group by 字句和ROLLUP（）一起使用

 可方便的生成"合计"、"小计"、"总计" 等混合统计的报表

例如语句 

SELECT city,customerstype, sum(quantity) quantity 

FROM customers 

GROUP BY ROLLUP (city,customerstype) 

运行结果如下 

city    customerstype   quantity 

德国  特殊客户                  11 

德国  NULL                11 

法国  特殊客户                   2 

法国  主要客户                   2 

法国  NULL                 4 

美国  特殊客户    　　           2 

美国  NULL                 2 

中国  普通客户                  16 

中国  主要客户                  10 

中国  NULL                26 

NULL    NULL                43

 解释：①ROLLUP会为 (city,customerstype)、和 (city) 值的每个唯一组合生成一个带有小计的行。还将计算一个总计行

       ②列是按照从右到左的顺序汇总的。列的顺序会影响 ROLLUP 的输出分组，而且可能会影响结果集内的行数

2.6 group by 字句和CUBE（）一起使用

生成简单的 GROUP BY 聚合行、ROLLUP 超聚合行和交叉表格行。

CUBE 针对表达式的所有排列输出一个分组。

例如语句 

SELECT city,customerstype, SUM (quantity) quantity 

FROM customers 

GROUP BY CUBE (city,customerstype) 

运行结果如下 

city    customerstype   quantity 

中国  普通客户    16 

NULL    普通客户    16 

德国  特殊客户    11 

法国  特殊客户    2 

美国  特殊客户    2 

NULL    特殊客户    15 

法国  主要客户    2 

中国  主要客户    10 

NULL    主要客户    12 

NULL    NULL    43 

德国  NULL    11 

法国  NULL    4 

美国  NULL    2 

中国  NULL    26

 解释：①CUBE会为 (city,customerstype)、(city) 和 (customerstype) 值的每个唯一组合生成一个带有小计的行，还会生成一个总计行。

　   　②CUBE中列的顺序不影响 CUBE 的输出。

2.7 group by all

group by all 需和 where 一起使用，否则all 不起作用，查询结果中包含又 group by 字句产生的所有分组，即使这些组没有符合 where 字句的条件 ，这些没有符合条件的结果会以 null  显示

例如： 

select city,SUM(quantity) quantity  

from customers  

where quantity>5  

group by all city 

运行的结果为 

city    quantity 

德国  6 

法国  NULL

美国  NULL

中国  19 

解释：其中 法国和美国 quantity 的和值不符合 where 条件，分别为 4 和2，单仍然在查询的结果中显示 

以上语句，将all 去掉后，运行的结果如下 

德国  6 

中国  19