企业笔试Mysql真题持续分享|点个关注

学员出去面试遇到的真题，分享给大家

CREATE TABLE `stuscore` (  `name` varchar(255) DEFAULT NULL,  `subject` varchar(255) DEFAULT NULL,  `score` int(255) DEFAULT NULL,  `stuid` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;


insert into dbo.stuscore values('张三','数学' ,89,1);insert into dbo .stuscore values('张三','语文',80,1);insert into dbo.stuscore values('张三','英语',70,1);insert into dbo.stuscore values('李四','数学',90,2);insert into dbo.stuscore values ('李四','语文',70,2);insert into dbo.stuscore values ('李四','英语',80,2);

题目是：

1. 计算每个人的总成绩并排名(要求显示字段: 学号，姓名，总成绩)

2. 列出各门课程成绩最好的学生(要求显示字段: 学号，姓名,科目，成绩)

参考答案：

SELECT  stuid,  NAME,  sum( score ) FROM  dbo.stuscore GROUP BY  stuid,NAME ORDER BY  sum( score ) DESC;

SELECT  a.stuid,  a.NAME,  a.SUBJECT,  a.score FROM  dbo.stuscore a  INNER JOIN ( SELECT SUBJECT, max( score ) AS max_s FROM dbo.stuscore GROUP BY SUBJECT ) b ON a.SUBJECT = b.SUBJECT   AND a.score = b.max_s;