积累一些常见的写SQL的题

引言

---

前几天面了美团的一面，在数据库方面本来以为会像阿里一样只问一些基础知识，没想到面试官居然让我写SQL，而且写的都是一些比较复杂的SQL，我都好长时间没写过复杂的SQL了，所以写得相当糟糕，所以在这篇博客中积累几题经典的题。

考察NOT IN子句

---

例题

有如下的student表，里面记录学生不同科目的成绩：

name	subject	score
Bob	数学	90
Bob	语文	79
Lucy	数学	81
Lucy	语文	80

现在要查询出所有成绩都大于80分的所有学生的姓名。

解答

使用NOT IN子句将存在小于80分成绩的学生筛选掉就可以了

SELECT DISTINCT name FROM student 
  WHERE name NOT IN (
    SELECT name FROM student WHERE score < 80
  );

考察LIMIT子句

---

LIMIT子句的格式

LIMIT [offsets,]rows

offsets表示偏移量是多少（偏移量从0开始），rows表示要查询多少条记录。其中offsets如果不写的话默认就是从0开始。

例题

有如下的student表，里面记录了学生的成绩：

name	score
Bob	90
Tom	90
Lucy	86
Work	86
Abel	77

现在要查询出所有并列排名第二的学生姓名。

解答

题目中要求考虑并列排名，所以我们先用LIMIT子句把排名第二的分数查出来，如下：

SELECT DISTINCT score FROM student 
  order by score desc LIMIT 1,1;

然后再从student表中查出分数等于这个排名第二的分数的学生，将答案写成一条完整的SQL语句如下：

SELECT * FROM student 
  WHERE score=
    (SELECT DISTINCT score FROM student 
      order by score desc LIMIT 1,1);

考察聚集函数

---

例题

假设有如下的student表：

name	class	age
Bob	1	20
Tom	1	21
Lucy	2	22
Work	1	23
Abel	3	25
…	…	…

查询出人数在60以上的各个班级的平均年龄

解答

通过HAVING子句筛选出人数在60以上的班级，然后使用AVG聚集函数求出平均年龄。

SELECT class, AVG(age) FROM student 
  GROUP BY class HAVING COUNT(*)>60;

考察ANY与ALL谓词

---

ANY与ALL谓词

谓词	含义
>ANY	大于子查询结果中的某个值
>ALL	大于子查询结果中的所有值
<ANY	小于子查询中的某个值
<ALL	小于子查询结果中的所有值

例题

假设有如下的student表：

name	class	age
Bob	1	20
Tom	1	21
Lucy	2	22
Work	1	23
Abel	3	25

查询1班以外比一班年龄最小的学生还要小的学生

解答

使用ALL子句可以很容易的解决

SELECT * FROM student WHERE class<>1 AND 
  age<ALL(
    SELECT age from student WHERE class=1
  );

考察NOT EXISTS子句

---

NOT EXISTS子句常用于带有”至少”语义的查询问题，在SQL中难度是比较大的

例题1

假设有三张表，分别是student（学生）,course（课程）,sc（学生选课），表格如下：

• student表：

id	name
0	Bob
2	Tom
3	Lucy
4	Work
5	Abel
…	…

• course表：

id	course
0	数据库
2	计算机网络
3	数据结构
4	计算机组成原理
5	离散数学
…	…

• sc表（是一个连接表，代表学生选课，s_id是学生id，c_id是课程id）：

s_id	c_id
0	3
0	0
1	4
2	5
3	2
…	…

问题1：查询选修了全部课程的学生姓名
问题2：查询至少选修了id为1的学生选修的全部课程的学生id

问题1解答

将问题的语义转化为“查询这样的学生，不存在任意一门他没选的课”：

SELECT name FROM student WHERE NOT EXISTS
  (SELECT * FROM course WHERE NOT EXISTS
    (SELECT * FROM sc WHERE sc.s_id=student.id 
      AND sc.c_id=course.id)
  );

问题2解答

将语义转化为“查询这样的学生，不存在任意一门课程，id为1的学生选的但是他没选”：

SELECT s_id FROM sc scx WHERE NOT EXISTS
  (SELECT sc FROM sc scy WHERE scy.id=1 AND
    NOT EXISTS
    (SELECT * FROM sc scz WHERE 
      scx.s_id=scz.s_id AND scy.c_id=scz.c_id) 
  );