SQL笔试题总结

已于 2023-03-10 09:58:39 修改
阅读量2.7k 收藏 40
点赞数 18
文章标签： sql 面试 数据库
于 2023-01-01 12:17:11 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_56952363/article/details/128510444
版权

文章目录


  

  

  

  

前言

  

本篇文章将总结博主在笔试或面试过程中碰到的比较有难度或经典的sql题目。笔试考察的大都不是很难，不像工作中那样好几个嵌套子查询，很多个表关联…可能行数很少，但绝对得多想。如果之前没碰见过，第一次做肯定会漏洞百出。会持续更新的~

  
  
  

一、列转行

  

题目：将表Student

namesubjectscore
张三语文78.0
张三数学88.0
张三英语98.0
李四语文89.0
李四数学76.0
李四英语90.0
王五语文66.0

转化为下面的形式展示

name语文数学英语总分
张三788898264
李四897690255
王五990066

  

先放答案

SELECT `name`,语文,数学,英语,总分
FROM(
	SELECT
	`name`,
	SUM(IF(SUBJECT = '语文',score,0)) AS 语文,
	SUM(IF(SUBJECT = '数学',score,0)) AS 数学,
	SUM(IF(SUBJECT = '英语',score,0)) AS 英语
	FROM student
	GROUP BY `name`)t1
LEFT JOIN(
	SELECT `name`,SUM(score) AS 总分
	FROM student
	GROUP BY `name`)t2
USING(`name`);

  

逐步剖析

1、先看总分，计算很简单，就是按名字group by 之后 sum(分数) 即可

SELECT `name`,SUM(score) AS 总分
FROM student
GROUP BY `name`
name总分
张三264
李四255
王五66

得出这样的结果
  
2、本题精髓就是怎么把 subject 这一列数据转化成一行列名。可以拿 subject 列中的数据做 if 判断的条件，创建一张只有 name列 不变，后几列是 语数英 的新表

SELECT
`name`,
IF(SUBJECT = '语文',score,0) AS 语文,
IF(SUBJECT = '数学',score,0) AS 数学,
IF(SUBJECT = '英语',score,0) AS 英语
FROM student
name语文数学英语
张三78.000
张三088.00
张三0098.0
李四89.000
李四076.00
李四0090.0
王五66.000

  
3、离最终答案不远了，用 group by + sum 就能变成没人一行且语数英三列数据合并

SELECT
`name`,
SUM(IF(SUBJECT = '语文',score,0)) AS 语文,
SUM(IF(SUBJECT = '数学',score,0)) AS 数学,
SUM(IF(SUBJECT = '英语',score,0)) AS 英语
FROM student
GROUP BY `name`
name语文数学英语
张三788898
李四897690
王五9900

这条sql就是本题精髓！！！ 怕直接看看不懂，所以拆开分两步写了。

  
4、最后一步简单，把各科表和总分表关联起来就行，刚才那俩分离的sql写一起

SELECT `name`,语文,数学,英语,总分
FROM(
	SELECT
	`name`,
	SUM(IF(SUBJECT = '语文',score,0)) AS 语文,
	SUM(IF(SUBJECT = '数学',score,0)) AS 数学,
	SUM(IF(SUBJECT = '英语',score,0)) AS 英语
	FROM student
	GROUP BY `name`)t1
LEFT JOIN(
	SELECT `name`,SUM(score) AS 总分
	FROM student
	GROUP BY `name`)t2
USING(`name`);

  
  
  

二、row_number() over() 的使用

  

row_number() over() 在面试中经常和 rank() over()、dense_rank() over() 一起被问，在笔试中也经常需要用到，甚至在工作中也经常见到，反正是相当的重要！
在工作中其实用的是 row_number() over() 的变种，用 @ 。因为公司大都是MySQL5，但窗口函数在MySQL8中才出现，所以工作中都是 row_number 的思想，@的写法。关于@的使用有时间我写写。
  

