MySQL中查询获取每个班级成绩前三名的学生信息_查询每个系总成绩前三名学生的姓名

CREATE TABLE t_testscore(    pk_id INT PRIMARY KEY,    c_name VARCHAR(50) ,    c_score INT,    c_class INT )DEFAULT CHARSET=utf8;
 
INSERT INTO t_testscore VALUES (1, '张三6', 66, 1),(2, '张三5', 65, 1),(3, '张三4', 64, 1), (4, '张三3', 63, 1),(5, '张三2', 62, 1);
 
INSERT INTO t_testscore VALUES (11, '李四6', 76, 2),(12, '李四5', 75, 2),(13, '李四4', 74, 2), (14, '李四3', 73, 2),(15, '李四2', 72, 2);

SELECT * FROM t_testscore t WHERE EXISTS(SELECT COUNT(*) FROM t_testscore ts  WHERE ts.c_score>=t.c_score and ts.c_class=t.c_class  GROUP BY ts.c_class  HAVING COUNT(*)<=3)  
 ORDER BY c_class,c_score DESC;

上述的sql语句在执行过程中相当于一个for循环处理，将t表中的每一条record取出来去跟ts中的数据做比较，如果满足条件就将数据进行展示。

转换成java的伪代码可以这样理解:

import java.util.ArrayList;
import java.util.List;
 
public class Top3Score {
    public static void main(String[] args) {
        List<Record> recordList = RecordMapper.getAllRecord();
        List<Record> res = new ArrayList<>();
        for (Record recordComp : recordList){
            for (Record record: recordList){
                if (record.getClassNo() == recordComp.getClassNo()
                    && record.getScore() >= recordComp.getScore()
                    && getRankOfScoreInClass(record,recordList) <= 3){
                    res.add(record);
                }
            }
        }
        System.out.println(res.toString());
    }
 
    private static int getRankOfScoreInClass(Record record,List<Record> recordList){
        int rank = 1;
        for (Record recordEle : recordList){
            if (record.getScore() < recordEle.getScore()){
                rank++;
            }
        }
        return rank;
    }
 
    private class Record{
        private int id;
        private String name;
        private int score;
        private int classNo;
 
        public int getId() {
            return id;
        }
 
        public String getName() {
            return name;
        }
 
        public int getScore() {
            return score;
        }
 
        public int getClassNo() {
            return classNo;
        }
    }
    
    private static class RecordMapper{
        private static List<Record> getAllRecord(){
            return new ArrayList<>();
        }
    }
}