sql关于求成绩的面试题

table如下，共有5个科目，所有的题目都必须用一条语句处理：
+---------+-------------+------+
| name    |lesson      |mark  |
+---------+-------------+------+
| John    | Math        | 60   |
| Mike    | Eng         | 70   |
| Mark    | History     | 80   |
+---------+-------------+------+

1.有一科不及格的学生名单

2.不及格科目超过2门的学生名单

3.所有科目都不及格的学生名单

4.总分前三的所有学生名单（包括并列）

5.各科成绩最高的所有学生名单（包括并列）

答案--分析 --脚本

create table te (
name char(20) ,
lesson char(20),
mark float
)


insert into te values('john','Math',60);
insert into te values('john','Eng',50);
insert into te values('john','HIstory',56);

insert into te values('Mike','Eng',51);
insert into te values('Mike','Math',59);
insert into te values('Mike','HIstory',55);

insert into te values('Mark','Eng',71);
insert into te values('Mark','Math',89);
insert into te values('Mark','HIstory',95);

insert into te values('mm','Eng',61);
insert into te values('mm','Math',79);
insert into te values('mm','HIstory',85);

insert into te values('f','Eng',51);
insert into te values('f','Math',69);
insert into te values('f','HIstory',95);
select *from te;
--1.有一科不及格的学生名单
--先求出 < 60的，然后分组 统计 在having
select name as c from te where mark <60 group by name having count(*)=1

--2.不及格科目超过2门的学生名单
select name from te where mark <60 group by name having count(*) =2;

--3.所有科目都不及格的学生名单
select name, count(lesson) from te where mark < 60
group by name
having count(lesson) =
(select count(distinct lesson) lesson from te)
--4.总分前三的所有学生名单（包括并列）
SQLServer:

select top 3 name ,sum(isnull(mark,0)) as s_sum from te group by name order by s_sum desc ;

MYSql:

select  name ,sum(ifnull(mark,0)) as s_sum from te group by name order by s_sum desc limit 0,3 ;

select *from te;
--5.各科成绩最高的所有学生名单（包括并列）
--先使用分组求出每科的 Max
--然后join on 就ok 了
select t.name,t.lesson,t.mark from te t join (
select lesson, max(isnull(mark,0)) l_max from te group by lesson) tl on t.mark = tl.l_max;

 注意：处理空值 sqlserver：isnull 

mysql ：ifnull

oracle ：nvl

评论

2 楼 mamaoyuan625 2015-09-05  

lucane 写道

你好
--4.总分前三的所有学生名单（包括并列）
这种实现是不是有问题啊，并列的可能产生超过3条记录

你试一下就知道结果了
Mark Math 89
Mark HIstory 95
mm Math 79
f HIstory 95
mK HIstory 95
mK Eng 79

1 楼 lucane 2011-09-01  

你好
--4.总分前三的所有学生名单（包括并列）
这种实现是不是有问题啊，并列的可能产生超过3条记录