这是我的史上最细SQL实战系列的第一篇文章：基础知识篇的补充篇。本系列文章核心不在于总结Mysql相关的八股（Mysql超全八股可见此文：Mysql超全八股笔记贴），本系列旨在：1.快速掌握sql知识，会写面试时常见的sql题目；2.了解在实际工作中需要注意的超多数据库和表设计相关的知识和坑点。

本系列文章分为3个部分：1基础知识篇；2sql笔试篇；3公司级数据库与表设计相关知识与坑点总结。

另外：

想要学习Java冲实习或冲春招的，我能助你一臂之力，我之前整理了高质量可速成的魔改外卖项目话术和7000字轮子项目话术，还有超全超精品八股大全专栏，怎么写简历，怎么包装实习经历，怎么0基础速成冲春招和实习等等等等精品帖子，大家可以去看看我的精品文章汇总帖子：https://www.nowcoder.com/discuss/721704696242536448?sourceSSR=users

我的八股大全、算法、项目话术全专栏（20w人学习，超千人订阅，牛客最受欢迎最高质量java八股专栏，内容包含： 1.八股大全：多一句没有少一句不行的最精简八股整理，完全可以应付校招社招的八股拷打！ 2.速成项目话术：目前有魔改苍穹外卖项目话术（额外扩展了很多技术亮点），能速成拿去面试，后面会更新魔改黑马点评、商城项目等等热门高质量项目话术 3.智力题超详细题解汇总； 4.面试时非技术问题话术整理，绝对震惊面试官一年； 5.算法lc hot100全题系列题解：绝对通俗易懂：快速冲刺秋招专栏

---------------------------------------------------------------------

1. 窗口函数

窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理。窗口函数的基本语法如下：

<窗口函数> over ([partition by <用于分组的列名>]
                order by <用于排序的列名>)

中括号里内容可以省略

那么语法中的<窗口函数>都有哪些呢？<窗口函数>的位置，可以放以下两种函数：

1、 专用窗口函数，包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。

2、 聚合函数，如sum. avg, count, max, min等

因为窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中。

例如如果我们想在每个班级内按成绩排名，得到下面的结果。

以班级“1”为例，这个班级的成绩“95”排在第1位，这个班级的“83”排在第4位。上面这个结果确实按我们的要求在每个班级内，按成绩排名了。得到上面结果的sql语句代码如下：

select *,
   rank() over (partition by 班级
                 order by 成绩 desc) as ranking
from 班级表

我们来解释下这个sql语句里的select子句。rank是排序的函数。要求是“每个班级内按成绩排名”，这句话可以分为两部分：

1）每个班级内：按班级分组 - partition by用来对表分组。在这个例子中，所以我们指定了按“班级”分组（partition by 班级）

2）按成绩排名：order by子句的功能是对分组后的结果进行排序，默认是按照升序（asc）排列。在本例中（order by 成绩 desc）是按成绩这一列排序，加了desc关键词表示降序排列。

窗口函数的作用

窗口函数具备了我们之前学过的group by子句分组的功能和order by子句排序的功能。那么，为什么还要用窗口函数呢？

这是因为，group by分组汇总后改变了表的行数，一行只有一个类别。而partiition by和rank函数不会减少原表中的行数。例如下面统计每个班级的人数。

专用窗口函数有哪些？

专用窗口函数rank, dense_rank, row_number

举例：

select *,
   rank() over (order by 成绩 desc) as ranking,
   dense_rank() over (order by 成绩 desc) as dese_rank,
   row_number() over (order by 成绩 desc) as row_num
from 班级表

从上面的结果可以看出：

（1）rank函数：这个例子中是5位，5位，5位，8位，也就是如果有并列名次的行，会占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，4。

（2）dense_rank函数：这个例子中是5位，5位，5位，6位，也就是如果有并列名次的行，不占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，2。

（3）row_number函数：这个例子中是5位，6位，7位，8位，也就是不考虑并列名次的情况。比如前3名是并列的名次，排名是正常的1，2，3，4。

最后，需要强调的一点是：在上述的这三个专用窗口函数中，函数后面的括号不需要任何参数，保持()空着就可以

聚合函数作为窗口函数

