面试题目之SQL面试题目之可以举一反三的SQL问题

面试题目之SQL面试题目可以举一反三的SQL问题

经典的sql题目可以举一反三

表结构：

• 学生表student(id,name)
• 课程表course(id,name)
• 学生成绩表student_course(sid,cid,score)

编写创建表的sql

create table student( id int unsigned primary key auto_increment, 
name char(10) not null ); 

insert into student(name) values('张三'),('李四'); 
 
create table course( id int unsigned primary key auto_increment, name char(20) not null ); 

insert into course(name) values('语文'),('数学');  

create table student_course( sid int unsigned, cid int unsigned, score int unsigned not null, 
foreign key (sid) references student(id), foreign key (cid) references course(id), primary key(sid, cid) ); 

insert into student_course values(1,1,80),(1,2,90),(2,1,90),(2,2,70);

问题

查询student表中重名的学生，结果包含id和name，按name,id升序

select id,name
from student
where name in (
select name from student group by name having(count(*) > 1)
) order by name;

我们经常需要查询某一列重复的行，一般通过group by(有重复的列)然后取count>1的值。 关系型数据库有他的局限性， 有些看似简单的查询写出来的sql很复杂，而且效率也会很低。

在student_course表中查询平均分不及格的学生，列出学生id和平均分

select sid,avg(score) as avg_score
from student_course
group by sid having(avg_score<60);

group by和having是最常考的 where子句中不能用聚集函数作为条件表达式，但是having短语可以，where和having的区别在于对用对象不同，where作用于记录，having作用于组。

在student_course表中查询每门课成绩都不低于80的学生id

select distinct sid
from student_course
where sid not in (
select sid from student_course
where score < 80);

用到反向思想，其实就是数理逻辑中的∀x:P和¬∃x:¬P是等价的。

查询每个学生的总成绩，结果列出学生姓名和总成绩 如果使用下面的sql会过滤掉没有成绩的人

select name,sum(score) total
from student,student_course
where student.id=student_course.sid
group by sid;

更保险的做法应该是使用 左外连接

select name,sum(score)
from student left join student_course
on student.id=student_course.sid
group by sid;

总成绩最高的学生，结果列出学生id和总成绩 下面的sql效率很低，因为要重复计算所有的总成绩。

select sid,sum(score) as sum_score
from student_course group by sid having sum_score>=all
(select sum(score) from student_course group by sid);

因为order by中可以使用聚集函数，最简单的方法是：

select sid,sum(score) as sum_score
from student_course group by sid
order by sum_score desc limit 1;

同理可以查总成绩的前三名。