题目：统计订单交易表（orders）每个商品交易金额最高的那一条数据

item_idtrade_datetrade_amount
A2021/3/11500
A2021/3/92500
A2021/2/221000
B2021/3/53000
B2021/5/95000
B2021/6/22800

item_id=商品代码  trade_date=交易日期  trade_amount=交易金额

  

先放答案

SELECT 
	item_id,
	trade_date,
	trade_amount
FROM(
	SELECT 
		item_id,
		trade_date,
		trade_amount,
		row_number() over(PARTITION BY item_id ORDER BY trade_amount DESC) AS sort
	FROM orders)t
WHERE sort = 1
item_idtrade_datetrade_amount
A2021/3/92500
B2021/5/95000

  

逐步剖析

1、因为要求每个商品的金额最高，所以分组排序必不可少。为什么不能用 group by 然后 max 呢？因为要求的是金额最高的那一条数据，而不是求最高金额，那一行数据的其他信息得保留下来。而group by 后的其他字段不用聚合函数是没法保存的，就丢失了，跟题意不符。

举个栗子吧：

SELECT item_id,MAX(trade_amount)
FROM orders
GROUP BY item_id

在这里插入图片描述
虽然能找到最高金额，但这样就缺字段了

还有这样：

SELECT item_id,trade_date,MAX(trade_amount)
FROM orders
GROUP BY item_id

这样写直接报错，因为 group by 了 item_id 这一列，那么它之外的列如果不用聚合函数直接报错

这样也是不对：

SELECT item_id,trade_date,MAX(trade_amount)
FROM orders
GROUP BY item_id,trade_date

虽然不报错，但是
在这里插入图片描述
只有 item_id 和 trade_date 都相同的才能分为一组，不符合题意

  
所以要用到 row_number() over() 进行分组排序：

SELECT 
	item_id,
	trade_date,
	trade_amount,
	row_number() over(PARTITION BY item_id ORDER BY trade_amount DESC) AS sort
FROM orders
item_idtrade_datetrade_amountsort
A2021/3/925001
A2021/3/115002
A2021/2/2210003
B2021/5/950001
B2021/3/530002
B2021/6/228003

  
2、最后子查询即可

SELECT 
	item_id,
	trade_date,
	trade_amount
FROM(
	SELECT 
		item_id,
		trade_date,
		trade_amount,
		row_number() over(PARTITION BY item_id ORDER BY trade_amount DESC) AS sort
	FROM orders)t
WHERE sort = 1

  
  
  

三、逐行累加

  

题目：还是订单交易表（orders），对商品按照月份累加汇总。比如，一月份显示一月的交易金额，二月份显示一月份+二月份的交易金额

展示结果应该为：

item_idtrade_datetrade_amount
A2021/21000
A2021/35000
B2021/33000
B2021/58000
B2021/68800

  

先放答案

SELECT 
	item_id,
	`month`,
	SUM(trade_amount) over(PARTITION BY item_id ORDER BY `month`) sums
FROM (
	SELECT
		item_id,
		DATE_FORMAT(trade_date,'%Y-%m') AS `month`,
		SUM(trade_amount) trade_amount 
	FROM orders 
	GROUP BY item_id,DATE_FORMAT(trade_date,'%Y-%m')
	ORDER BY item_id,`month`)t
GROUP BY item_id,`month`

  

逐步剖析

1、题目说：按照月份累加汇总。怎么按照月份呢，按月份分组，把相同月份的金额加起来

SELECT
	item_id,
	DATE_FORMAT(trade_date,'%Y-%m') AS `month`,
	SUM(trade_amount) trade_amount 
FROM orders 
GROUP BY item_id,DATE_FORMAT(trade_date,'%Y-%m')
ORDER BY item_id,`month`
item_idtrade_datetrade_amount
A2021/21000
A2021/34000
B2021/33000
B2021/55000
B2021/6800

