- 论坛徽章:
- 0
|
本帖最后由 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怎么写? |
|