常见SQL题

最新推荐文章于 2024-02-01 16:35:25 发布

原创最新推荐文章于 2024-02-01 16:35:25 发布 · 771 阅读

1 ·

CC 4.0 BY-SA版权

数据库面试题专栏收录该内容

19 篇文章

订阅专栏

本文通过多个实例，详细介绍了如何使用SQL解决实际问题，包括查询特定条件的数据、计算平均值、求解排名等，适用于初学者及有一定基础的读者。

文章目录

1.在a表不在b表
2. 求平均成绩大于60的学生
3. 分组求和TOPN
4.分组求及格率
5.GROUP BY求最高分
6. 求平均收入大于某值的公司
7. 学生成绩表+课程表经典题
8. 求等级排名

1.在a表不在b表

有2张表s1和s2，每张表都只有一列(列名：id)，现在想抽取仅存在于t1的id

思路：
使用左连接，左边会全部显示，而右边关联不上的则会为空，以此来判断仅在s1表中存在。

注意：
1）使用左连接的时候，当右边没有匹配值的时候会自动赋NULL
2）在判断是否为NULL的时候用IS，而不是用=
3）这里模拟的是表中不止有一列，所以需要用临时表。如果只有一列就没必要用临时表。所以在写的过程中尽量少的使用临时表。

答：

SELECT s1.id
FROM(
	SELECT id FROM school.student
) s1
LEFT OUTER JOIN
(
	SELECT id FROM school.student1
) s2
on s1.id = s2.id
WHERE s2.id IS NULL;

2. 求平均成绩大于60的学生

注意：
1）where，group by，having，order by是有顺序的，这个顺序不能更改
2) 使用嵌套语句的时候，外层要指定查询的是哪两个表中的数据

方法一：使用嵌套语句

SELECT s.name, s.avg_socre
FROM (
	SELECT name,AVG(score) avg_socre
	FROM school.student
	GROUP BY name 
) s
WHERE s.avg_socre> 60;

方法二：使用having

having可以看做分组筛选

SELECT name, AVG(score) avg_socre
FROM school.student 
GROUP BY name
HAVING avg_socre > 60

错误的使用：
这就是初学者容易犯的错误，觉得分组之后直接用where就可以进行筛选，其实这是不行的

SELECT name, AVG(score) avg_socre
FROM school.student 
GROUP BY name
WHERE avg_socre > 60

3. 分组求和TOPN

即求每个学科的前N名

（其实这是一道HIVESQL题，如果使用HiveSQL可以很简单的写出来）

解法1： 暴力解法，求每科的前N个，然后进行uoion all

(SELECT * FROM student WHERE course = '计算机网络' ORDER BY score DESC LIMIT 3)
UNION all 
(SELECT * FROM student  WHERE course = '数据结构' ORDER BY score DESC LIMIT 3)
·······

解法2：

使用a表和b表，查询a表中某一行成绩在b表中大于该行成绩（相同课程）的个数。如果小于3，则代表该行成绩属于前三。

SELECT *
FROM student a 
WHERE (
	SELECT COUNT(*)
	FROM student b
	WHERE a.course = b.course AND a.score < b.score 
) < 3
ORDER BY a.course, a.score DESC;

解法3：HiveSQL

正在学习hive，还没有学到这里，先做记录，后面再补

select  * from (
    select *, ROW_NUMBER() over(partition by course order by score desc) rowNum
    from scores 
) as s where s.rowNum<=3

4.分组求及格率

表t1有3列：id, sex, score，3列均为int型，id表示员工工号，
gender表示性别（0女1男)，score代表员工某次考试的成绩(0-100之间)，
若成绩大于等于60为及格，请使用sql语句查询不同性别员工的及格率

解法1：

使用两个表：
表1：查每个性别及格的人数
表2：查每个性别的总人数
联表查询，查及格率

SELECT s1.sex, s1.count1/s2.count2 res
FROM 
(
	SELECT sex,COUNT(*) count1
	FROM student 
	WHERE score > 60
	GROUP BY sex
) s1
LEFT OUTER JOIN 
(
	SELECT sex,COUNT(*) count2
	FROM student 
	GROUP BY sex
) s2
ON s1.sex = s2.sex

解法2：

使用sum （case when），当分数大于60就加1，否则加0；最后除以总数。使用group by分组。

PS：讲实话，我真觉得面试现场想，想不出来这种写法。

