求sql语句或存储过程！

[hzcao]

假设我要做一个学校的排名表，学校排名是根据该学校中学生学习成绩最高来决定学校的排名先后,数据结构如下：

Table Name: school
schoolid-----schoolname

Table Name: Class
classid---schoolid----classname

Table Name: student
studentid--classid----studentname--totalscore(总分)

经过以上三个表查询得到：schoolid---schoolname---totalscore，其中totalscore=该校中学生成绩最高数，排列顺序由高到低

[mugua604]

LZ要得到什么?

[冷箫轻笛]

select t3.schoolid,t3.schoolname,max(t1.totalscore) as totalscore
from student t1 left join Class t2 on t1.classid = t2.classid
left join school t3 on t2.schoolid = t3.schoolid
group by t3.schoolid,t3.schoolname
order by totalscore desc

[dulei115]

select a.school, a.schoolname, max(c.totalscore) as totalscore
from school a join class b on a.schoolid = b.schoolid
join student c on b.classid = c.classid
group by a.school, a.schoolname
order by max(c.totalscore) desc

[hzcao]

不好意思，不是需要这个，需要查询的数据应该是schoolid---schoolname--classid---totalscore

classid是包含本校最高分数的班级

[Well]

用order by 就可，

[dulei115]

select a.schoolid, a.schoolname, b.classid, totalscore
from school a join class b on a.schoolid = b.schoolid
join student c on b.classid = c.classid
where not exists(select 1
from class d
join student e on d.classid = e.classid
where d.schoolid = a.schoolid and e.totalscore > c.totalscore)

[dulei115]

if object_id('school') is not null drop table school
select 1 as schoolid, '学校1' as schoolname
into school
union select 2, '学校2'

if object_id('class') is not null drop table class
select 11 as classid, 1 as schoolid, '班级1' as classname
into class
union select 12, 1, '班级2'
union select 13, 1, '班级3'
union select 21, 2, '班级1'
union select 22, 2, '班级2'

if object_id('student') is not null drop table student
select 111 as studentid, 11 as classid, 'aaa' as studentname, 100 as totalscore
into student
union select 112, 11, 'bbb', 101
union select 113, 11, 'ccc', 103
union select 121, 12, 'ddd', 104
union select 122, 12, 'eee', 105
union select 211, 21, 'fff', 106
union select 212, 21, 'ggg', 107
union select 221, 22, 'hhh', 108
union select 222, 22, 'iii', 109
----------------------------------------------------------------------
select a.schoolid, a.schoolname, b.classid, c.totalscore
from school a join class b on a.schoolid = b.schoolid
join student c on b.classid = c.classid
where not exists(select 1
from class d
join student e on d.classid = e.classid
where d.schoolid = a.schoolid and e.totalscore > c.totalscore)
/*
schoolid schoolname classid totalscore
----------- ---------- ----------- -----------
1 学校1 12 105
2 学校2 22 109
*/
--加几个显示字段也可以
select a.schoolid, a.schoolname, b.classid, b.classname, c.studentid, c.studentname, c.totalscore
from school a join class b on a.schoolid = b.schoolid
join student c on b.classid = c.classid
where not exists(select 1
from class d
join student e on d.classid = e.classid
where d.schoolid = a.schoolid and e.totalscore > c.totalscore)
/*
schoolid schoolname classid classname studentid studentname totalscore
----------- ---------- ----------- --------- ----------- ----------- -----------
1 学校1 12 班级2 122 eee 105
2 学校2 22 班级2 222 iii 109
*/
----------------------------------------------------------------------
drop table school
drop table class
drop table student