MySQl查询各科成绩前三名创建表
create table student (
name varchar(20) ,
lesson varchar(20),
mark float
mysql group by order by) ;
插⼊数据
insert into student values('john','Math',60);
insert into student values('john','Eng',50);
insert into student values('john','HIstory',56);
insert into student values('Mike','Eng',51);
insert into student values('Mike','Math',59);
insert into student values('Mike','HIstory',55);
insert into student values('Mark','Eng',71);
insert into student values('Mark','Math',89);
insert into student values('Mark','HIstory',95);
insert into student values('张三','Eng',61);
insert into student values('张三','Math',79);
insert into student values('张三','HIstory',85);
insert into student values('李明','Eng',51);
insert into student values('李明','Math',69);
insert into student values('李明','HIstory',95);
查询
#⽅法⼀
SELECT T1.*
FROM student T1
LEFT JOIN (
SELECT DISTINCT lesson,mark
FROM student) T2 ON T1.lesson = T2.lesson AND T1.mark <= T2.mark GROUP BY name,lesson,mark
HAVING COUNT(1) <=3ORDER BY lesson,mark DESC;
#⽅法⼆
SELECT s1.*
FROM student s1
WHERE (
SELECT COUNT(1)
FROM student s2
WHERE s1.lesson=s2.lesson AND s1.mark<s2.mark)<3
ORDER BY s1.lesson,s1.mark DESC;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。