这里看到一个SQL题目:查询学生总成绩的前三名,给出MySQL和Oracle的写法

学生总成绩前三名

INSERT INTO `student_grade`(`name`, `course`, `grade`) VALUES ('nick', 'yuwen', 88);
INSERT INTO `student_grade`(`name`, `course`, `grade`) VALUES ('jack', 'shuxue', 95);
INSERT INTO `student_grade`(`name`, `course`, `grade`) VALUES ('nick', 'shuxue', 68);
INSERT INTO `student_grade`(`name`, `course`, `grade`) VALUES ('jack', 'yuwen', 96);
INSERT INTO `student_grade`(`name`, `course`, `grade`) VALUES ('susan', 'yuwen', 100);
INSERT INTO `student_grade`(`name`, `course`, `grade`) VALUES ('susan', 'shuxue', 95);
INSERT INTO `student_grade`(`name`, `course`, `grade`) VALUES ('lee', 'yuwen', 100);
INSERT INTO `student_grade`(`name`, `course`, `grade`) VALUES ('lee', 'shuxue', 95);
INSERT INTO `student_grade`(`name`, `course`, `grade`) VALUES ('zhang', 'yuwen', 58);
INSERT INTO `student_grade`(`name`, `course`, `grade`) VALUES ('zhang', 'shuxue', 91);

MySQL使用LIMIT关键字:

SELECT name
FROM student_grade
GROUP BY name
ORDER BY sum(grade) DESC
LIMIT 0,3

Oracle使用ROWNUM关键字

select name FROM(
    SELECT name
    FROM student_grade
    GROUP BY name
    ORDER BY sum(grade) DESC
)WHERE rownum <=3;

标签: SQL

添加新评论