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<>();
}
}
}