汇总查询

一、GROUP BY的使用   


注意以下几项：
1、在group By子句中出现的字段，必须出现在Select语句的列表中(子集关系)。

2、group by与order by连用，order by 子句中必包含group by子句后所有字段

3、group by关键字后可以使用多个字段名作为分组字段，这样，系统将根据这些
字段的先后顺对果集进行更加详细地分组。



1、从表order details中返回订购信息。查询将按每一种产品的标识并计算总订购数量。
结果集中，总产品数量用聚集函数SUM计算并显示出来

use northwind
select ProductID,sum(Quantity) 该产品总量
from dbo.[Order Details]
group by ProductID
go

2、在xscj数据库中，将学生数据库中各专业名输出

use xscj
select 专业名
from xs
group by 专业名
go

3、在xscj数据库中，求学生数据库中各专业的学生数

use xscj
select 专业名, count(学号)
from xs
group by 专业名
go


4、在xscj数据库中，求被选修的各门课程的平均成绩和选修该门课程的人数

use xscj
select xk.课程号,avg(xk.成绩) 平均成绩,count(*) 选修人数
from xs_kc xk
join xs x
on xk.学号=x.学号
group by xk.课程号
go

use xscj
select k.课程名,avg(xk.成绩) 平均成绩,count(*) 选修人数
from xs_kc xk join xs x on xk.学号=x.学号
join kc k  on k.课程号=xk.课程号
group by k.课程名


二、GROUP BY子句与HAVING子句
group by与having连用， having子句将对group by子句选择出来的结果进行再次筛选，
最后输出符合having子句中条件的记录。如果having子句中包含多个条件，那么这些条件将
通过逻辑运算符(and、or或not)组合在一起。 having子句的语法与where子句的语法类似，
唯一不同的是having子句中可以包含聚合函数，而且having子句可以引用选择列表中(select
后面出现的字段)出现的任意项。

5、列出表dbo.Order Details中数量多于30的每种产品

use northwind
select distinct p.Productname,o.Quantity from dbo.[Order Details] o
join dbo.Products  p on p.ProductID=o.ProductID
group by p.Productname,o.Quantity
having o.quantity>30
go


6、 在[order details]列出了订购数量大于12的每种产品的标识和数量

use northwind
select ProductID,Quantity from dbo.[Order Details]
group by ProductID,Quantity
having Quantity>12
go



7、查找xscj数据库中平均成绩在85分以上的学生的学号和和平均成绩

use xscj
select 学号,avg(成绩) 平均成绩 from xs_kc
group by 学号
having avg(成绩)>85
go



8、在xscj数据库中，查找选修课程超过两门且成绩都在85分以上的学生的学号

use xscj
select 学号,count(distinct 课程号) 课程,avg(成绩) 平均成绩
from xs_kc
group by 学号
having count(distinct 课程号)>=2 and avg(成绩)>85
go

 
9、在xscj数据库中，查找通信工程专业平均成绩在80分以上的学生的学号和平均成绩

use xscj
select xs.专业名,xs_kc.学号,avg(成绩) 平均成绩
from xs_kc
join xs
on xs_kc.学号=xs.学号
group by xs_kc.学号,xs.专业名
having avg(成绩)>80 and xs.专业名='通信工程'
go


三、在结果集中创建汇总值
同时使用group by子句和操作符rollup，计算每个分组中的合计值和所有分组的汇总值。
使用with rollup关键字的主要目的是自动对group by子句中列出的第一个分组字段进行分组汇总运算。
注意：
    1、计算出的合计值和汇总值，将在结果集中增加一行，
          新增的一行以null来标识。

同时使用group By子句和操作符cube，创建所有基于groupBy子句中，指定字段的所有组合，并对组合进行
汇总计算。

注意：
    1、如果group by指定了N个字段或表达式，则结果集中
          产生2N－1种组合。
    2、 group by 最多可以包含10个字段或表达式。






10、联合使用GROUP BY子句和ROLLUP或CUBE操作符，它们将在结果集中创建汇总值
列出了表Order Details 中所有的记录，并对每种产品的数量进行汇总。

use northwind
select distinct ProductID,Quantity from [Order Details]
group by productid,quantity
with rollup
--with cube
go

11、 在Order Details表中，将包含每次订购的每种产品的总数量，每次订购的所有产品的总数量，
所订购的每种产品的总数量，所有订购的所有产品的总数量

use northwind
select orderid,productid,sum(quantity) 总数量
from [order details]
group by OrderID,ProductID
with cube
go


12、在xscj数据库中产生一个结果集，包括每个专业的男生、女生人数、总人数及学生总人数

use xscj
select 专业名,性别,count(学号) from xs
group by 专业名,性别
with cube
go

use xscj
select 专业名,性别,count(学号) from xs
group by 专业名,性别
with rollup
go

13、在xscj数据库中产生一个结果集，包括个专业每门课程的平均成绩、
每门课程的总平均成绩和所有课程的总平均成绩


use xscj
select xs.专业名,kc.课程名,avg(成绩) 平均成绩
from xs_kc
join xs
on xs.学号=xs_kc.学号
join kc
on kc.课程号=xs_kc.课程号
group by xs.专业名,kc.课程名
with cube
--with rollup
go



14、在xscj数据库上产生一个结果集，包括每个专业的男生、女生人数、总人数及男生总数、
女生总数、学生总人数

use xscj
select 专业名,性别,count(*)
from xs
group by 专业名,性别
with cube
go




15、在xscj数据库上产生一个结果集，包括各专业每门课程的平均成绩、每门课程的总平均成绩、
每个专业的总平均成绩和所有课程的总平均成绩

use xscj
select 课程号,avg(成绩)
from xs_kc
group by 课程号
with cube
go