select s.sid,s.sname,row_number() over(order by avg(score) desc) as ranking,convert(decimal(18,2),
avg(score)) as '平均成绩' from student s inner join sc a on s.sid=a.sid group by s.sid,s.sname
只显示有成绩的学生。?
select s.sid,s.sname,row_number() over(order by avg(score) desc) as ranking,convert(decimal(18,2),
avg(score)) as '平均成绩' from student s left join sc a on s.sid=a.sid group by s.sid,s.sname
显示所有学生。?
--24.1 查询学生的平均成绩并进行排名，sql 2000用子查询完成，分平均成绩重复时保留名次空缺和不保留名次空缺两种。
select t1.* , px = (selectcount(1) from
(
select m.Sid [学生编号] ,
m.Sname [学生姓名] ,
isnull(cast(avg(score) asdecimal(18,2)),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(cast(avg(score) asdecimal(18,2)),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(cast(avg(score) asdecimal(18,2)),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(cast(avg(score) asdecimal(18,2)),0) [平均成绩]
from Student m leftjoin SC non m.Sid = n.Sid
groupby m.Sid, m.Sname
)t1
orderby px?
--24.2 查询学生的平均成绩并进行排名，sql 2005用rank,DENSE_RANK完成，分平均成绩重复时保留名次空缺和不保留名次空缺两种。
select t.* , px = rank() over(orderby[平均成绩]desc) from
(
select m.Sid [学生编号] ,
m.Sname [学生姓名] ,
isnull(cast(avg(score) asdecimal(18,2)),0) [平均成绩]
from Student m leftjoin SC non m.Sid = n.Sid
groupby m.Sid, m.Sname
)t
orderby px?
select t.* , px = DENSE_RANK() over(orderby[平均成绩]desc) from
(
select m.Sid [学生编号] ,
m.Sname [学生姓名] ,
isnull(cast(avg(score) asdecimal(18,2)),0) [平均成绩]
from Student m leftjoin SC non m.Sid = n.Sid
groupby m.Sid, m.Sname
)t
orderby px?
--25、查询各科成绩前三名的记录
思路：各学科成绩排序（一个记录集合），再取前3。
select * from
(select row_number() over(partition by a.cidorder by a.score desc)as ranking,
s.sid,s.sname,a.score from student s innerjoin sc a on s.sid=a.sid) t where ranking in (1,2,3)?
--25.1 分数重复时保留名次空缺
select m.* , n.Cid , n.score from Student m, SC n where m.Sid = n.Sid and n.score in
(selecttop3 score from sc where Cid = n.Cid orderby score desc) orderby n.Cid , n.score desc?
--25.2 分数重复时不保留名次空缺，合并名次
--sql 2000用子查询实现
select*from (select t.* , px = (selectcount(distinct score) from SC where Cid = t.Cid and score >= t.score) from sc t) m where px between1and3orderby m.cid , m.px
--sql 2005用DENSE_RANK实现
select*from (select t.* , px = DENSE_RANK() over(partition by cid orderby score desc) from sc t) m where px between1and3orderby m.Cid , m.px?
--26、查询每门课程被选修的学生数
思路：每门课被选修的学生数（一个记录集合）。
select * fromcourse a inner join
(select cid,count(*) as '人数' from sc group by cid) b
on a.cid=b.cid?
select a.cid,a.cname,count(1) as '人数' from course a inner join sc b
on a.cid=b.cid groupby a.cid,a.cname?
select cid , count(Sid)[学生数]from sc groupby Cid?
--27、查询出只有两门课程的全部学生的学号和姓名
select Student.Sid ,Student.Sname
from Student , SC
where Student.Sid = SC.Sid
groupby Student.Sid , Student.Sname
havingcount(SC.Cid) =2
orderby Student.Sid?
--28、查询男生、女生人数
思路：
select ssex,count(1)as '人数' from student group by ssex?
selectcount(Ssex) as男生人数from Student where Ssex = N'男'
selectcount(Ssex) as女生人数from Student where Ssex = N'女'
selectsum(casewhen Ssex = N'男'then1else0end) [男生人数],sum(casewhen