SQL语句中Group by语句的详细介绍

hner

SQL语句中Group by语句的详细介绍                       
                       
一、创建数据表（表名：Sales）                       
Create table Sales(ID numeric(28,0) identity,InvNo char(8),InvDate datetime default getdate(),                       
Customer varchar(20),Department varchar(5),Amount numeric(10,2))                       
二、语法介绍                       
SELECT [DISTINCT|ALL] {*|模式名.] {表名|视图名|                       
快照名] .*…| {表达式[列别名]…} } [, [模式名. ] {表名|                       
视图名|} .*…| 表达式[列别名] ]…                       
FROM [模式名.] {表名|视图名|快照名} [@数据库链名] [表别名]                       
[, [模式名.] {表名|视图名|快照名} [@数据库链名]                       
[表别名] ]…                       
[WHERE条件]                       
[START WITH条件 CONNECT BY 条件]                       
[GROUP BY表达式[，表达式] …[HAVING条件] [WITH CUBE|ROLLUP]                       
[UNION|UNION ALL |INTERSECT|MINUS]SELECT命令                       
[ORDER BY{表达式|位置} [ASC|DESC] [, {表达式|位置[ASC|DESC]}]…]                       
                       
Group by 表达式[，表达式]……[HAVING条件] [WITH CUBE|ROLLUP]表示分组                       
                       
1、查询Sales表中，统计各客户的销售额，并按销售额降序排列结果                       
select customer 客户名称,sum(amount) 销售额 from sales group by customer order by sum(amount) desc                       
结果如下：                       
        Customer        Amount       
        中海集运        1700000       
        中韩轮渡        1091201       
        国际海运        22402       
        氨纶股份        16801.5       
                       
Group by按组汇总，所谓组就是完全一致的数据统称为一组，本例主要是按客户名称分组，一组中一致的名称                       
进行sum求和。                       
2、查询Sales表中，统计各部门的销售额，并按销售额降序排列结果                       
select department 部门,sum(amount) 销售额 from sales group by department order by sum(amount) desc                       
结果如下：                       
        Department        Amount       
        C        1591201       
        A        811201       
        B        411201       
        D        16801.5       
3、查询Sales表中，按部门、客户分组统计销售额，并按客户排列结果                       
select department 部门,customer 客户 ,sum(amount) 销售额 from sales                        
   group by department,customer order by department                       
这里先按部门分组，然后按客户分组。对于部门相同客户相同的记录进行汇总                       
结果如下：                       
        部门        客户        销售额
        A        氨纶股份        5600.5
        A        国际海运        5600.5
        A        中海集运        800000
        B        氨纶股份        5600.5
        B        国际海运        5600.5
        B        中海集运        400000
        C        氨纶股份        5600.5
        C        国际海运        5600.5
        C        中韩轮渡        1080000
        C        中海集运        500000
        D        国际海运        5600.5
        D        中韩轮渡        11201
调换Group by分组位置，不影响查询结果，如：                       
select department 部门,customer 客户 ,sum(amount) 销售额 from sales                        
   group by customer,department order by department                       
                       
无论是先按照客户分组，还是先按照部门分组，分组后查询出的结果是一致的，这是因为                       
对于两个分组，只有部门、客户完全一致的方可汇总。                       
                       
4、查询Sales表中，统计各客户的销售额，并计算汇总销售额                       
select customer 客户名称,sum(amount) 销售额 from sales group by customer with cube order by sum(amount)                       
注意这里的with cube的用法。With cube除了返回由Group by子句指定的列外，还返回按组统计的行。返回的结果先按分组的第一                       
个条件列排序显示，再按第二个条件列排序显示。统计行包括了Group by子句指定的列的各种组合的数据统计。                       
此例中，只有一个分组条件，客户名称，由于有with cube则需要返回除了group by指定的列外，还要返回行。                       
按客户分组，除了每个客户外，还应统计所有客户的销售额，这就是with cube。                       
结果如下：        客户名称        销售额       
        氨纶股份        16801.5       
        国际海运        22402       
        中韩轮渡        1091201       
        中海集运        1700000       
                2830404.5       
再例如，求出各部门的销售额，并计算总销售额                       
select department as 部门,sum(amount) as 销售额 from sales group by department with cube                       
        部门        销售额       
        A        811201       
        B        411201       
        C        1591201       
        D        16801.5       
                2830404.5       
5、按部门、客户分组统计销售额，并计算各部门总的销售额、分客户销售额和全公司总的销售额                       
select department 部门,customer 客户 ,sum(amount) 销售额 from sales                        
   group by department,customer with cube                        
                       
结果如下：        部门        客户        销售额
1        A        氨纶股份        5600.5
2        A        国际海运        5600.5
3        A        中海集运        800000
4        A                811201
5        B        氨纶股份        5600.5
6        B        国际海运        5600.5
7        B        中海集运        400000
8        B                411201
9        C        氨纶股份        5600.5
10        C        国际海运        5600.5
11        C        中韩轮渡        1080000
12        C        中海集运        500000
13        C                1591201
14        D        国际海运        5600.5
15        D        中韩轮渡        11201
16        D                16801.5
17                        2830404.5
18                氨纶股份        16801.5
19                国际海运        22402
20                中海集运        1700000
21                中韩轮渡        1091201
本例包含了两个分组条件，第一个分组条件为部门，第二个分组条件为客户名称，这里使用了with cube，也就是说                       
该分组除了返回group by指定的列外，还返回按组统计的行，返回顺序先按照第一个字段显示，然后按照第二个字段                       
显示。                       
（1）第1-3行是按照group by子句后department部门、customer客户名称进行分组求和；                       
（2）第4行是按照with cube子句返回第一个字段组合的行。即A客户总的销售额；                       
（3）第5-16行原理同上；                       
（4）第18-21行按照WITH CUBE子句返回第二个字段组合的所有行。即各客户的销售额；                       
（5）第17行所有部门、所有客户总的销售额；                       
                       
修改GROUP BY子句中字段的顺序                       
select department 部门,customer 客户 ,sum(amount) 销售额 from sales                        
   group by customer,department with cube                        
                       
        部门        客户        销售额
        A        氨纶股份        5600.5
        B        氨纶股份        5600.5
        C        氨纶股份        5600.5
                        16801.5
        A        国际海运        5600.5
        B        国际海运        5600.5
        C        国际海运        5600.5
        D        国际海运        5600.5
                        22402
        A        中海集运        800000
        B        中海集运        400000
        C        中海集运        500000
                        1700000
        C        中韩轮渡        1080000
        D        中韩轮渡        11201
                        1091201
                        2830404.5
        A                811201
        B                411201
        C                1591201
        D                16801.5
                       
6、按部门、客户分组统计销售额，并计算各部门总的销售额以及全公司总的销售额                       
select department 部门,customer 客户 ,sum(amount) 销售额 from sales                        
   group by department,customer with ROLLUP                       
                       
结果如下：        部门        客户        销售额
1        A        氨纶股份        5600.5
2        A        国际海运        5600.5
3        A        中海集运        800000
4        A                811201
5        B        氨纶股份        5600.5
6        B        国际海运        5600.5
7        B        中海集运        400000
8        B                411201
9        C        氨纶股份        5600.5
10        C        国际海运        5600.5
11        C        中韩轮渡        1080000
12        C        中海集运        500000
13        C                1591201
14        D        国际海运        5600.5
15        D        中韩轮渡        11201
16        D                16801.5
17                        2830404.5
                       
交换group by子句中的字段，改变列的顺序会使返回结果的行数发生变化                       
                       
select customer 客户,department 部门 ,sum(amount) 销售额 from sales                        
   group by customer,department with ROLLUP                       
        客户        部门        销售额
        氨纶股份        A        5600.5
        氨纶股份        B        5600.5
        氨纶股份        C        5600.5
                        16801.5
        国际海运        A        5600.5
        国际海运        B        5600.5
        国际海运        C        5600.5
        国际海运        D        5600.5
                        22402
        中海集运        A        800000
        中海集运        B        400000
        中海集运        C        500000
                        1700000
        中韩轮渡        C        1080000
        中韩轮渡        D        11201
                        1091201
                        2830404.5
                       
总结：                       
Group by子句主要功能是分组，分组后可以使用聚合函数sum/avg等，所谓的组                       
实质就是相同的项目称为组，当Group by中有多个组字段时，按照字段的先后顺序进行                       
分组统计。当有with cube和with rullop子句时，系统除了按照group by子句进行分组                       
外，还返回按组统计的行，他们唯一的区别是with rullop统计行时，只按照group by                       
子句中第一个字段进行分组统计。                       
另外，Having子句主要是对分组后的数据进行条件过滤，通常发生在where子句之后。                       
如：                       
select customer 客户名称,sum(amount) 销售额 from sales group by customer                        
having sum(amount)>100000 order by sum(amount) desc                       
结果如下：                       
        客户名称        销售额       
        中海集运        1700000       
        中韩轮渡        1091201       
再如：                       
select customer 客户名称,sum(amount) 销售额 from sales                        
where amount>6000                        
group by customer                        
having sum(amount)>100000 order by sum(amount) desc                       
结果如下：                       
        客户名称        销售额       
        中海集运        1700000       
        中韩轮渡        1080000       
该查询首先对Sales进行一次筛选，筛选条件为销售额大于6000，然后再进行分组汇总，                       
此例中，中韩轮渡有两条记录在数据分组前已经被过滤掉。

wch2008wch

好东西！！！！！！！！！！！！！！！！！！

LANGJI-TIANYA02

路过.............
学习.......................

wuchaoli

不错!又学到了不少!

fsun

不错!支持下

lizi211

学习学习一下

CYGYKY

学习了！３Ｑ

ctgujunwei

太感谢了，我要好好学习下！

shichen17002216

楼主对ＧＲＯＵＰ　ＢＹ　的讲解很到位
谢谢

jd_sam

不错，支持一下！

sylovesy

好东西 学学看

emarsonlive

介绍得很细致，学习到了！

acong

很详细 辛苦了  顶一下

naojin

楼主辛苦了