积累一些常见的写SQL的题

﹏ヽ暗。殇╰゛Y 2022-05-29 04:37 212阅读 0赞

# 引言 #

--------------------

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

# 考察NOT IN子句 #

--------------------

## 例题 ##

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

<table> 
 <thead> 
  <tr> 
   <th>name</th> 
   <th align="center">subject</th> 
   <th align="right">score</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Bob</td> 
   <td align="center">数学</td> 
   <td align="right">90</td> 
  </tr> 
  <tr> 
   <td>Bob</td> 
   <td align="center">语文</td> 
   <td align="right">79</td> 
  </tr> 
  <tr> 
   <td>Lucy</td> 
   <td align="center">数学</td> 
   <td align="right">81</td> 
  </tr> 
  <tr> 
   <td>Lucy</td> 
   <td align="center">语文</td> 
   <td align="right">80</td> 
  </tr> 
 </tbody> 
</table>

现在要查询出所有成绩都大于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表，里面记录了学生的成绩：

<table> 
 <thead> 
  <tr> 
   <th>name</th> 
   <th>score</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Bob</td> 
   <td>90</td> 
  </tr> 
  <tr> 
   <td>Tom</td> 
   <td>90</td> 
  </tr> 
  <tr> 
   <td>Lucy</td> 
   <td>86</td> 
  </tr> 
  <tr> 
   <td>Work</td> 
   <td>86</td> 
  </tr> 
  <tr> 
   <td>Abel</td> 
   <td>77</td> 
  </tr> 
 </tbody> 
</table>

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

## 解答 ##

题目中要求考虑并列排名，所以我们先用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表：

<table> 
 <thead> 
  <tr> 
   <th>name</th> 
   <th>class</th> 
   <th>age</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Bob</td> 
   <td>1</td> 
   <td>20</td> 
  </tr> 
  <tr> 
   <td>Tom</td> 
   <td>1</td> 
   <td>21</td> 
  </tr> 
  <tr> 
   <td>Lucy</td> 
   <td>2</td> 
   <td>22</td> 
  </tr> 
  <tr> 
   <td>Work</td> 
   <td>1</td> 
   <td>23</td> 
  </tr> 
  <tr> 
   <td>Abel</td> 
   <td>3</td> 
   <td>25</td> 
  </tr> 
  <tr> 
   <td>…</td> 
   <td>…</td> 
   <td>…</td> 
  </tr> 
 </tbody> 
</table>

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

## 解答 ##

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

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

# 考察ANY与ALL谓词 #

--------------------

## ANY与ALL谓词 ##

<table> 
 <thead> 
  <tr> 
   <th>谓词</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code>&gt;ANY</code></td> 
   <td>大于子查询结果中的某个值</td> 
  </tr> 
  <tr> 
   <td><code>&gt;ALL</code></td> 
   <td>大于子查询结果中的所有值</td> 
  </tr> 
  <tr> 
   <td><code>&lt;ANY</code></td> 
   <td>小于子查询中的某个值</td> 
  </tr> 
  <tr> 
   <td><code>&lt;ALL</code></td> 
   <td>小于子查询结果中的所有值</td> 
  </tr> 
 </tbody> 
</table>

## 例题 ##

假设有如下的student表：

查询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表：

*  course表：

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>course</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>0</td> 
   <td>数据库</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>计算机网络</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>数据结构</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>计算机组成原理</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>离散数学</td> 
  </tr> 
  <tr> 
   <td>…</td> 
   <td>…</td> 
  </tr> 
 </tbody> 
</table>

*  sc表（是一个连接表，代表学生选课，s\_id是学生id，c\_id是课程id）：

问题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) );