学员出去面试遇到的真题,分享给大家
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);
题目是:
-
计算每个人的总成绩并排名(要求显示字段: 学号,姓名,总成绩)
-
列出各门课程成绩最好的学生(要求显示字段: 学号,姓名,科目,成绩)
参考答案:
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;