select a.schoolid,c.schoolname,
a.id,a.classname
into #schoolclass
from classinfo a
inner join (select schoolid,
学生人数=max(学生人数)
from classinfo
group by schoolid) b on a.schoolid=b.schoolid
and a.学生人数=b.学生人数
inner join school c on a.schoolid=c.id
select 'stuid'=e.id,e.classname,e.score,e.classid
into #stu
from stuinfo e
inner join (
select c.classid,
score=max(score)
from stuinfo c
inner join #schoolclass d on c.classid=d.id
group by c.classid) f on e.classid=f.classid
and e.score=f.score
select a.*, b.*
from #schoolclass a
inner join #stu b on a.id=b.classid
表结构
schoolInfo
id schoolName
1 实验中学a
2 实验中学b
classInfo
id className stuNum schoolid
1 初一一班 54 1
2 初一二班 55 1
3 初二一班 53 1
4 初一一班 53 2
5 初一二班 46 2
6 初二一班 63 1
stuInfo
id stuName score classid
1 张三 77 2
2 张士 66 1
3 李四 78 1
select a.*,b.className,b.stuNum,c.stuName,c.score
from schoolInfo as a inner join (
select id,className,stuNum,schoolid
from classinfo where
id in
(select max(stuNum) from classInfo group by schoolid)
) as b on a.id=b.shoolid
left join(
select id,stuName,score,classid
from stuInfo where id in
(select max(score) from stuinfo group by classid)
) as c on c.classid=b.id
SELECT TOP 1 MAX(分数),COUNT(1) FROM 学生表 GROUP BY 班级ID ORDER BY COUNT(1) DESC
