#头条创作挑战赛#

分组查询

1 group by 分组查询语句

1)通过group by语句对一个或多个列对结果集进行简单的分组查询

--分组查询语句的格式
select 
分组列名 --A
聚合函数(分组列名)
from 表名
[where 条件表达式]--可省略,看需要
group by 分组列名  --B

--注意:B 后面的分组列名必须出现在
--A的 分组列名中

查看表中数据

假设:1)以SId分组,有15个组 2)以SCId分组,有5组 3)以SGender分组,有3组(以上图中的数据为基准)

分组条件:就是看分组列最后的唯一值有多少个(有重复值的算作一组)

USE School;

SELECT 
SCId AS N'班级编号分组' 
FROM dbo.Student
WHERE SId<=15
GROUP BY SCId  

--此查询语句的执行顺序
1)FROM dbo.Student 将数据查询出来
2)WHERE SId<=15 从1)中筛选符合条件的数据
3)GROUP BY SCId  在2)的结果集中进行分组
4)SELECT SCId 获取最后的数据结果

SELECT 
SGender AS N'性别分组'  
FROM dbo.Student
GROUP BY SGender   

简单的分组查询

2)进行分组查询时,在select语句后面添加不在group by语句中的其他列,会报错

--新增 SId 列进行分组查询
SELECT 
SId AS N'学生编号',
SGender AS N'性别分组'
FROM dbo.Student
GROUP BY SGender

错误的分组查询语句:新增其他列

错误原因:简单来说就是SId的查询结果的个数与SGender的查询结果个数不一致

如果没有 SId 列,查询结果为 3组;SId 列查询结果为每一个编号一组(23组-示例表)无法与分组查询结果对应显示,以SGender分组只能显示三列,而SId结果有23组,无法匹配显示(比如,系统是匹配显示每个班中 SId列的 前几列还是后几列,偶数列与奇数列等等)

3)可以将所有列都放在 group by 语句中,但是不建议也无意义,会失去分组查询的价值

SELECT 
SId AS N'学生编号',
SGender AS N'性别分组'
FROM dbo.Student
GROUP BY SId,SGender

无意义的分组查询语句

4)与集合函数一起进行分组查询,会将每个分组进行聚合处理

--集合函数就是昨天所说的5个
--统计每个班级的班级总人数
SELECT 
SCId AS N'班级分组',
COUNT(*) AS N'班级人数'
FROM dbo.Student
GROUP BY SCId

--注意:聚合函数是对每一组的数据进行聚合统计
--不是对分组后的数据进行聚合统计
--COUNT(*) 总共执行了 八次
--如班级编号为 2 聚合函数统计的是班级编号为2的总人数 5
--而不是分组后统计总人数的聚合
      
SELECT 
SCId AS N'班级分组',
COUNT(SGender) AS N'班级人数'
FROM dbo.Student
GROUP BY SCId

--班级编号 11的 两者统计不一样的原因
--在于聚合函数对null值不进行计算

统计各个班级的总人数

示例1:获取每个班级的平均年龄
SELECT 
SCId AS N'班级分组',
avg(SAge) AS N'平均年龄'
FROM dbo.Student
GROUP BY SCId
--获取编号11的总年龄
SELECT SUM(SAge) AS N'总年龄'
FROM dbo.Student

示例2:获取每个班级中年龄最大的
SELECT 
SCId AS N'班级分组',
max(SAge) AS N'最大年龄'
FROM dbo.Student
GROUP BY SCId

获取每个分组的平均/最大年龄

2 having(筛选条件) 筛选分组后的查询语句(用于对分组之后结果集的筛选)

--示例1:分组之前的筛选
--即对源数据进行筛选
SELECT 
SCId AS N'班级分组',
count(*) AS N'班级人数'
FROM dbo.Student
WHERE SGender=0 --筛选出性别为女的
GROUP BY SCId

--示例2:查询出每个班性别为女
--人数大于2的班级编号及其人数
--即对分组后这个小结果集进行筛选
SELECT 
SCId AS N'班级分组',
count(*) AS N'班级人数'
FROM dbo.Student
WHERE SGender=0 --筛选出性别为女的
GROUP BY SCId
HAVING count(*)>2
             
示例2的执行顺序
1)FROM dbo.Student
2)WHERE SGender=0  --筛选源数据
3)GROUP BY SCId
4)HAVING count(*)>2  --筛选分组后的数据
5)SELECT 
SCId AS N'班级分组',
count(*) AS N'班级人数'

having 对分组后 的筛选

示例3:增加排序语句
--新增排序语句
SELECT 
SCId AS N'班级分组',
count(*) AS N'班级人数'
FROM dbo.Student
WHERE SGender=0 --筛选出性别为女的
GROUP BY SCId
HAVING count(*)>2
--ORDER BY count(*) ASC
ORDER BY 班级人数 ASC
 --两个order by 语句都可以使用
--================================
ORDER BY 班级人数 ASC
这里之所以可以使用 别名:班级人数
在于排序的执行顺序在 语句count(*) AS N'班级人数'执行之后
否则只能使用 语句 ORDER BY count(*) ASC

分组中使用排序语句与别名