SqlServer–分组查询和统计

以下是学习笔记：

1，分组统计

 

 2，分组后筛选统计

select 班级=StudentClass.ClassName,人数=COUNT*),C#最高分=MaxCSharp),DB最高分=MAXSQLServerDB),
AVGCSharp) as C#平均分,AVGSQLServerDB) as DB平均分
from Students
inner Join StudentClass on Students.ClassId =StudentClass.ClassId
inner join ScoreList on ScoreList.StudentId=Students.StudentId
group by ClassName --ClassName 按照班级的名称分组


select 班级=StudentClass.ClassName,人数=COUNT*),C#最高分=MaxCSharp),DB最高分=MAXSQLServerDB),
AVGCSharp) as C#平均分,AVGSQLServerDB) as DB平均分
from Students
inner Join StudentClass on Students.ClassId =StudentClass.ClassId
inner join ScoreList on ScoreList.StudentId=Students.StudentId
group by ClassName --ClassName 按照班级的名称分组
having AVGCSharp)>=70 and AVGSQLServerDB)>=70 --分组统计后不能有where条件筛选，要用having

　

 3，重复数据问题

--在知道那个字段重复的情况
select StudentId from ScoreList  group by StudentId having COUNT*)>1

--查询所有重复的记录
select * from ScoreList
where StudentId inselect StudentId from ScoreList  group by StudentId having COUNT*)>1)
order by StudentId

--其他方法
select * from ScoreList
where select COUNT*) from ScoreList  s where s.StudentId=ScoreList.StudentId)>1
order by StudentId

--过滤掉重复数据
select distinct StudentId,CSharp from ScoreList

select distinct StudentId,CSharp,SQLServerDB from ScoreList