SQL每日一练（三）

最新推荐文章于 2024-04-18 15:01:44 发布

恋恋风尘hhh

最新推荐文章于 2024-04-18 15:01:44 发布

阅读量535

点赞数 2

分类专栏： SQL练习题 SQL知识文章标签： sql 数据库

本文链接：https://blog.csdn.net/weixin_42384784/article/details/105318027

版权

SQL知识同时被 2 个专栏收录

12 篇文章 1 订阅

订阅专栏

SQL练习题

3 篇文章 0 订阅

订阅专栏

每一章包含10道题，持续更新中…

1、有如下test 表，写出查询语句，得到如下结果。

表test：
在这里插入图片描述

对于上述例子数据，结果为：
在这里插入图片描述

解题思路：

（1）按照 id 进行分组求和，得到 B

（2）原表和（1）进行联结，即可得到答案。

参考实现：

SELECT a.id,
		a.A,
		b.B
FROM test a
JOIN
(SELECT id,
		SUM(A) as B
FROM test
GROUP BY id) b
ON a.id = b.id

2、有如下test 表，有ABC三列和几组数据，写出查询语句，得到如下结果。

表test：
在这里插入图片描述

对于上述例子数据，结果为：
在这里插入图片描述

解题思路：

（1）观察结果，可以发现B列为相同A列对应的B值相加，C列分两种情况：如果相同A列对应的C值相同，则取该相同的C值，如果相同A列对应的C值不相同，则取’1’.

（2）对原表按照 A,C进行分组聚合，如果分组之后A列的记录数为1，则说明C值不相同，则取“1”值，否则取C

（3）根据（2）的结果，按照 A, C列进行分组聚合，计算B的和

参考实现：

SELECT a.A,
		SUM(B) as B,
		C
FROM
(SELECT A,
		SUM(B) as B,
		CASE COUNT(A)
		WHEN 1 THEN '1'
		ELSE C
		END C
FROM test
GROUP BY A, C) a
GROUP BY a.A, a.C

3、有如下两张表， “雇员表“中记录了员工的信息，“薪水表“中记录了对应员工发放的薪水。两表通过“雇员编号”关联。查找当前所有员工入职以来的薪水涨幅，给出雇员编号以及其对应的薪水涨幅，并按照薪水涨幅进行升序。

（注：薪水表中结束日期为2004-01-01的才是当前员工，否则是已离职员工）

雇员表：

在这里插入图片描述

薪水表：

在这里插入图片描述

对于上述例子数据，结果为：

在这里插入图片描述

解题思路：

（1）连接雇员表和薪水表，求出每个雇员的入职薪水（薪水的起始日期等于雇用日期即为入职薪水）

（2）求出所有在职员工的当前薪水（结束日期为"2004-01-01"即为当前员工）

（3）连接（1）和（2）的结果，并以（2）的结果为基准表（（2）中结果不包含已离职员工），求出当前薪水和入职薪水的差，即为薪水涨幅。

（4）将（3）的结果按照薪水涨幅进行升序排序，即可得出结果。

参考实现：

SELECT c.`雇员编号`,
      (d.`当前薪水` - c.`入职薪水`) as `薪水涨幅` 
FROM
(SELECT a.`雇员编号`,
       b.`薪水` as `入职薪水`
FROM `雇员表` a
LEFT JOIN `薪水表` b
ON a.`雇员编号`=b.`雇员编号` AND a.`雇用日期`=b.`起始日期`) c
RIGHT JOIN
(SELECT `雇员编号`,
		`薪水` as `当前薪水`
FROM `薪水表`
WHERE `结束日期` = '2004-01-01') d
ON c.`雇员编号`=d.`雇员编号`
ORDER BY `薪水涨幅`

4、有如下用户登录表，记录每个用户的id，姓名，邮箱地址和用户登录时间，求出每个人的登录时间排名和登录天数排名。

“登录时间排名”这一列：按时间给出每个人的登录次数，登录时间最早的为1，之后的分别是2，3，4等。

“登录天数排名”这一列：按天给出每个人的登录次数，同一天多次登录认为是同一次，最早标记为1，之后以此类推。

用户登录表：

在这里插入图片描述

对于上述例子，有如下结果：

在这里插入图片描述

解题思路：

（1）使用ROW_NUMBER() 窗口函数，以姓名为分组，登录时间进行排序，求出每个人每次登录时间的排名。

（2）使用DENSE_RANK()窗口函数，以姓名为分组，登录时间的日期进行排序，求出每个人每天登录的排名。

（3）注意求登录天数排名时按照日期来进行对比，并且使用DENSE_RANK()进行排序。

参考实现：

SELECT `姓名`,
		`登录时间`,
        ROW_NUMBER() OVER(PARTITION BY `姓名` ORDER BY `登录时间` ) as `登录时间排名`,
		DENSE_RANK() OVER(PARTITION BY `姓名` ORDER BY DATE(`登录时间`)) as `登录天数排名`
FROM `用户登录表`

5、有如下两张表，“学生表”里记录了学生的学号、入学时间等信息。“成绩表”里是学生选课成绩的信息，两个表中的学号一一对应。

学生表：

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

在这里插入图片描述

现在需要求出以下两个问题：

筛选出2017年入学的“计算机”专业年龄最小的3位同学名单（姓名、年龄）

