文章来源：一个数据人的自留地

作者：鲸歌  “数据人创作者联盟”成员

预计阅读时间：15分钟

 

01

背景信息

• 工具：mysql数据库+navicat数据库管理客户端；
  
• 数据库&报表：School数据库，包含4张报表分别是student、score、course和teacher
  
• 报表明细信息：别看表格很小，其实能量巨大，真实业务场景会涉及更多报表以及海量数据，两者本质一样，区别在于数据量的多寡。倘若能够把4张报表实操顺溜，那么日后遇到巨量数据也不怕不怕啦~
  

02

阅读指南

为巩固SQL所学习的知识点，同时也期望通过刷题来提升写SQL语句的速度，因此将自己在刷题过程中所积累的感悟写下，与大家分享交流。恰如标题所述，刷题的感觉你懂的，对于工具性技能，唯有通过不断地实操来训练自己，以达到有一天可以不假思索直接写出。

以知识点作为各版块内容，每版块内容分别包含3部分：例题解析，所涉及知识点和举一反三，最后以“如何提高SQL查询效率?”为总结。

2.1 简单查询

例题解析: 查询姓“孟”的老师的个数

所涉及知识点：

• 日常Sql解决业务问题的步骤：翻译成大白话→写出分析思路→写出对应sql语句；
  
• 字符串模糊查询：like + % + _ 组合，%表示任意字符串，_特定单一字符串，%和_使用位置根据问题的要求而定；
  
• Count()函数:与计数相关，常与distinct联用，以区分是否剔除重复值；
  
• As:为表格原始字段或表名设置简单别名，方便书写
  
• Sql语句执行顺序：from子句→where子句→select子句；
  

举一反三：

在业务场景中，简单查询适用于提取临时数据，比如活动中某一时刻的订单数量、赠品数量和下单用户量等，在分析问题时也可以基于某个分论点快速提取相关数据进行验证。

2.2 汇总分析

例题解析: 统计每门课程的学生选修人数(超过2人的课程才统计)，要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序

所涉及知识点：

• 聚合函数有：count()、sum()、avg()、sum()、Max()、Min()；
  
• Group by 分组：分组后跟的列名与select后保持一致，分组可有多个列名，但多个列名保持1对1关系，避免因关系紊乱而报错；
  
• Where子句与having子句区别：having后可跟聚合函数，where不可以，having位置跟在group by后；
  
• Order by 排序：desc降序、asc升序，默认asc可省略，多个列名同一排序规则可只写一个desc/asc，不同列名不同排序需对应指明；
  

举一反三：

在业务场景中，可查看各大类产品的年、季度、月和日销量，根据产品的排名情况判断产品的生命周期，计算客户的下单频率可对客户进行分组管理等。

2.3 复杂查询

例题解析:查询没有学全所有课的学生的学号、姓名

所涉及知识点：

• 子查询：判断是否需要子查询和需要哪种类型，普通子查询（返回多行结果）、标量子查询（返回单行单列）和关系子查询（每组之间进行比较）；
  
• Sql语句书写顺序：先写子查询，再把子查询嵌套主查询中，若有多层嵌套，注意嵌套顺序，如上题子查询中还有子查询；
  
• Sql运行顺序：先运行子查询，其次运行from、where、group by 和having，再select，最后是order by和limit子句；
  

举一反三：

在实际业务中，遇到的业务问题往往比较复杂，通常一个查询结果作为另一个查询条件，所以对于类似针对销售贡献占比最高的客户在最近1年中销售情况分析，就需要先把贡献占比最高的客户找出，再找出对应的1年中销售记录，由此可以分析这些客户的客单价区间，销量区间，产品偏好等；

2.4 topN问题

例题解析:按课程号分组取成绩最大值所在行的数据

所涉及知识点：

关联子查询：判断条件是同组对象之间的比较，如上题是同一组中每一个对象与公认的对象之间的比较，关联条件被放置在子查询中，相当于是拿外部查询中的每一个对象与关联子查询中的特定对象进行比较。

举一反三：

在数据分析中，对比分析方法很常用，这就意味着关联子查询在实际业务中也会频繁涉及，比如在对店铺访客的分析中，有哪些新客对页面访问是高于同类其他新客的平均访问页面频次的，以此可以判断这些新客是潜在顾客。

2.5 多表查询

例题解析:查询各科成绩前两名的记录

所涉及知识点：

• Order by 排序：分辨升序和降序的区别，asc是数字由小到大，desc是数字由大到小；
  
• Limit 提取特定行数：limit n = limit 0,n，比如limit 5 是提取前5行，limit 2,3是从第3行开始提取3行（包括第3行）；
  
• Union all：表的加法，区别于union，union all允许重复值存在；
  

举一反三：

Order by +limit 类似excel中排序+筛选功能，适用于业务中要求提供不同类别的特定数据组合，比如要提供最近半年内各档活动中销量排名前五的商品，或者是每个月成交金额最高的3位用户的信息。

例题解析:查询平均成绩大于85的所有学生的学号、姓名和平均成绩

所涉及知识点：

多表联结：判断涉及几张表，通过哪列进行联结和联结方式是哪种，联结方式和对应语句见下图：

图片来源于：猴子聊数据分析

常规是2表联结，但有时也会3表联结，这时就需要通过中间表来实现联结。

举一反三：

现实业务场景中，企业的数据库报表都会分门别类，因此多表联结是必须要会的技能点，具体联结类型则要从业务需求方来判断，比如说在电商业务场景中，经常要分析的报表有订单和产品，当要分析某些商品是否热销时就要将2表进行联结。

2.6 Case表达式

例题解析:使用分段[100-85],[85-70],[70-60],[‹60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称

所涉及知识点：

Case表达式：用于多条件判断，比如上题的成绩分组，但要注意end不能遗忘。

举一反三：

业务场景中，数据分类是不可或缺的一项工作，比如RFM模型，电商业务中会对产品、用户和竞品进行分类，由此来看这些对象的变化趋势。

补充：case表达式+group by+max()等函数可以实现表格行列互换，见下：

那么，在提高SQL查询的效率上，我们需要知晓哪些呢？

以上Sql题是我从众多练习题中挑选而出，包含知识点相对较全。在实际技能提升中，我们要不断练习，且练习需要持之以恒，而不是三天打鱼两天晒网，与同行者共勉！

扫描下方二维码，注册成为九枝兰用户「预约产品演示」

 

更多详情，可扫描下方二维码，添加九枝兰-阿潘进行咨询