groupby m.Sid, m.Sname
)t
orderby px?
select t.* , px = DENSE_RANK() over(orderby[总成绩]desc) from
(
select m.Sid [学生编号] ,
m.Sname [学生姓名] ,
isnull(sum(score),0) [总成绩]
from Student m leftjoin SC non m.Sid = n.Sid
groupby m.Sid, m.Sname
)t
orderby px?
--21、查询不同老师所教不同课程平均分从高到低显示
思路：不同老师所教不同课程的平均分（一个记录集合），再使用函数over(order by 字段名)
select rank() over(order by convert(decimal(5,2),avg(score)) desc) as ranking,c.tid,c.tname,b.cid,b.cname,
convert(decimal(5,2),avg(score)) as '平均分' from sc a
inner join course b on a.cid=b.cid inner join teacher c on b.tid=c.tid group by c.tid,c.tname,b.cid,b.cname?
select m.Tid , m.Tname , cast(avg(o.score) asdecimal(18,2)) avg_score
from Teacher m , Course n , SCo
where m.Tid = n.Tid and n.Cid = o.Cid
groupby m.Tid , m.Tname
orderby avg_score desc?
--22☆、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
思路：所有课程成绩的学生及课程信息（一个记录集合），再利用函数排序（一个记录集合），选择第2名和第3名的记录。
;with abc as
(select row_number() over(partition by a.cidorder by a.score desc)as ranking,s.sid,s.sname,a.cid,b.cname,
a.score from student s inner join sc a on s.sid=a.sid inner join course b on a.cid=b.cid)
select * from abc where ranking in (2,3)?
select * from
(select row_number() over(partition by a.cidorder by a.score desc)as ranking,s.sid,s.sname,a.cid,b.cname,
a.score from student s inner join sc a on s.sid=a.sid inner join course b on a.cid=b.cid) t
where t.ranking in(2,3)?
--22.1 sql 2000用子查询完成
--Score重复时保留名次空缺
select*from (select t.* , px = (selectcount(1) from SC where Cid = t.Cid and score > t.score) +1from sc t) m where px between2and3orderby m.cid , m.px
--Score重复时合并名次
select*from (select t.* , px = (selectcount(distinct score) from SC where Cid = t.Cid and score >= t.score) from sc t) m where px between2and3orderby m.cid , m.px?
--22.2 sql 2005用rank,DENSE_RANK完成
--Score重复时保留名次空缺(rank完成)
select*from (select t.* , px = rank() over(partition by cid orderby score desc) from sc t) m where px between2and3orderby m.Cid , m.px
--Score重复时合并名次(DENSE_RANK完成)
select*from (select t.* , px = DENSE_RANK() over(partition by cid orderby score desc) from sc t) m where px between2and3orderby m.Cid , m.px?
--23☆☆☆、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
思路：SC表和COURSE表联合查询（一个记录集合），然后每个字段都看做是一个子查询，最后将这些子查询拼接起来。
select b.cid as '课程编号',b.cname as '课程名称',
count(1) as'总人数',
count(case when a.score<60 then 1 else null end) as '不及格人数',
convert(decimal(5,2),count(case when a.score>=0 and a.score<60 then 1 else null end)*100/count(1)) as '不及格率%',
count(case when a.score>=60 and a.score<70 then 1 else null end) as '及格人数',
convert(decimal(5,2),count(case when a.score>=60 and a.score<70 then 1 else null end)*100/count(1)) as '及格率%',
count(case when a.score>=70 and a.score<85 then 1 else null end) as '优良人数',
convert(decimal(5,2),count(case when a.score>=70 and a.score<85 then 1 else null end)*100/count(1)) as '优良率%',
count(case when a.score>=85 then 1 else null end) as '优秀人数',
convert(decimal(5,2),count(case when a.score>=85 then 1 else null end)*100/count(1)) as '优秀率%'
from sc a inner join course b on a.cid=b.cid group by b.cid,b.cname
以上方法为横向显示。?
select b.cid as '课程编号',b.cname as '课程名称',(case when score<60 then '0-59'
when score>=60 and score<70 then '60-69'
when score>=70 and score<85 then '70-85'
else '85-100' end) as '分数段',
count(1) as'人数',
convert(decimal(18,2),count(1)*100/(select count(1) from sc where cid=b.cid)) as '百分比'
from sc a inner join course b on a.cid=b.cid group by all b.cid,b.cname,(case when score<60 then '0-59'
when score>=60 and score<70 then '60-69'
when score>=70 and score<85 then '70-85'
else '85-100' end)
order by b.cid,b.cname,'分数段'
以上方法为纵向显示，但为0的就不显示了。?
--23.1 统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]
--横向显示
select Course.Cid [课程编号] , Cna