0
0

SQL笔试题总结

文章目录

前言

本篇文章将总结博主在笔试或面试过程中碰到的比较有难度或经典的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
    GROUPBY`name`)t1
LEFTJOIN(SELECT`name`,SUM(score)AS 总分
    FROM student
    GROUPBY`name`)t2
USING(`name`);

逐步剖析

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

SELECT`name`,SUM(score)AS 总分
FROM student
GROUPBY`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
GROUPBY`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
    GROUPBY`name`)t1
LEFTJOIN(SELECT`name`,SUM(score)AS 总分
    FROM student
    GROUPBY`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_amountA2021/3/11500A2021/3/92500A2021/2/221000B2021/3/53000B2021/5/95000B2021/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(PARTITIONBY item_id ORDERBY trade_amount DESC)AS sort
    FROM orders)t
WHERE sort =1

item_idtrade_datetrade_amountA2021/3/92500B2021/5/95000

逐步剖析

1、因为要求

每个

商品的金额最高，所以分组排序必不可少。为什么不能用 group by 然后 max 呢？因为要求的是金额最高的

那一条数据

，而不是求最高金额，那一行数据的其他信息得保留下来。而group by 后的其他字段不用聚合函数是没法保存的，就丢失了，跟题意不符。

举个栗子吧：

SELECT item_id,MAX(trade_amount)FROM orders
GROUPBY item_id

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

还有这样：

SELECT item_id,trade_date,MAX(trade_amount)FROM orders
GROUPBY item_id

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

这样也是不对：

SELECT item_id,trade_date,MAX(trade_amount)FROM orders
GROUPBY item_id,trade_date

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

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

SELECT 
    item_id,
    trade_date,
    trade_amount,
    row_number()over(PARTITIONBY item_id ORDERBY trade_amount DESC)AS sort
FROM orders

item_idtrade_datetrade_amountsortA2021/3/925001A2021/3/115002A2021/2/2210003B2021/5/950001B2021/3/530002B2021/6/228003

2、最后子查询即可

SELECT 
    item_id,
    trade_date,
    trade_amount
FROM(SELECT 
        item_id,
        trade_date,
        trade_amount,
        row_number()over(PARTITIONBY item_id ORDERBY trade_amount DESC)AS sort
    FROM orders)t
WHERE sort =1

三、逐行累加

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

展示结果应该为：
item_idtrade_datetrade_amountA2021/21000A2021/35000B2021/33000B2021/58000B2021/68800

先放答案

SELECT
    t1.item_id AS item_id,
    t1.monthAS trade_date,SUM(t2.trade_amount)AS trade_amount
FROM(SELECT
        item_id,
        DATE_FORMAT(trade_date,'%Y-%m')AS`month`,SUM(trade_amount) trade_amount 
    FROM orders 
    GROUPBY item_id,DATE_FORMAT(trade_date,'%Y-%m')ORDERBY item_id,`month`)t1
LEFTJOIN(SELECT
        item_id,
        DATE_FORMAT(trade_date,'%Y-%m')AS`month`,SUM(trade_amount) trade_amount 
    FROM orders 
    GROUPBY item_id,DATE_FORMAT(trade_date,'%Y-%m')ORDERBY item_id,`month`)t2
ON t1.item_id = t2.item_id
WHERE t2.month<= t1.monthGROUPBY t1.item_id,t1.month

逐步剖析

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

SELECT
    item_id,
    DATE_FORMAT(trade_date,'%Y-%m')AS`month`,SUM(trade_amount) trade_amount 
FROM orders 
GROUPBY item_id,DATE_FORMAT(trade_date,'%Y-%m')ORDERBY item_id,`month`

item_idtrade_datetrade_amountA2021/21000A2021/34000B2021/33000B2021/55000B2021/6800
因为要求不同商品不同月份汇总，所以 group by 后要用两个字段分组。

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

SELECT 
    item_id,`month`,SUM(trade_amount)over(PARTITIONBY item_id) sums
FROM(SELECT
        item_id,
        DATE_FORMAT(trade_date,'%Y-%m')AS`month`,SUM(trade_amount) trade_amount 
    FROM orders 
    GROUPBY item_id,DATE_FORMAT(trade_date,'%Y-%m')ORDERBY item_id,`month`)t
GROUPBY item_id,`month`

在这里插入图片描述
但是 sum 窗口函数只能把分组内的总和计算出来填到每一列，没法做到逐行累加。到这我已经没有思路了，我就去求助了一下经理@红糖番薯，才知道可以用自连接。

我先自连接试试：

SELECT*FROM(SELECT
        item_id,
        DATE_FORMAT(trade_date,'%Y-%m')AS`month`,SUM(trade_amount) trade_amount 
    FROM orders 
    GROUPBY item_id,DATE_FORMAT(trade_date,'%Y-%m')ORDERBY item_id,`month`)t1
LEFTJOIN(SELECT
        item_id,
        DATE_FORMAT(trade_date,'%Y-%m')AS`month`,SUM(trade_amount) trade_amount 
    FROM orders 
    GROUPBY item_id,DATE_FORMAT(trade_date,'%Y-%m')ORDERBY item_id,`month`)t2
ON t1.item_id = t2.item_id

在这里插入图片描述
看似杂乱无章，其实有点东西

题目要求：一月份显示一月的金额，二月份显示一月份+二月份的金额

那么上面自连接的 sql 在最后也要加一个筛选条件

WHERE t2.month<= t1.month

这样使得表右半段的数据，即 t2 的数据不会超出 t1 指定的月份范围，不加时可能 t1 是第一月，而表的后半段 t2 是第二月这个情况。
加上后，表前半段 t1 的数据是某月，而后半段 t2 就是 <= t1 指定的月了，那么把 t2 月份的金额相加就是累积金额了。
怎么分组呢？按表前半段 t1 的月份分组，每一组中的表后半段 t2 就是 <= t1 的所有月份，金额和就是累积值。

SELECT
    t1.item_id AS item_id,
    t1.monthAS trade_date,SUM(t2.trade_amount)AS trade_amount
FROM(SELECT
        item_id,
        DATE_FORMAT(trade_date,'%Y-%m')AS`month`,SUM(trade_amount) trade_amount 
    FROM orders 
    GROUPBY item_id,DATE_FORMAT(trade_date,'%Y-%m')ORDERBY item_id,`month`)t1
LEFTJOIN(SELECT
        item_id,
        DATE_FORMAT(trade_date,'%Y-%m')AS`month`,SUM(trade_amount) trade_amount 
    FROM orders 
    GROUPBY item_id,DATE_FORMAT(trade_date,'%Y-%m')ORDERBY item_id,`month`)t2
ON t1.item_id = t2.item_id
WHERE t2.month<= t1.monthGROUPBY t1.item_id,t1.month

精髓就是 group by t1 的两列，但 sum t2 的金额

标签： sql 面试 数据库
本文转载自: https://blog.csdn.net/qq_56952363/article/details/128510444
版权归原作者 苍夜月明 所有， 如有侵权，请联系我们删除。
登录后发布评论

“SQL笔试题总结”的评论:

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

2024年【制冷与空调设备运行操作】考试及制冷与空调设备运行操作模拟考试题

AWS密钥管理服务实战指南：安全保障数据之锁

Centos7 安装mysql8.0.39(当前最新版)

深入理解网络概念：NAT网关、路由、IP、共享带宽、DNS解析

windows下使用make编译C/C++程序 gcc编译 MinGW编译器

使用GitHub托管静态网页

Soul Machines——AI生成虚拟主播或虚拟人，模拟真人交互

文章导航