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
相关资源：敏捷开发V1.0.pptx
