数据库查前三名
首先创建表:
CREATE TABLE score_t(
name VARCHAR(10),
subject VARCHAR(10),
score INT(10)
);
插入数据:
INSERT INTO score_t VALUES
("张三","java",71),
("李四","java",82),
("王五","java",90),
("赵六","java",98),
("孙七","java",90),
("张三","python",81),
("李四","python",92),
("王五","python",93),
("赵六","python",97),
("孙七","python",70);
思路:
这里只需要先将T2表数据按科目和分数,去重,再查找满足比它分数高的记录数 < 3 的数据即可。
查询各科成绩前三名,前三名都有一个特点,比他们成绩高的人数小于3,成绩就是前三名。
正确写法:
![](https://img2020.cnblogs.com/blog/1679495/202101/1679495-20210104221131914-674964348.png)
另一种写法:用了limit
select aa.* from ( select DISTINCT name as n1, ( select sum(mark) from te where name=n1 )as g from te t ) aa where aa.g in ( select ta.grade as g from ( select (select sum(mark) from te where name=t1.name )as grade from te t1 GROUP BY grade desc LIMIT 3 ) as ta )
摘自:https://blog.csdn.net/weixin_44497013/article/details/107317719