oracle排序问题?
本帖最后由 zhuzhu316 于 2012-08-03 22:18 编辑问个简单排序
学生成绩表:student
create table student
(
s_id char(20),
s_name char(16),
s_score number(10)
);
insert into student values('A001','张三',100);
insert into student values('A001','张三',100);
insert into student values('A002','张五',99);
insert into student values('A003','张六',97);
insert into student values('A004','张七',96);
insert into student values('A005','张八',95);
insert into student values('A006','张九',94);
insert into student values('A007','王一',93);
insert into student values('A008','王二',92);
insert into student values('A009','王三',91);
insert into student values('A010','王四',90);
insert into student values('A012','王五',79);
insert into student values('A013','王六',77);
insert into student values('A014','王七',76);
insert into student values('A015','王八',75);
insert into student values('A016','王九',74);
1、我想问问查询成绩第N名的学生?
2、我想查询成绩排名前N的学生?
3、根据ID把成绩sum后在根据成绩sum的值在排序?
提供参考
select s_id,s_name,dense_rank() over (order by s_score desc) 名次 from student score;
select s_name,sum(s_score)
from student
group by s_name order by sum(s_score) desc;
图3的SQL怎么写? e:em48::'( 哭、、没有回复 求解啊。。 我终于写出来了,大神门看看 有什么错误吗、会有什么缺陷吗?
select s_id,sum(s_score)total,
dense_rank() over(order by sum(s_score) desc) 排名
from student
group by s_id;
在sum之后使用分析函数进行排序+指定返回N条记录
select s_id,sum(s_score)total,
dense_rank() over(order by sum(s_score) desc) 排名
from student
where rownum<= 6
group by s_id;
我想问问写6为什么只返回5条? 指定返回排名第三的怎么写? 回复 6# zhuzhu316
查询出6条,但是显示5条,因为A001有两条,已经被你分组合并了。 在结果集外层加select... from ... where rownum = 3 你很喜欢用分析函数哈
页:
[1]
2