(selectmax(score) from SC where Cid = m.Cid) [最高分],
(selectmin(score)from SC where Cid = m.Cid) [最低分],
(selectcast(avg(score) asdecimal(18,2)) from SC where Cid = m.Cid) [平均分],
cast((selectcount(1) from SC where Cid = m.Cid and score >=60)*100.0/ (selectcount(1) from SC where Cid = m.Cid) asdecimal(18,2)) [及格率(%)],
cast((selectcount(1) from SC where Cid = m.Cid and score >=70and score <80 )*100.0/ (selectcount(1) from SC where Cid = m.Cid) asdecimal(18,2)) [中等率(%)],
cast((selectcount(1) from SC where Cid = m.Cid and score >=80and score <90 )*100.0/ (selectcount(1) from SC where Cid = m.Cid) asdecimal(18,2)) [优良率(%)],
cast((selectcount(1) from SC where Cid = m.Cid and score >=90)*100.0/ (selectcount(1) from SC where Cid = m.Cid) asdecimal(18,2)) [优秀率(%)]
from Course m
orderby m.Cid?
--19、按各科成绩进行排序，并显示排名
思路：利用over(partition by 字段名order by 字段名)函数。
正常排序：1，2，3
select row_number() over(partition by cid order by cid,score desc) as sort,* from sc
合并重复不保留空缺：1，1，2，3
select dense_rank() over(partition by cid order by cid,score desc) as sort,* from sc
合并重复保留空缺：1，1，3
select rank() over(partitionby cid order by cid,score desc) as sort,* from sc?
--19.1 sql 2000用子查询完成
--Score重复时保留名次空缺
select t.* , px = (selectcount(1) from SC where Cid = t.Cid and score > t.score) +1from sc t orderby t.cid , px
--Score重复时合并名次
select t.* , px = (selectcount(distinct score) from SC where Cid = t.Cid and score >= t.score) from sc t orderby t.cid , px?
--19.2 sql 2005用rank,DENSE_RANK完成
--Score重复时保留名次空缺(rank完成)
select t.* , px = rank() over(partition by cid orderby score desc) from sc t orderby t.Cid , px
--Score重复时合并名次(DENSE_RANK完成)
select t.* , px = DENSE_RANK() over(partition by cid orderby score desc) from sc t orderby t.Cid , px?
--20、查询学生的总成绩并进行排名
思路：所有学生的总成绩（一个记录集合），再使用函数进行排序。
select rank() over(order by sum(a.score) desc) as ranking,s.sid,s.sname,sum(a.score) as '总成绩' from student s inner join sc a
on s.sid=a.sid groupby s.sid,s.sname
这个查询只能查询到有成绩的7名学生。?
select dense_rank() over(order by isnull(sum(a.score),0) desc) as ranking,s.sid,s.sname,
isnull(sum(a.score),0) as '总成绩'
from student s left join sc a on s.sid=a.sid group by s.sid,s.sname
用了leftjoin就可以查询到所有的8名学生了，包括没有成绩的1名学生。?
--20.1 查询学生的总成绩
select m.Sid [学生编号] ,
m.Sname [学生姓名] ,
isnull(sum(score),0) [总成绩]
from Student m leftjoin SC n on m.Sid = n.Sid
groupby m.Sid , m.Sname
orderby[总成绩]desc?
--20.2 查询学生的总成绩并进行排名，sql 2000用子查询完成，分总分重复时保留名次空缺和不保留名次空缺两种。
select t1.* , px = (selectcount(1) 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
)t2 where总成绩> t1.总成绩) +1from
(
select m.Sid [学生编号] ,
m.Sname [学生姓名] ,
isnull(sum(score),0) [总成绩]
from Student m leftjoin SC non m.Sid = n.Sid
groupby m.Sid, m.Sname
)t1
orderby px?
select t1.* , px = (selectcount(distinct总成绩) 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
)t2 where总成绩>= t1.总成绩) 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
)t1
orderby px?
--20.3 查询学生的总成绩并进行排名，sql 2005用rank,DENSE_RANK完成，分总分重复时保留名次空缺和不保留名次空缺两种。
select t.* , px = rank() over(orderby[总成绩]desc) from
(
select m.Sid [学生编号] ,
m.Sname [学生姓名] ,
isnull(sum(score),0) [总成绩]
from Student m leftjoin SC non m.Sid = n.S