group by having分组筛选

create table TestClassInfo(
 id int identity(1,1) primary key not null,
 UserNo int not null,
 UserName nvarchar(50) not null,
 ClassType nvarchar(10) not null,
 Score float default(0) not null
)
go
insert into TestClassInfo (UserNo,UserName,ClassType,Score)
values
(1,'张三','语文',50),(2,'李四','语文',80),(3,'王五','语文',90),
(1,'张三','数学',99),(2,'李四','数学',97),(3,'王五','数学',70),
(1,'张三','英语',80),(2,'李四','英语',98),(3,'王五','英语',60)
go

--查询各科成绩最高和最低的同学
select b.* from (select ClassType,MAX(Score) as MaxScore,min(Score) as MinScore from TestClassInfo group by ClassType) a,TestClassInfo b
where b.ClassType = a.ClassType and b.Score in (a.MaxScore,a.MinScore)
--查询语文单科成绩最高和最低的同学，在group by 后添加having筛选
select b.* from (select ClassType,MAX(Score) as MaxScore,min(Score) as MinScore from TestClassInfo group by ClassType having ClassType = '语文') a,TestClassInfo b
where b.ClassType = a.ClassType and b.Score in (a.MaxScore,a.MinScore)

--drop table TestClassInfo