SELECT *
FROM 学生表 a,
课程表 b,
成绩表 c
WHERE a.ID=c.学生ID
AND b.ID=c.课程ID
AND rownum<=5
AND b.ID=00000000001
ORDER BY 分数;
SELECT *
FROM 学生表 a,
课程表 b,
成绩表 c
WHERE a.ID=c.学生ID
AND b.ID=c.课程ID
AND rownum<=5
AND b.ID=00000000002
ORDER BY 分数;
...
FROM 学生表 a,
课程表 b,
成绩表 c
WHERE a.ID=c.学生ID
AND b.ID=c.课程ID
AND rownum<=5
AND b.ID=00000000001
ORDER BY 分数;
SELECT *
FROM 学生表 a,
课程表 b,
成绩表 c
WHERE a.ID=c.学生ID
AND b.ID=c.课程ID
AND rownum<=5
AND b.ID=00000000002
ORDER BY 分数;
...
From 学生表 a,课程表 b,成绩表 c
Where c.学生ID= a.ID AND c.课程ID=a.ID AND rownum=5
Order by 课程1 Uion Select ...
create table student
(
s_id int,
s_name char(10)
)
create table class
(
c_id int,
c_name char(10)
)create table grade
(
s_id int,
c_id int,
cj int
)--测试数据
insert student values(1,'A')
insert student values(2,'B')
insert student values(3,'C')
insert student values(4,'D')
insert student values(5,'E')insert class values(1,'历史')
insert class values(2,'数学')insert grade values(1,1,60)
insert grade values(2,1,70)
insert grade values(3,1,80)
insert grade values(4,1,90)
insert grade values(5,1,100)
insert grade values(1,2,85)
insert grade values(2,2,67)
insert grade values(3,2,94)
insert grade values(4,2,63)
insert grade values(5,2,87)
select a.c_id,a.s_id,a.cj from grade a
join grade b
on a.c_id = b.c_id
group by a.c_id,a.s_id,a.cj
having count(case when a.cj <= b.cj then 1 else null end) < = 5 --可动态修改
order by a.c_id,a.cj desc--你可以以动态的修改: n <= n 来获得
--每门课程的前n个最高分的
(select (select 姓名 from 学生表 where id=学生ID) 学生姓名,(select 名字 from 课程表 where ID=课程ID) 课程名字,分数,rank() over(partition by 课程ID order by 分数 desc) rk from 成绩表)
where rk<=5
order by 课程名字,分数 desc
以上我在sql server 2000调试通过!
不知在Oracle是否可以通过!
我想应该可以!
最后要的是最高的5个分数,人数多少没有限制
select a.c_id,a.s_id,a.cj
from grade a join
(
select c_id,cj
from grade
group by c_id,cj
) b
on a.c_id = b.c_id
group by a.c_id,a.s_id,a.cj
having count(case when a.cj <= b.cj then 1 else null end) < = 5 --可动态修改
order by a.c_id,a.cj desc--你可以以动态的修改: n <= n 来获得
--每门课程的前n个最高分(包括重复分数)