近期从推文上看到一篇面试题上考察的sql编写能力，感觉好多聚合函数的知识还需要巩固一下，特意摘出几个经典的语句在这里给大家分享一下，希望对大家有帮助。
建表及插入语句如下：
create table student( -- 创建学生表
sid varchar(20),
sname varchar(20),
sage int
);
-- 学生表数据简单插入
insert into student values('s01','唐妍',18);
insert into student values('s02','胡哥',19);
insert into student values('s03','刘诗',18);
insert into student values('s04','杨蜜',17);
insert into student values('s05','何建华',19);
insert into student values('s06','彭鱼彦',19);
create table teacher( -- 教师表
tid varchar(20),
tname varchar(20)
);
insert into teacher values('t01','高磊');
insert into teacher values('t02','韩争');
insert into teacher values('t03','李征辉');
insert into teacher values('t04','何倩文');
insert into teacher values('t05','刘宏');
insert into teacher values('t06','彭伟');
create table lessons( -- 课程表
cid varchar(20),
cname varchar(20),
cteacher varchar(20)
);
insert into lessons values('c01','高等数学','高磊');
insert into lessons values('c02','英语','韩争');
insert into lessons values('c03','计算机基础','李征辉');
insert into lessons values('c04','汇编语言','何倩文');
insert into lessons values('c05','电子商务','李征辉');
create table score( -- 分数表
sid varchar(20),
cid varchar(20),
cscore int
);
insert into score values('s01','c01',58);
insert into score values('s01','c02',59);
insert into score values('s01','c03',55);
insert into score values('s02','c02',83);
insert into score values('s02','c02',79);
insert into score values('s02','c05',77);
insert into score values('s03','c01',55);
insert into score values('s03','c03',81);
insert into score values('s03','c04',73);
insert into score values('s04','c01',67);
insert into score values('s04','c02',78);
insert into score values('s04','c03',82);
insert into score values('s04','c05',80);
insert into score values('s05','c01',61);
insert into score values('s04','c04',78);
-- 1 查询选修“李征辉”老师所授课程的学生中，成绩最高的学生姓名及其成绩
select s.sname, max(sc.Cscore) from student s
left join score sc on s.sid=sc.sid where sc.cid in (select cid from lessons where Cteacher = '李征辉');
-- 2 查询不同老师所教不同课程平均分从高到低显示
select c.cteacher,c.cname, avg(sc.cscore) from score sc
left join lessons c on sc.cid=c.cid
group by sc.cid
order by avg(sc.cscore) desc;
-- 3 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select s.sid, s.sname, avg(sc.cscore) from student s left join score sc on s.sid = sc.sid
where s.sid in(select sid from score sc where cscore < 60 group by sid having count(sid) > 2);
-- 4 查询所有同学的学号、姓名、选课数、总成绩
select c.sid,s.sname,count(c.cid) ,sum(cscore) from score c
left join student s on c.sid=s.sid
group by c.sid;
-- 5查询没学过“何倩文”老师课的同学的学号、姓名；
select sid, sname from student
where sid not in(select sid from score
where cid =(select cid from lessons where cteacher = '何倩文')
);
--不积跬步无以至千里！