select sex,
sum(case when score>=60 then 1 else 0 end) / count(*)
FROM student 
group by sex

5.GROUP BY求最高分

例如下表：成绩表，求每位学生多门课程中的最高分（返回name，course，score）
在这里插入图片描述
这是一道经典的MYSQL GROUP BY 的面试题，为什么这么说呢？因为往往会有人这么写：

SELECT name, course, MAX(score)
FROM student
GROUP BY name;

往往觉得使用MAX求最大分数，再按照名字group by就好了。实际上这样写是报错的，因为GROUP BY的使用是有规范的，查询的字段要不然在聚合函数里，要不然就在group by指定的字段中。不能出现上述的这种情况，course在查询字段里，但是不在聚合函数中也不在group by的字段中。

但是如果这么写就是可以的，但是不符合要求：查询字段不够

SELECT name, MAX(score)
FROM student
GROUP BY name;

正确写法：

解法一：相关子查询

s2表求分数的最大值，s1进行name匹配。

这里要注意的是，s2的确没有查询name字段，但是不代表没有name字段

SELECT name, course, score
FROM student s1 
WHERE s1.score = 
(
SELECT MAX(score)
FROM student s2
WHERE s1.name = s2.name 
)

解法二：非相关子查询

s1表和s2表进行左连接，对s1表中的每一行都去s2中找相同名字的，比他大的，如果找不到就代表s1这里是最大的。

SELECT s1.name, s1.course, s1.score
FROM student s1
LEFT OUTER JOIN
student s2
ON s1.name = s2.name AND s1.score < s2.score 
WHERE s2.name IS NULL

6. 求平均收入大于某值的公司

一张收入表：公司id，收入profit
要求：平均收入大于1000的公司

注意不可以使用where直接对平均收入进行筛选，where后面不能跟聚合函数。后面会讲案例

**解法一：**使用AVG函数求平均收入，使用group by分组，使用having进行分组筛选

SELECT id, AVG(profit) as avg_profit
FROM company_profit
GROUP BY id
HAVING avg_profit > 1000

只查id的情况

SELECT id
FROM company_profit
GROUP BY id
HAVING AVG(profit) > 1000

错误写法：经典错误，很容易犯

之前刚学习了where必须写在group by前面，where的执行顺序也在group by前面，因此where后面不能跟聚合函数。要想进行分组筛选，就使用having或者嵌套select语句。

SELECT id, AVG(profit) as avg_profit
FROM company_profit
WHERE avg_profit > 1000
GROUP BY id

7. 学生成绩表+课程表经典题

成绩表：
在这里插入图片描述
课程表：

1）如果学号的前两位表示年纪，要查找19级男生的姓名，请写出相应SQL语句。

解法一：like模糊查询，因为这不是学生表而是成绩表，因此学号会重复，要使用DISTINCT去重。

SELECT DISTINCT(sno), name, sex
FROM score_table
WHERE sno LIKE "19%" AND sex = 1

解法二：左匹配

SELECT DISTINCT(sno), name, sex
FROM score_table
WHERE LEFT(sno,2) = '19' AND sex = 1

2）要查找所有需要补考（小于60分）的学生姓名和课程的名字和成绩，请写出相应SQL语句。

很简单的关联查询

SELECT s.sno,s.name,c.co_name, s.score
FROM course_table c, score_table s
WHERE s.courseid = c.id AND s.score < 60

3）找出所有学生都不及格的课程及其平均分

先找出及格的课程，然后not in找出不及格的课程，然后AVG聚合函数求平均分，使用GROUP BY分组

SELECT a.courseid, b.co_name ,AVG(score)
FROM score_table a, course_table b
WHERE a.courseid NOT IN 
(
SELECT DISTINCT(courseid)
FROM score_table
WHERE score > 60
) AND a.courseid = b.id
GROUP BY a.courseid

4）找出所有课程分数都在80以上的学生

思路：找出分数在80一下的学生，用not in即可

SELECT sno, name
FROM score_table 
WHERE sno NOT IN
(
SELECT sno
FROM score_table
WHERE score < 80
)

8. 求等级排名

题目：求身高排序
在这里插入图片描述
题解：

先用a和b做笛卡尔积，然后求出b中大于等于a成绩的个数，这个数量就是a表中的排名

SELECT a.name, count(DISTINCT(b.high)) rank
FROM hight_table a, hight_table b
WHERE a.high <= b.high
GROUP BY name
ORDER BY rank