因为要求不同商品不同月份汇总，所以 group by 后要用两个字段分组。

  
2、到这里已经跟结果很接近了，只要能按照 item_id（商品代码）分组然后累加即可，我首先想到了sum() over() 窗口函数

SELECT 
	item_id,
	`month`,
	SUM(trade_amount) over(PARTITION BY item_id ORDER BY `month`) sums
FROM (
	SELECT
		item_id,
		DATE_FORMAT(trade_date,'%Y-%m') AS `month`,
		SUM(trade_amount) trade_amount 
	FROM orders 
	GROUP BY item_id,DATE_FORMAT(trade_date,'%Y-%m')
	ORDER BY item_id,`month`)t
GROUP BY item_id,`month`

在这里插入图片描述

  
  
  

四、rank() over() 开窗函数的使用（以下三道都是滴滴面试过程中手撕）

  

表：订单表t，（网约车订单明细表，每日增量）

dt（分区格式yyyy-MM-dd）, product_line, order_id,car_id, driver_id,is_finish,is_cancel,start_time,end_time。
其中，is_finish=1代表订单完成。is_cancel=1代表订单取消。product_line：品类（如快车、专车等）。

  

题目：查询近7天内上一单是取消单的，近7天完单量top100的司机。

  

题目解读：上一单 = 最近一单的上一单，最近一单 = 当前单

  

先放答案

select driver_id,count(*) as top
from t
where datediff(current_date(),dt) < 7 and is_finish=1 and driver_id in (
	select driver_id
	from(
		select driver_id,is_cancel,dt,rank() over(PARTITION BY driver_id ORDER BY dt DESC) as sort
		from t
		where datediff(current_date(),dt) < 7
	)t1
	where sort = 2 AND is_cancel = 1
	)
group by driver_id
order by top desc
limit 100

  

逐步剖析

1、先查出来近7天内上一单是取消单的司机

select driver_id,is_cancel,dt,rank() over(PARTITION BY driver_id ORDER BY dt DESC) as sort
from t
where datediff(current_date(),dt) < 7

按司机分组，按时间排序。求出来每个司机所有订单放一起，并按时间降序排序。最后加个where条件限制七天内

结果类似这样：

driver_idis_canceldtsort
10202023-03-061
10212023-03-052
10202023-03-043
10102023-03-061
10102023-03-042
10312023-03-061
10302023-03-042
10302023-03-033

  

在刚才的基础上子查询，即可求出上一单是取消单的司机

select driver_id
from(
	select driver_id,is_cancel,dt,rank() over(PARTITION BY driver_id ORDER BY dt DESC) as sort
	from t
	where datediff(current_date(),dt) < 7
)t1
where sort = 2 AND is_cancel = 1

这块是精髓！

  

2、将刚才查到的符合要求的driver_id写进where条件，再加上时间和完单的限制，最后聚合即可求出完单量top100的司机

select driver_id,count(*) as top
from t
where datediff(current_date(),dt) < 7 and is_finish=1 and driver_id in (
	# 刚才开窗的符合条件的sql
	)
group by driver_id
order by top desc
limit 100

最后就能拼接完整代码了！

  
  
  

五、hive中列转行函数 collect_set 的使用

  

表：表结构和上面那题的一样

  

题目：查询近30天内，司机完单的品类集合、完单天数。产出3个字段：司机id, 近30日完单品类列表，近30日完单天数。

  

函数科普：

  1. collect_set：group by后，将分组中的某列转为一个数组返回，set去重

  2. collect_list：group by后，将分组中的某列转为一个数组返回，list不去重

  

先放答案

select driver_id,collect_set(product_line),count(distinct(dt))
from t
where datediff(current_date(),dt) < 30 and is_finish=1
group by driver_id

  

逐步剖析

1、题目让产出3个字段：司机id，品类列表，完单天数。可以发现，题目可以按照司机id分组，后面两个字段用函数聚合