聚和窗口函数和上面提到的专用窗口函数用法完全相同，只需要把聚合函数写在窗口函数的位置即可，但是函数后面括号里面不能为空，需要指定聚合的列名。我们来看一下窗口函数是聚合函数时，会出来什么结果：

select *,
   sum(成绩) over (order by 学号) as current_sum,
   avg(成绩) over (order by 学号) as current_avg,
   count(成绩) over (order by 学号) as current_count,
   max(成绩) over (order by 学号) as current_max,
   min(成绩) over (order by 学号) as current_min
from 班级表

有发现什么吗？我单独用sum举个例子：如上图，聚合函数sum在窗口函数中，是对自身记录、及位于自身记录以上的数据进行求和的结果。比如0004号，在使用sum窗口函数后的结果，是对0001，0002，0003，0004号的成绩求和，若是0005号，则结果是0001号~0005号成绩的求和，以此类推。

不仅是sum求和，平均、计数、最大最小值，也是同理，都是针对自身记录、以及自身记录之上的所有数据进行计算，现在再结合刚才得到的结果（下图），是不是理解起来容易多了？

比如0005号后面的聚合窗口函数结果是：学号0001~0005五人成绩的总和、平均、计数及最大最小值。如果想要知道所有人成绩的总和、平均等聚合结果，看最后一行即可。

**这样使用窗口函数有什么用呢？**聚合函数作为窗口函数，可以在每一行的数据里直观的看到，截止到本行数据，统计数据是多少（最大值、最小值等）。同时可以看出每一行数据，对整体统计数据的影响。

注意

1.因为窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中。牢记是在执行完select之后，在所得结果集之上进行partition。

2.partition子句可以省略，省略就是不指定分组，结果如下，只是按成绩由高到低进行了排序

select *,
   rank() over (order by 成绩 desc) as ranking
from 班级表

但是，这就失去了窗口函数的功能，所以一般不要这么使用。

SQL经典面试题：topN问题

工作中会经常遇到这样的业务问题：如何找到每个类别下用户点击最多的5个商品是什么？这类问题其实就是常见的：每组最大的N条记录(topN)。

1、题目：现有“成绩表”，记录了每个学生各科的成绩。

2、问题：查找每个学生成绩最高的2个科目。

3、解题思路：

（1）看到问题中要查**“每个”学生最高的成绩，当有“每个”出现的时候，就要想到是要分组**了。这里是“每个学生”，结合表的结构，是按学生“姓名”来分组。

（2）将表按学生姓名分组后，把成绩按降序排列，排在最前面的2个就是我们要找的“成绩最高的2个科目”。

（3）现在分组后，需要排序，又不减少原表的行数，这种功能自然想到是窗口函数。

（4）使用哪个专用窗口函数？为了不受并列成绩的影响，使用row_number专用窗口函数

错误思路：

select *, 
      row_number() over (partition by 姓名
                   order by 成绩 desc) as ranking
from 成绩表
where ranking <=2

这样写，sql会报错，为什么呢？

要牢记sql的书写顺序和运行顺序。在运行顺序中，select字句是最后被运行的。

当明白了运行顺序以后，就知道错误的原因了：运行到”where ranking > 2”的时候，因为select字句还没有被执行，因此select中的“ranking”列还没有出现，从而导致报错。

正确解法：

select *
from (
   select *, 
          row_number() over (partition by 姓名
                       order by 成绩 desc) as ranking
   from 成绩表) as a
where ranking <=2

2.CTE

CTE是Common Table Expressions通用表表达式，它允许我们创建一个临时的命名结果集，可以在SQL语句内部引用。CTE通常由WITH关键字定义，并且可以在SQL语句中多次使用。使用CTE可以将复杂的查询分解为可读性更强的部分，提高SQL语句的可维护性。

CTE由两部分组成：名称和查询。名称用于引用CTE，并在同一SQL语句中多次使用。查询是一个普通的SELECT语句，用于定义CTE的内容。

CTE 可以理解成一个可以复用的子查询，当然跟子查询还是有点区别的，CTE 可以引用其他 CTE，但子查询不能引用其他子查询。

基础语法：

WITH cte_name (column1, column2, ...) AS (
    -- CTE的查询定义
    SELECT expression1, expression2, ...
    FROM table_name
    WHERE condition
)
-- 主查询，引用CTE
SELECT * FROM cte_na