在这里插入图片描述
解题思路：

（1）筛选出2017年入学和专业为计算机的学生

（2）将筛选出来的学生按照年龄进行排序，并且取前三位。

参考实现：

SELECT `姓名`,
		`年龄`
FROM `学生表`
WHERE YEAR(`入学时间`)='2017' AND `专业`='计算机'
ORDER BY `年龄` LIMIT 3

统计每个班同学各科成绩平均分大于80分的人数和人数占比。

在这里插入图片描述

解题思路：

（1）成绩表按照学号进行分组，求出每个学生的平均成绩，并且将结果封装起来。

（2）连接（1）的结果和学生表，以班级为分组，统计每个班平均分大于80分的人数（使用case when，大于80分记为1，否则记为0，使用sum进行统计）。

（3）在（2）的基础上，用人数除以每个班的总人数，求出人数占比。

参考实现：

WITH cte AS 
(SELECT `学号`,
		AVG(`分数`) as `平均分`
FROM `成绩表`
GROUP BY `学号`) 

SELECT b.`班级`,
      --也可以使用IF来实现
      --SUM(IF(a.`平均分`>80,1,0)) as `人数`,
      SUM(CASE WHEN a.`平均分`> 80 THEN 1
          ELSE 0 END) as `人数`,
      SUM(CASE WHEN a.`平均分`> 80 THEN 1
          ELSE 0 END)/COUNT(b.`学号`) as `人数占比`
FROM cte a
JOIN `学生表` b
ON a.`学号`=b.`学号`
GROUP BY b.`班级`

6、有如下访问次数表，列名包括用户编号、用户类型和访问量。要求在剔除访问量前20%的用户后，每类用户的平均访问量。

访问次数表：
在这里插入图片描述

对于以上例子，有如下结果：

在这里插入图片描述

解题思路：

（1）求解前百分之几或几分之几的问题，可以考虑使用切分窗口函数 ntile() over()，本题求前20%的访问量，则将数据分成5份，按照访问量进行排序，得出每个访问量的等级分布。

（2）在（1）的结果上剔除掉等级为1（前20%）的数据，然后根据用户类型进行分组聚合。

（3）求出每类用户的平均访问量，即可得出答案。

参考实现：

SELECT `用户类型`,
       AVG(`访问量`) as `平均访问量`
FROM
(SELECT *,
		ntile(5) over(ORDER BY `访问量` DESC) as `访问量等级`
FROM `访问次数表`) a
WHERE a.`访问量等级` != 1
GROUP BY `用户类型`

7、有如下成绩表，列名包括学号、成绩号和成绩，要求找出每门课程成绩最差的学生信息。

成绩表：

在这里插入图片描述
对于以上例子，有如下结果：

在这里插入图片描述

解题思路：

（1）使用 min() over()窗口函数，以课程号为分组，求出每门课的最差成绩。

（2）找出成绩等于最差成绩的学生信息

参考实现：

SELECT `学号`,
		`课程号`,
		`成绩`
FROM
(SELECT *,
		MIN(`成绩`) OVER(PARTITION BY `课程号`) as `最差成绩`
FROM `成绩表`) a
WHERE a.`成绩` = a.`最差成绩`

8、有如下sales表，希望转换成new_sales表的形式，请用SQL语句实现。

sales表：
在这里插入图片描述

new_sales表：
在这里插入图片描述

解题思路：

（1）这是一个典型的行转列问题，以year为分组，先用case when 语句得到每个month的amout值，然后对amount值进行求和即可。

（2）使用ROUND()函数将结果保留1位小数

参考实现：

SELECT `year`,
	SUM(CASE `month` WHEN 1 THEN ROUND(amount,1) ELSE 0 END) as `m1`,
	SUM(CASE `month` WHEN 2 THEN ROUND(amount,1) ELSE 0 END) as `m2`,
	SUM(CASE `month` WHEN 3 THEN ROUND(amount,1) ELSE 0 END) as `m3`,
	SUM(CASE `month` WHEN 4 THEN ROUND(amount,1) ELSE 0 END) as `m4`
FROM sales
GROUP BY `year`

9、有如下student表，请用SQL语句查询表中每个学生连续三个月（含）以上degree均为A的记录。

student表：

在这里插入图片描述

对于以上例子，有如下结果：

在这里插入图片描述

解题思路：

（1）使用IF函数，判断degree的值，值为A计为1，否则计为0。

（2）使用移动窗口函数，计算最近三行的degree总数，总数为3则说明连续三行出现A。

（3）要取出连续3个月为A的记录，需要分三种情况计算degree总数（本行和前两行，本行和前后各一行，本行和后一行），筛选三种情况中degree总数为3的全部记录即可。

参考实现：

SELECT a.name, a.month, a.degree
FROM
(SELECT *,
     SUM(IF(degree='A', 1, 0)) OVER(PARTITION BY `name` ORDER BY `month`
				ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as score1,
	SUM(IF(degree='A', 1, 0)) OVER(PARTITION BY `name` ORDER BY `month`
				ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as score2,
	SUM(IF(degree='A', 1, 0)) OVER(PARTITION BY `name` ORDER BY `month`
				ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) as score3
FROM student) as a
WHERE a.score1=3 OR a.score2=3 OR a.score3=3