select driver_id,collect_set(product_line),count(distinct(dt))
from t
group by driver_id

  

2、还有两个条件：近30天内、完单，写在where条件中

where datediff(current_date(),dt) < 30 and is_finish=1

拼接起来即可。

  
  
  

六、sql不光要写出来，也要注意效率——调优

  

表：订单表t1，（订单明细表，每日增量）

dt（分区格式yyyy-MM-dd）, product_line, order_id,car_id, driver_id,is_finish,is_cancel,start_time,end_time。
其中，is_finish=1代表订单完成。is_cancel=1代表订单取消。product_line：品类（如快车、专车、拼车等）。

司机表t2，（每日全量）

dt（分区格式yyyy-MM-dd）, driver_id, work_type。
其中，work_type=1代表全职

  

题目：查询近7天内，各品类中，完单量top100的全职司机。

  

先放答案

SELECT product_line,driver_id,num
FROM(
	SELECT product_line,driver_id,num,rank() over(PARTITION BY product_line ORDER BY num DESC) AS sort
	FROM(
		# 各品类中各全职司机的完单量
		SELECT product_line,driver_id,num
		FROM(
			 SELECT product_line,driver_id,COUNT(*) num
			 FROM t1
			 WHERE DATEDIFF(CURRENT_DATE,dt)<7 AND is_finish=1
			 GROUP BY product_line,driver_id
			)a
	LEFT JOIN
	 	(
		 SELECT *
		 FROM t2
		 WHERE DATEDIFF(CURRENT_DATE,dt)<7 AND work_type=1
		 )b 
	 ON a.driver_id = b.driver_id
	)t1
)t2
WHERE sort <= 100
苍夜月明
关注
  • 18
    点赞
  • 踩
  • 40
    收藏
    觉得还不错? 一键收藏
  • 13
    评论
MySQL数据库笔试试题及答案（全）
02-01
MySQL数据库笔试试题及答案（全），单选题103题，多选题58题，填空题30题，编程题若干题
经典 SQL 数据库笔试题及答案整理
qq_73332379的博客
05-26 1866
马上又是金三银四啦，有蛮多小伙伴在跳槽找工作，但对于年限稍短的软件测试工程师，难免会需要进行笔试，而在笔试中，基本都会碰到一道关于数据库的大题，今天这篇文章呢，就收录了下最近学员反馈上来的一些数据库笔试题，包含答案！
SQL笔试题
沉觞的博客
09-25 845
SQL笔试题
阿里面试官：连sql语句都不会，还敢来面试？
程序员二黑
08-03 537
在面试大公司的时候，很多情况下都有笔试题，笔试题经常包含软件测试的基础知识点、逻辑题、等等，有时候也会碰到关于sql语句的题目，今天来列举一些简单的sql语句，希望对大家有所帮助。
数据库基本操作
haitunxiaomo的专栏
08-06 1423
一、数据表 Student(S#,Sname,Sage,Ssex) 学生表 S#：学号；Sname：学生姓名；Sage：学生年龄；Ssex：学生性别 Course(C#,Cname,T#) 课程表 C#,课程编号；Cname：课程名字；T#：教师编号 SC(S#,C#,score) 成绩表 S#：学号；C#,课程编号；score：成绩 Teacher(T#,Tname) 教师表 T#：教师编号； Tname：教师名字
软件测试面试必考的SQL语句
林家小猪
11-30 6223
SQL基础查询、多表连接
SQL笔试经典40题
weixin_44458771的博客
03-23 3865
SQL是我接触的第二门编程语言，学得挺早，但到现在已经好几年没用过SQL了，因为搞机器学习用python（numpy/pandas），最近刷笔试题遇到SQL题都有点忘了，故再次拾起，回顾回顾。本文所使用的数据库为Oracle19c。SQL (Structured Query Language) 是具有数据操纵和数据定义等多种功能的数据库语言，这种语言具有交互性特点，能为用户提供极大的便利，数据库管理系统应充分利用SQL语言提高计算机应用系统的工作质量与效率。SQL 语句可以单行或多行书写，以分号结尾；
SQL笔试经典50题
liushuang99的博客
08-28 3990
MySQL经典50题 1、 #1、查询“01”课程比“02”课程成绩高的所有学生的学号； SELECT s1.sid FROM SC s1 JOIN SC s2 ON s1.sid=s2.sid AND s1.cid='01' AND s2.cid='02' AND s1.score>s2.score 2、 #2、查询平均成绩大于60分的同学的学号和平均成绩； SELECT sid as 学号, avg(score) as 平均成绩 FROM SC GROUP BY sid HAVING 平均成绩&
30道SQL经典笔试题及其答案解析
SongYu的博客
01-18 5166
答案 查询 “01” 课程比 “02” 课程成绩高的所有学生的学号 select distinct t1.sid as sid from (select * from sc where sc.cid = '01') as t1 left join (select * from sc where sc.cid = '02') as t2 on t1.sid = t2.sid where t1.score > t2.score; 查询平均成绩
sql 笔试题总结版
10-29
48道常见的sql笔试题,基本涵盖所有考试的题了
荣耀笔试题总结.docx
10-05
荣耀笔试题总结.docx
Java笔试题总结（附答案，适合初中级）
04-24
内含SQL题、编程题、简答题、判断题等多种笔试题型，附带答案！！！适合Java初中级使用。希望可以对你有所帮助。
sql+中间件+网络知识等上市公司实施运维人员面试笔试题
04-21
现大概总结一下面试题的内容，答题时间为50分钟，题型有：一、填空题。 主要考Weblogic和oracle在服务器中常用的配置，实施中需注意的事项，项目管理的一些常规知识，linux服务器的一些运维知识 二、简述题。 包括...
数据库经典笔试题总结 -- 相当全面
10-07
数据库经典笔试题，知识点很全面，每个知识点都有详尽解释，主要包括： 范式，索引，备份，死锁，数据库操作，SQL语法等等，非常全面！！！
SQL Server详细使用教程
DYFdingyifei的博客
04-23 599
例如，要向名为“Customers”的表中插入一条新记录，您可以使用以下语句： INSERT INTO Customers (CustomerName, ContactName, Address, City, Country) VALUES ('Google', 'Larry Page', '1600 Amphitheatre Parkway', 'Mountain View', 'USA');本篇文章将为您提供SQL Server的详细使用教程，包括数据库的创建、表的创建、数据的插入和查询等内容。
[SWPUCTF-2022-新生赛]ez_sql
最新发布
liaochonxiang的博客
04-24 358
根据提示，POST传参得到假的flag。
专注 APT 攻击与防御—工具介绍—Sqlmap
weixin_62641226的博客
04-23 320
sqlmap参数详解：由于Sqlmap 是常用工具之一，所以本篇的篇幅较长，详解一次所有参数。
【数据库】MySQL分页查询
学习笔记，人工智能，教程，技术分享！
04-19 577
比如：size=10那么, page 和 size 记录范围 和 起始记录索引值 limit。limit offset（开始记录索引,是从0开始的）,size（要取出的条数）；通用的分页查询写法(page:页数,size:每页显示的记录条数)记住每页的起始记录索引值：(page-1)*size。案例：（查询有奖金且工资最高的前10名的员工信息）案例：（查询第11条到第25条）2页：11-20条 10,10。案例：（查询前5条员工数据）1页： 1-10条 0,10。where ....等等。
sql笔试题及答案
05-25
以下是几个 SQL 笔试题及其答案，供您参考： 1. 查询表中所有记录 ```sql SELECT * FROM table_name; ``` 2. 查询表中的某些字段 ```sql SELECT column1, column2, ... FROM table_name; ``` 3. 查询表中的某些...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 13
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值