https://mp.weixin.qq.com/s?__biz=MzI2MjE3OTA1MA==&mid=2247488879&idx=1&sn=a3c9edf4009c9b0b896695aaca9d89c4&chksm=ea4e44eadd39cdfceeb9332c6b373a5a5fd2b54667c4ef5fa37323bc39a9b7aa95e4334c592a&scene=27

《计算机之魂》核心要点整理

核心思维与原则

计算机思维的核心概念

• 递归：通过函数调用自身来解决问题，通常用于分解复杂问题为更小的子问题。
• 分治：将一个问题分解为多个较小规模的问题，分别求解后合并结果。

关键原则

等价性原理

• 等价转换：如书中P310所述，交换变量x和y的方法展示了如何利用等价性进行巧妙编程。例如，x, x-1, 2x在某些情况下是等价的。
• 应用：等价性常用于将复杂状态简化，并间接解决难题，有助于归类和简化问题处理。

抽象化

• 模型构建：使用数学模型、数据结构或编码方式将现实问题转化为可计算的形式。
• 示例：从具体问题中抽象出不同的状态，并理解它们之间的转换关系。

平衡艺术

• 精度与范围：计算机表示浮点数时需平衡数值精度和表达范围，采用近似值以适应存储限制。
• 权衡策略：在不同场景下（如粗调+精调）做出适当的权衡，确保效率与效果的最优结合。

分类逻辑

• 智能问题的本质：很多问题可以归结为分类问题，包括分类、组织、查找和重组。
• 桥梁理论：第一座桥梁是将实际问题转换为信息处理中的分类问题；第二座桥梁是解决这些分类问题。
• 集合边界确认：
  • 二叉判断：如二分决策树、M叉树（如B+树）。
  • 枚举元素：如哈希表直接列出集合中的成员。

存储理论和技术

数据访问模式

• 特点导向：根据数据使用特点（顺序访问/随机访问）和获取量（大量/单个）选择合适的存储设备。
• 层次结构：围绕存储系统的层次展开讨论，考虑缓存、内存、磁盘等不同层级的作用。

多路归并与排序算法

• 多路归并：用于处理n个有序序列的合并问题，如找出前几名元素。
• 快速排序分割：适用于解决中值问题或比例划分问题。

图论及其应用

图的基本概念

• 点与线的关系：图是对离散、有限集合元素间关系的描述，广泛应用于最短路径、最大流、匹配等问题。

图的遍历方法

• 深度优先遍历：生成图的生成树，探索所有可能路径。
• 连通性分析：检查图中节点间的可达性。

最短路径与最大流

• 动态规划：一种常用算法，用于求解最短路径问题。
• 等价问题：最大流问题与最大配对问题在一定条件下是等价的。

确定性与随机性

概率算法的应用

• 世界本质：不确定性是固有的，概率算法能够有效应对这种特性。
• 量子通信安全：依赖于随机性来保证安全性。
• 置信度权衡：在成本与效果之间寻找最佳平衡点。

排序优化

蒂姆排序（Timsort）

• 混合排序法：结合了插入排序（节省内存）和归并排序（节省时间）的优点，是一种高效的排序算法。

数学特性

对数函数特征

• 分辨率变化：对数函数对较小数字提供高分辨率，而对较大数字则分辨率较低，这反映了其非线性的增长模式。

参考https://blog.csdn.net/xushiyu1996818/article/details/106590789

同一个进程内的线程切换比进程切换快，因为线程具有相同的地址空间（虚拟内存共享），这意味着同一个进程的线程都具有同一个页表，那么在切换的时候不需要切换页表。而对于进程之间的切换，切换的时候要把页表给切换掉，而页表的切换过程开销是比较大的；

进程上下文 操作系统中把进程物理实体和支持进程运行的环境合称为进程上下文。

进程在其当前上下文中运行，当系统调度新进程占有处理器时，新老进程随之发生上下文切换。即保存老进程状态而装入被保护了的新进程的状态，以便新进程运行

进程上下文组成
用户级上下文：由正文（程序）、数据、共享存储区、用户栈组成，占用进程的虚地址空间。

存器上下文：由程序状态字寄存器、指令计数器、栈指针、控制寄存器、通用寄存器等组成。

系统级上下文：由进程控制块、主存管理信息（页表或段表）、核心栈等组成。

CPU 上下文切换
大多数操作系统都是多任务，通常支持大于 CPU 数量的任务同时运行。实际上，这些任务并不是同时运行的，只是因为系统在很短的时间内，让各个任务分别在 CPU 运行，于是就造成同时运行的错误。

任务是交给 CPU 运行的，那么在每个任务运行前，CPU 需要知道任务从哪里加载，又从哪里开始运行。

所以，操作系统需要事先帮 CPU 设置好 CPU 寄存器和程序计数器。

CPU 寄存器是 CPU 内部一个容量小，但是速度极快的内存（缓存）。我举个例子，寄存器像是你的口袋，内存像你的书包，硬盘则是你家里的柜子，如果你的东西存放到口袋，那肯定是比你从书包或家里柜子取出来要快的多。

再来，程序计数器则是用来存储 CPU 正在执行的指令位置、或者即将执行的下一条指令位置。

所以说，CPU 寄存器和程序计数是 CPU 在运行任何任务前，所必须依赖的环境，这些环境就叫做 CPU 上下文。

既然知道了什么是 CPU 上下文，那理解 CPU 上下文切换就不难了。

CPU 上下文切换就是先把前一个任务的 CPU 上下文（CPU 寄存器和程序计数器）保存起来，然后加载新任务的上下文到这些寄存器和程序计数器，最后再跳转到程序计数器所指的新位置，运行新任务。

系统内核会存储保持下来的上下文信息，当此任务再次被分配给 CPU 运行时，CPU 会重新加载这些上下文，这样就能保证任务原来的状态不受影响，让任务看起来还是连续运行。

上面说到所谓的「任务」，主要包含进程、线程和中断。所以，可以根据任务的不同，***==把 CPU 上下文切换分成：进程上下文切换、线程上下文切换和中断上下文切换。==***

进程的上下文切换到底是切换什么呢？
进程是由内核管理和调度的，所以进程的切换只能发生在内核态。

所以，进程的上下文切换不仅包含了虚拟内存、栈、全局变量等用户空间的资源，还包括了内核堆栈、寄存器等内核空间的资源。

通常，会把交换的信息保存在进程的 PCB，当要运行另外一个进程的时候，我们需要从这个进程的 PCB 取出上下文，然后恢复到 CPU 中，这使得这个进程可以继续执行，如下图所示：

大家需要注意，进程的上下文开销是很关键的，我们希望它的开销越小越好，这样可以使得进程可以把更多时间花费在执行程序上，而不是耗费在上下文切换。

原文链接：https://blog.csdn.net/xushiyu1996818/article/details/106590789

自动机，状态机，有限自动机，有限状态机，有限状态自动机，非确定下有限状态自动，确定性有限状态自动机的区别于联系-CSDN博客

描述

回溯其实就是暴力搜索，其思路是dfs。

回溯法思路的简单描述是：把问题的解空间转化成了图或者树的结构表示，然后使用**深度优先搜索策略**进行遍历，遍历的过程中记录和寻找所有可行解或者最优解。

分类

解空间树分为两种：**子集树和排列树**。两种在算法结构和思路上大体相同。

排列就是遍历所有空间，需要标记是否遍历过；for(i=0)

组合和子集其实是一类问题，只用遍历同一层还没遍历的，因此可以省去标记，for(i=start)

不同的是子集问题是，只要遍历一个点，就将当前符合要求的路径添加到最终答案中。

回溯法模板：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

void backtrack(参数) {

    if (终止条件) {
        存放结果;
        return;
    }

//其实就是遍历与其相连的所有节点

    for (选择：本层集合中元素（树中节点孩子的数量就是集合的大小）) {

      判断是否处理过

        处理节点;

      记录状态、遍历过与否的标记

        backtrack(路径，选择列表); // 递归
        回溯，撤销处理结果，回退状态、遍历过与否的标记
    }
}

对于需要去重的回溯问题：

（1）排列：

for(int i = 0; i < nums.length; i++){

            //若在同一层已经遍历过（就是循环过了），则跳过这次

            if(i > 1 && nums[i] == nums[i-1] && !used[i - 1]) continue;

（2）子集和组合：

for(inti=start;i<nums.length;i++){

if(i>start&&nums[i]==nums[i-1])continue;

题目

当问题是要求满足某种性质（约束条件）的所有解或最优解时，往往使用回溯法。
它有“通用解题法”之美誉。

1. 子集问题lc78，lc90
2. 排列问题lc46，lc47
3. 组合求和问题lc39，40，216
4. 分割回文串lc131
5. 阿里快递最短路alibaba（DFS）
6. 组合问题lc77【lc77中有对回溯法的优化，即剪枝】，lc401
7. 二维平面上使用回溯法 lc79
8. floodfill lc200（岛屿的个数），130，417
9. N皇后问题lc51
10. 数独问题lc37

由于每天的情况都可以分为两种状态：买入和卖出，而这两种状态的收益和前一天的状态有关，因此构造动态规划的通用解。
在问题的进一步扩展上，增加了交易次数。
难点和关键在于找到状态之间的递推公式。
而动态规划的空间优化需要注意。

参考分享｜股票问题系列通解（转载翻译） - 力扣（LeetCode）

题目来源于：牛客题霸 - SQL 进阶挑战

较难或者困难的题目可以根据自身实际情况和面试需要来决定是否要跳过。

聚合函数

SQL 类别高难度试卷得分的截断平均值（较难）

描述： 牛客的运营同学想要查看大家在 SQL 类别中高难度试卷的得分情况。

请你帮她从exam_record数据表中计算所有用户完成 SQL 类别高难度试卷得分的截断平均值（去掉一个最大值和一个最小值后的平均值）。

示例数据：examination_info（exam_id 试卷 ID, tag 试卷类别, difficulty 试卷难度, duration 考试时长, release_time 发布时间）

示例数据：exam_record（uid 用户 ID, exam_id 试卷 ID, start_time 开始作答时间, submit_time 交卷时间, score 得分）

根据输入你的查询结果如下：

从examination_info表可知，试卷 9001 为高难度 SQL 试卷，该试卷被作答的得分有[80,81,84,90,50]，去除最高分和最低分后为[80,81,84]，平均分为 81.6666667，保留一位小数后为 81.7

输入描述：

输入数据中至少有 3 个有效分数

思路一： 要找出高难度 sql 试卷，肯定需要联 examination_info 这张表，然后找出高难度的课程，由 examination_info 得知，高难度 sql 的 exam_id 为 9001，那么等下就以 exam_id = 9001 作为条件去查询；

先找出 9001 号考试 select * from exam_record where exam_id = 9001

然后，找出最高分 select max(score) 最高分 from exam_record where exam_id = 9001

接着，找出最低分 select min(score) 最低分 from exam_record where exam_id = 9001

在查询出来的分数结果集当中，去掉最高分和最低分，最直观能想到的就是 NOT IN 或者 用 NOT EXISTS 也行，这里以 NOT IN 来做

首先将主体写出来select tag, difficulty, round(avg(score), 1) clip_avg_score from examination_info info INNER JOIN exam_record record

小 tips : MYSQL 的 ROUND() 函数 ,ROUND(X)返回参数 X 最近似的整数 ROUND(X,D)返回 X ,其值保留到小数点后 D 位,第 D 位的保留方式为四舍五入。

再将上面的 “碎片” 语句拼凑起来即可， 注意在 NOT IN 中两个子查询用 UNION ALL 来关联，用 union 把 max 和 min 的结果集中在一行当中，这样形成一列多行的效果。

答案一：

1
2
3
4
5
6
7
8
9
10
11
12
13

SELECT tag, difficulty, ROUND(AVG(score), 1) clip_avg_score
	FROM examination_info info  INNER JOIN exam_record record
		WHERE info.exam_id = record.exam_id
			AND  record.exam_id = 9001
				AND record.score NOT IN(
					SELECT MAX(score)
						FROM exam_record
							WHERE exam_id = 9001
								UNION ALL
					SELECT MIN(score)
						FROM exam_record
							WHERE exam_id = 9001
				)

这是最直观，也是最容易想到的解法，但是还有待改进，这算是投机取巧过关，其实严格按照题目要求应该这么写：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

SELECT tag,
       difficulty,
       ROUND(AVG(score), 1) clip_avg_score
FROM examination_info info
INNER JOIN exam_record record
WHERE info.exam_id = record.exam_id
  AND record.exam_id =
    (SELECT examination_info.exam_id
     FROM examination_info
     WHERE tag = 'SQL'
       AND difficulty = 'hard' )
  AND record.score NOT IN
    (SELECT MAX(score)
     FROM exam_record
     WHERE exam_id =
         (SELECT examination_info.exam_id
          FROM examination_info
          WHERE tag = 'SQL'
            AND difficulty = 'hard' )
     UNION ALL SELECT MIN(score)
     FROM exam_record
     WHERE exam_id =
         (SELECT examination_info.exam_id
          FROM examination_info
          WHERE tag = 'SQL'
            AND difficulty = 'hard' ) )

然而你会发现，重复的语句非常多，所以可以利用WITH来抽取公共部分

WITH 子句介绍：

WITH 子句，也称为公共表表达式（Common Table Expression，CTE），是在 SQL 查询中定义临时表的方式。它可以让我们在查询中创建一个临时命名的结果集，并且可以在同一查询中引用该结果集。

基本用法：

1
2
3
4
5
6
7
8
9
10

WITH cte_name (column1, column2, ..., columnN) AS (
    -- 查询体
    SELECT ...
    FROM ...
    WHERE ...
)
-- 主查询
SELECT ...
FROM cte_name
WHERE ...

WITH 子句由以下几个部分组成：

• cte_name: 给临时表起一个名称，可以在主查询中引用。
• (column1, column2, ..., columnN): 可选，指定临时表的列名。
• AS: 必需，表示开始定义临时表。
• CTE 查询体: 实际的查询语句，用于定义临时表中的数据。

WITH 子句的主要用途之一是增强查询的可读性和可维护性，尤其在涉及多个嵌套子查询或需要重复使用相同的查询逻辑时。通过将这些逻辑放在一个命名的临时表中，我们可以更清晰地组织查询，并消除重复代码。

此外，WITH 子句还可以在复杂的查询中实现递归查询。递归查询允许我们在单个查询中执行对同一表的多次迭代，逐步构建结果集。这在处理层次结构数据、组织结构和树状结构等场景中非常有用。

小细节：MySQL 5.7 版本以及之前的版本不支持在 WITH 子句中直接使用别名。

下面是改进后的答案：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

WITH t1 AS
  (SELECT record.*,
          info.tag,
          info.difficulty
   FROM exam_record record
   INNER JOIN examination_info info ON record.exam_id = info.exam_id
   WHERE info.tag = "SQL"
     AND info.difficulty = "hard" )
SELECT tag,
       difficulty,
       ROUND(AVG(score), 1)
FROM t1
WHERE score NOT IN
    (SELECT max(score)
     FROM t1
     UNION SELECT min(score)
     FROM t1)

思路二：

• 筛选 SQL 高难度试卷：where tag="SQL" and difficulty="hard"
• 计算截断平均值：(和-最大值-最小值) / (总个数-2):
  • (sum(score) - max(score) - min(score)) / (count(score) - 2)
  • 有一个缺点就是，如果最大值和最小值有多个，这个方法就很难筛选出来, 但是题目中说了—–>去掉一个最大值和一个最小值后的平均值, 所以这里可以用这个公式。

答案二：

1
2
3
4
5
6
7
8

SELECT info.tag,
       info.difficulty,
       ROUND((SUM(record.score)- MIN(record.score)- MAX(record.score)) / (COUNT(record.score)- 2), 1) AS clip_avg_score
FROM examination_info info,
     exam_record record
WHERE info.exam_id = record.exam_id
  AND info.tag = "SQL"
  AND info.difficulty = "hard";

统计作答次数

有一个试卷作答记录表 exam_record，请从中统计出总作答次数 total_pv、试卷已完成作答数 complete_pv、已完成的试卷数 complete_exam_cnt。

示例数据 exam_record 表（uid 用户 ID, exam_id 试卷 ID, start_time 开始作答时间, submit_time 交卷时间, score 得分）：

示例输出：

解释：表示截止当前，有 10 次试卷作答记录，已完成的作答次数为 7 次（中途退出的为未完成状态，其交卷时间和份数为 NULL），已完成的试卷有 9001 和 9002 两份。

思路： 这题一看到统计次数，肯定第一时间就要想到用COUNT这个函数来解决，问题是要统计不同的记录，该怎么来写？使用子查询就能解决这个题目(这题用 case when 也能写出来，解法类似，逻辑不同而已)；首先在做这个题之前，让我们先来了解一下COUNT的基本用法；

COUNT() 函数的基本语法如下所示：

1

COUNT(expression)

其中，expression 可以是列名、表达式、常量或通配符。下面是一些常见的用法示例：

1. 计算表中所有行的数量：

1

SELECT COUNT(*) FROM table_name;

2. 计算特定列非空（不为 NULL）值的数量：

1

SELECT COUNT(column_name) FROM table_name;

3. 计算满足条件的行数：

1

SELECT COUNT(*) FROM table_name WHERE condition;

4. 结合 GROUP BY 使用，计算分组后每个组的行数：

1

SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;

5. 计算不同列组合的唯一组合数：

1

SELECT COUNT(DISTINCT column_name1, column_name2) FROM table_name;

在使用 COUNT() 函数时，如果不指定任何参数或者使用 COUNT(*)，将会计算所有行的数量。而如果使用列名，则只会计算该列非空值的数量。

另外，COUNT() 函数的结果是一个整数值。即使结果是零，也不会返回 NULL，这点需要谨记。

答案：

1
2
3
4
5
6

SELECT
	count(*) total_pv,
	( SELECT count(*) FROM exam_record WHERE submit_time IS NOT NULL ) complete_pv,
	( SELECT COUNT( DISTINCT exam_id, score IS NOT NULL OR NULL ) FROM exam_record ) complete_exam_cnt
FROM
	exam_record

这里着重说一下COUNT( DISTINCT exam_id, score IS NOT NULL OR NULL )这一句，判断 score 是否为 null ，如果是即为真，如果不是返回 null；注意这里如果不加 or null 在不是 null 的情况下只会返回 false 也就是返回 0；

COUNT本身是不可以对多列求行数的，distinct的加入是的多列成为一个整体，可以求出现的行数了;count distinct在计算时只返回非 null 的行, 这个也要注意；

另外通过本题 get 到了——>count 加条件常用句式count( 列判断 or null)

得分不小于平均分的最低分

描述： 请从试卷作答记录表中找到 SQL 试卷得分不小于该类试卷平均得分的用户最低得分。

示例数据 exam_record 表（uid 用户 ID, exam_id 试卷 ID, start_time 开始作答时间, submit_time 交卷时间, score 得分）：

examination_info 表（exam_id 试卷 ID, tag 试卷类别, difficulty 试卷难度, duration 考试时长, release_time 发布时间）

示例输出数据：

解释：试卷 9001 和 9002 为 SQL 类别，作答这两份试卷的得分有[80,89,87,90]，平均分为 86.5，不小于平均分的最小分数为 87

思路：这类题目第一眼看确实很复杂， 因为不知道从哪入手，但是当我们仔细读题审题后，要学会抓住题干中的关键信息。以本题为例：请从试卷作答记录表中找到SQL试卷得分不小于该类试卷平均得分的用户最低得分。你能一眼从中提取哪些有效信息来作为解题思路？

第一条：找到==SQL==试卷得分

第二条：该类试卷==平均得分==

第三条：该类试卷的==用户最低得分==

然后中间的 “桥梁” 就是==不小于==

将条件拆分后，先逐步完成

1
2
3
4
5

-- 找出tag为‘SQL’的得分   【80, 89,87,90】
-- 再算出这一组的平均得分
select  ROUND(AVG(score), 1) from  examination_info info INNER JOIN exam_record record
	where info.exam_id = record.exam_id
	and tag= 'SQL'

然后再找出该类试卷的最低得分，接着将结果集【80, 89,87,90】 去和平均分数作比较，方可得出最终答案。

答案：

1
2
3
4
5
6
7
8
9
10
11

SELECT MIN(score) AS min_score_over_avg
FROM examination_info info
INNER JOIN exam_record record
WHERE info.exam_id = record.exam_id
  AND tag= 'SQL'
  AND score >=
    (SELECT ROUND(AVG(score), 1)
     FROM examination_info info
     INNER JOIN exam_record record
     WHERE info.exam_id = record.exam_id
       AND tag= 'SQL' )

其实这类题目给出的要求看似很 “绕”，但其实仔细梳理一遍，将大条件拆分成小条件，逐个拆分完以后，最后将所有条件拼凑起来。反正只要记住：抓主干，理分支，问题便迎刃而解。

分组查询

平均活跃天数和月活人数

描述：用户在牛客试卷作答区作答记录存储在表 exam_record 中，内容如下：

exam_record 表（uid 用户 ID, exam_id 试卷 ID, start_time 开始作答时间, submit_time 交卷时间, score 得分）

请计算 2021 年每个月里试卷作答区用户平均月活跃天数 avg_active_days 和月度活跃人数 mau，上面数据的示例输出如下：

解释：2021 年 7 月有 2 人活跃，共活跃了 3 天（1001 活跃 1 天，1002 活跃 2 天），平均活跃天数 1.5；2021 年 9 月有 4 人活跃，共活跃了 5 天，平均活跃天数 1.25，结果保留 2 位小数。

注：此处活跃指有==交卷==行为。

思路：读完题先注意高亮部分；一般求天数和月活跃人数马上就要想到相关的日期函数；这一题我们同样来进行拆分，把问题细化再解决；首先求活跃人数，肯定要用到COUNT()，那这里首先就有一个坑，不知道大家注意了没有？用户 1002 在 9 月份做了两种不同的试卷，所以这里要注意去重，不然在统计的时候，活跃人数是错的；第二个就是要知道日期的格式化，如上表，题目要求以202107这种日期格式展现，要用到DATE_FORMAT来进行格式化。

基本用法：

DATE_FORMAT(date_value, format)

• date_value 参数是待格式化的日期或时间值。
• format 参数是指定的日期或时间格式（这个和 Java 里面的日期格式一样）。

答案：

1
2
3
4
5
6

SELECT DATE_FORMAT(submit_time, '%Y%m') MONTH,
                                        round(count(DISTINCT UID, DATE_FORMAT(submit_time, '%Y%m%d')) / count(DISTINCT UID), 2) avg_active_days,
                                        COUNT(DISTINCT UID) mau
FROM exam_record
WHERE YEAR (submit_time) = 2021
GROUP BY MONTH

这里多说一句, 使用COUNT(DISTINCT uid, DATE_FORMAT(submit_time, '%Y%m%d')) 可以统计在 uid 列和 submit_time 列按照年份、月份和日期进行格式化后的组合值的数量。

月总刷题数和日均刷题数

描述：现有一张题目练习记录表 practice_record，示例内容如下：

请从中统计出 2021 年每个月里用户的月总刷题数 month_q_cnt 和日均刷题数 avg_day_q_cnt（按月份升序排序）以及该年的总体情况，示例数据输出如下：

解释：2021 年 8 月共有 2 次刷题记录，日均刷题数为 2/31=0.065（保留 3 位小数）；2021 年 9 月共有 3 次刷题记录，日均刷题数为 3/30=0.100；2021 年共有 5 次刷题记录（年度汇总平均无实际意义，这里我们按照 31 天来算 5/31=0.161）

牛客已经采用最新的 Mysql 版本，如果您运行结果出现错误：ONLY_FULL_GROUP_BY，意思是：对于 GROUP BY 聚合操作，如果在 SELECT 中的列，没有在 GROUP BY 中出现，那么这个 SQL 是不合法的，因为列不在 GROUP BY 从句中，也就是说查出来的列必须在 group by 后面出现否则就会报错，或者这个字段出现在聚合函数里面。

思路：

看到实例数据就要马上联想到相关的函数，比如submit_month就要用到DATE_FORMAT来格式化日期。然后查出每月的刷题数量。

每月的刷题数量

1
2
3
4
5

SELECT MONTH ( submit_time ), COUNT( question_id )
FROM
	practice_record
GROUP BY
	MONTH (submit_time)

接着第三列这里要用到DAY(LAST_DAY(date_value))函数来查找给定日期的月份中的天数。

示例代码如下：

1
2
3
4
5
6
7
8

SELECT DAY(LAST_DAY('2023-07-08')) AS days_in_month;
-- 输出：31

SELECT DAY(LAST_DAY('2023-02-01')) AS days_in_month;
-- 输出：28 (闰年中的二月份)

SELECT DAY(LAST_DAY(NOW())) AS days_in_current_month;
-- 输出：31 （当前月份的天数）

使用 LAST_DAY() 函数获取给定日期的当月最后一天，然后使用 DAY() 函数提取该日期的天数。这样就能获得指定月份的天数。

需要注意的是，LAST_DAY() 函数返回的是日期值，而 DAY() 函数用于提取日期值中的天数部分。

有了上述的分析之后，即可马上写出答案，这题复杂就复杂在处理日期上，其中的逻辑并不难。

答案：

1
2
3
4
5
6
7
8
9
10
11
12
13

SELECT DATE_FORMAT(submit_time, '%Y%m') submit_month,
       count(question_id) month_q_cnt,
       ROUND(COUNT(question_id) / DAY (LAST_DAY(submit_time)), 3) avg_day_q_cnt
FROM practice_record
WHERE DATE_FORMAT(submit_time, '%Y') = '2021'
GROUP BY submit_month
UNION ALL
SELECT '2021汇总' AS submit_month,
       count(question_id) month_q_cnt,
       ROUND(COUNT(question_id) / 31, 3) avg_day_q_cnt
FROM practice_record
WHERE DATE_FORMAT(submit_time, '%Y') = '2021'
ORDER BY submit_month

在实例数据输出中因为最后一行需要得出汇总数据，所以这里要 UNION ALL加到结果集中；别忘了最后要排序！

未完成试卷数大于 1 的有效用户（较难）

描述：现有试卷作答记录表 exam_record（uid 用户 ID, exam_id 试卷 ID, start_time 开始作答时间, submit_time 交卷时间, score 得分），示例数据如下：

还有一张试卷信息表 examination_info（exam_id 试卷 ID, tag 试卷类别, difficulty 试卷难度, duration 考试时长, release_time 发布时间），示例数据如下：

请统计 2021 年每个未完成试卷作答数大于 1 的有效用户的数据（有效用户指完成试卷作答数至少为 1 且未完成数小于 5），输出用户 ID、未完成试卷作答数、完成试卷作答数、作答过的试卷 tag 集合，按未完成试卷数量由多到少排序。示例数据的输出结果如下：

解释：2021 年的作答记录中，除了 1004，其他用户均满足有效用户定义，但只有 1002 未完成试卷数大于 1，因此只输出 1002，detail 中是 1002 作答过的试卷{日期:tag}集合，日期和 tag 间用 : 连接，多元素间用 ; 连接。

思路：

仔细读题后，分析出：首先要联表，因为后面要输出tag；

筛选出 2021 年的数据

1
2
3
4

SELECT *
FROM exam_record er
LEFT JOIN examination_info ei ON er.exam_id = ei.exam_id
WHERE YEAR (er.start_time)= 2021

根据 uid 进行分组，然后对每个用户进行条件进行判断，题目中要求完成试卷数至少为1,未完成试卷数要大于1，小于5

那么等会儿写 sql 的时候条件应该是：未完成 > 1 and 已完成 >=1 and 未完成 < 5

因为最后要用到字符串的拼接，而且还要组合拼接，这个可以用GROUP_CONCAT函数，下面简单介绍一下该函数的用法：

基本格式：

1

GROUP_CONCAT([DISTINCT] expr [ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [, ...]]             [SEPARATOR sep])

• expr：要连接的列或表达式。
• DISTINCT：可选参数，用于去重。当指定了 DISTINCT，相同的值只会出现一次。
• ORDER BY：可选参数，用于排序连接后的值。可以选择升序 (ASC) 或降序 (DESC) 排序。
• SEPARATOR sep：可选参数，用于设置连接后的值的分隔符。（本题要用这个参数设置 ; 号 ）

GROUP_CONCAT() 函数常用于 GROUP BY 子句中，将一组行的值连接为一个字符串，并在结果集中以聚合的形式返回。

答案：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

SELECT a.uid,
       SUM(CASE
               WHEN a.submit_time IS NULL THEN 1
           END) AS incomplete_cnt,
       SUM(CASE
               WHEN a.submit_time IS NOT NULL THEN 1
           END) AS complete_cnt,
       GROUP_CONCAT(DISTINCT CONCAT(DATE_FORMAT(a.start_time, '%Y-%m-%d'), ':', b.tag)
                    ORDER BY start_time SEPARATOR ";") AS detail
FROM exam_record a
LEFT JOIN examination_info b ON a.exam_id = b.exam_id
WHERE YEAR (a.start_time)= 2021
GROUP BY a.uid
HAVING incomplete_cnt > 1
AND complete_cnt >= 1
AND incomplete_cnt < 5
ORDER BY incomplete_cnt DESC

• SUM(CASE WHEN a.submit_time IS NULL THEN 1 END) 统计了每个用户未完成的记录数量。
• SUM(CASE WHEN a.submit_time IS NOT NULL THEN 1 END) 统计了每个用户已完成的记录数量。
• GROUP_CONCAT(DISTINCT CONCAT(DATE_FORMAT(a.start_time, '%Y-%m-%d'), ':', b.tag) ORDER BY a.start_time SEPARATOR ';') 将每个用户的考试日期和标签以逗号分隔的形式连接成一个字符串，并按考试开始时间进行排序。

嵌套子查询

月均完成试卷数不小于 3 的用户爱作答的类别（较难）

描述：现有试卷作答记录表 exam_record（uid：用户 ID, exam_id：试卷 ID, start_time：开始作答时间, submit_time：交卷时间，没提交的话为 NULL, score：得分），示例数据如下：

试卷信息表 examination_info（exam_id：试卷 ID, tag：试卷类别, difficulty：试卷难度, duration：考试时长, release_time：发布时间），示例数据如下：

请从表中统计出 “当月均完成试卷数”不小于 3 的用户们爱作答的类别及作答次数，按次数降序输出，示例输出如下：

解释：用户 1002 和 1005 在 2021 年 09 月的完成试卷数目均为 3，其他用户均小于 3；然后用户 1002 和 1005 作答过的试卷 tag 分布结果按作答次数降序排序依次为 C++、SQL、算法。

思路：这题考察联合子查询，重点在于月均回答>=3, 但是个人认为这里没有表述清楚，应该直接说查 9 月的就容易理解多了；这里不是每个月都要>=3 或者是所有答题次数/答题月份。不要理解错误了。

先查询出哪些用户月均答题大于三次

1
2
3
4
5

SELECT UID
FROM exam_record record
GROUP BY UID,
         MONTH (start_time)
HAVING count(submit_time) >= 3

有了这一步之后再进行深入，只要能理解上一步(我的意思是不被题目中的月均所困扰)，然后再套一个子查询，查哪些用户包含其中，然后查出题目中所需的列即可。记得排序！！

1
2
3
4
5
6
7
8
9
10
11
12

SELECT tag,
       count(start_time) AS tag_cnt
FROM exam_record record
INNER JOIN examination_info info ON record.exam_id = info.exam_id
WHERE UID IN
    (SELECT UID
     FROM exam_record record
     GROUP BY UID,
              MONTH (start_time)
     HAVING count(submit_time) >= 3)
GROUP BY tag
ORDER BY tag_cnt DESC

试卷发布当天作答人数和平均分

描述：现有用户信息表 user_info（uid 用户 ID，nick_name 昵称, achievement 成就值, level 等级, job 职业方向, register_time 注册时间），示例数据如下：

释义：用户 1001 昵称为牛客 1 号，成就值为 3100，用户等级是 7 级，职业方向为算法，注册时间 2020-01-01 10:00:00

试卷信息表 examination_info（exam_id 试卷 ID, tag 试卷类别, difficulty 试卷难度, duration 考试时长, release_time 发布时间） 示例数据如下：

试卷作答记录表 exam_record（uid 用户 ID, exam_id 试卷 ID, start_time 开始作答时间, submit_time 交卷时间, score 得分） 示例数据如下：

请计算每张 SQL 类别试卷发布后，当天 5 级以上的用户作答的人数 uv 和平均分 avg_score，按人数降序，相同人数的按平均分升序，示例数据结果输出如下：

解释：只有一张 SQL 类别的试卷，试卷 ID 为 9001，发布当天（2021-09-01）有 1001、1002、1003、1005 作答过，但是 1003 是 5 级用户，其他 3 位为 5 级以上，他们三的得分有[70,80,85,90]，平均分为 81.3（保留 1 位小数）。

思路：这题看似很复杂，但是先逐步将“外边”条件拆分，然后合拢到一起，答案就出来，多表查询反正记住：由外向里，抽丝剥茧。

先把三种表连起来，同时给定一些条件，比如题目中要求等级> 5的用户，那么可以先查出来

1
2
3
4
5
6
7

SELECT DISTINCT u_info.uid
FROM examination_info e_info
INNER JOIN exam_record record
INNER JOIN user_info u_info
WHERE e_info.exam_id = record.exam_id
  AND u_info.uid = record.uid
  AND u_info.LEVEL > 5

接着注意题目中要求：每张sql类别试卷发布后，当天作答用户，注意其中的==当天==，那我们马上就要想到要用到时间的比较。

对试卷发布日期和开始考试日期进行比较：DATE(e_info.release_time) = DATE(record.start_time)；不用担心submit_time 为 null 的问题，后续在 where 中会给过滤掉。

答案：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

SELECT record.exam_id AS exam_id,
       COUNT(DISTINCT u_info.uid) AS uv,
       ROUND(SUM(record.score) / COUNT(u_info.uid), 1) AS avg_score
FROM examination_info e_info
INNER JOIN exam_record record
INNER JOIN user_info u_info
WHERE e_info.exam_id = record.exam_id
  AND u_info.uid = record.uid
  AND DATE (e_info.release_time) = DATE (record.start_time)
  AND submit_time IS NOT NULL
  AND tag = 'SQL'
  AND u_info.LEVEL > 5
GROUP BY record.exam_id
ORDER BY uv DESC,
         avg_score ASC

注意最后的分组排序！先按人数排，若一致，按平均分排。

作答试卷得分大于过 80 的人的用户等级分布

描述：

现有用户信息表 user_info（uid 用户 ID，nick_name 昵称, achievement 成就值, level 等级, job 职业方向, register_time 注册时间）：

试卷信息表 examination_info（exam_id 试卷 ID, tag 试卷类别, difficulty 试卷难度, duration 考试时长, release_time 发布时间）：

试卷作答信息表 exam_record（uid 用户 ID, exam_id 试卷 ID, start_time 开始作答时间, submit_time 交卷时间, score 得分）：

统计作答 SQL 类别的试卷得分大于过 80 的人的用户等级分布，按数量降序排序（保证数量都不同）。示例数据结果输出如下：

解释：9001 为 SQL 类试卷，作答该试卷大于 80 分的人有 1002、1003、1005 共 3 人，6 级两人，5 级一人。

思路：这题和上一题都是一样的数据，只是查询条件改变了而已，上一题理解了，这题分分钟做出来。

答案：

1
2
3
4
5
6
7
8
9
10
11
12

SELECT u_info.LEVEL AS LEVEL,
       count(u_info.uid) AS level_cnt
FROM examination_info e_info
INNER JOIN exam_record record
INNER JOIN user_info u_info
WHERE e_info.exam_id = record.exam_id
  AND u_info.uid = record.uid
  AND record.score > 80
  AND submit_time IS NOT NULL
  AND tag = 'SQL'
GROUP BY LEVEL
ORDER BY level_cnt DESC

合并查询

每个题目和每份试卷被作答的人数和次数

描述：

现有试卷作答记录表 exam_record（uid 用户 ID, exam_id 试卷 ID, start_time 开始作答时间, submit_time 交卷时间, score 得分）：

题目练习表 practice_record（uid 用户 ID, question_id 题目 ID, submit_time 提交时间, score 得分）：

请统计每个题目和每份试卷被作答的人数和次数，分别按照”试卷”和”题目”的 uv & pv 降序显示，示例数据结果输出如下：

解释：“试卷”有 3 人共练习 3 次试卷 9001，1 人作答 3 次 9002；“刷题”有 3 人刷 5 次 8001，有 2 人刷 2 次 8002

思路：这题的难点和易错点在于UNION和ORDER BY 同时使用的问题

有以下几种情况：使用union和多个order by不加括号，报错！

order by在union连接的子句中不起作用；

比如不加括号：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

SELECT exam_id AS tid,
       COUNT(DISTINCT UID) AS uv,
       COUNT(UID) AS pv
FROM exam_record
GROUP BY exam_id
ORDER BY uv DESC,
         pv DESC
UNION
SELECT question_id AS tid,
       COUNT(DISTINCT UID) AS uv,
       COUNT(UID) AS pv
FROM practice_record
GROUP BY question_id
ORDER BY uv DESC,
         pv DESC

直接报语法错误，如果没有括号，只能有一个order by

还有一种order by不起作用的情况，但是能在子句的子句中起作用，这里的解决方案就是在外面再套一层查询。

答案：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

SELECT *
FROM
  (SELECT exam_id AS tid,
          COUNT(DISTINCT exam_record.uid) uv,
          COUNT(*) pv
   FROM exam_record
   GROUP BY exam_id
   ORDER BY uv DESC, pv DESC) t1
UNION
SELECT *
FROM
  (SELECT question_id AS tid,
          COUNT(DISTINCT practice_record.uid) uv,
          COUNT(*) pv
   FROM practice_record
   GROUP BY question_id
   ORDER BY uv DESC, pv DESC) t2;

分别满足两个活动的人

描述： 为了促进更多用户在牛客平台学习和刷题进步，我们会经常给一些既活跃又表现不错的用户发放福利。假使以前我们有两拨运营活动，分别给每次试卷得分都能到 85 分的人（activity1）、至少有一次用了一半时间就完成高难度试卷且分数大于 80 的人（activity2）发了福利券。

现在，需要你一次性将这两个活动满足的人筛选出来，交给运营同学。请写出一个 SQL 实现：输出 2021 年里，所有每次试卷得分都能到 85 分的人以及至少有一次用了一半时间就完成高难度试卷且分数大于 80 的人的 id 和活动号，按用户 ID 排序输出。

现有试卷信息表 examination_info（exam_id 试卷 ID, tag 试卷类别, difficulty 试卷难度, duration 考试时长, release_time 发布时间）：

试卷作答记录表 exam_record（uid 用户 ID, exam_id 试卷 ID, start_time 开始作答时间, submit_time 交卷时间, score 得分）：

示例数据输出结果：

解释：用户 1001 最小分数 81 不满足活动 1，但 29 分 59 秒完成了 60 分钟长的试卷得分 81，满足活动 2；1003 最小分数 86 满足活动 1，完成时长都大于试卷时长的一半，不满足活动 2；用户 1004 刚好用了一半时间（30 分钟整）完成了试卷得分 85，满足活动 1 和活动 2。

思路： 这一题需要涉及到时间的减法，需要用到 TIMESTAMPDIFF() 函数计算两个时间戳之间的分钟差值。

下面我们来看一下基本用法

示例：

1

TIMESTAMPDIFF(MINUTE, start_time, end_time)

TIMESTAMPDIFF() 函数的第一个参数是时间单位，这里我们选择 MINUTE 表示返回分钟差值。第二个参数是较早的时间戳，第三个参数是较晚的时间戳。函数会返回它们之间的分钟差值

了解了这个函数的用法之后，我们再回过头来看activity1的要求，求分数大于 85 即可，那我们还是先把这个写出来，后续思路就会清晰很多

1
2
3
4

SELECT DISTINCT UID
FROM exam_record
WHERE score >= 85
  AND YEAR (start_time) = '2021'

根据条件 2，接着写出在一半时间内完成高难度试卷且分数大于80的人

1
2
3
4
5
6
7

SELECT UID
FROM examination_info info
INNER JOIN exam_record record
WHERE info.exam_id = record.exam_id
  AND (TIMESTAMPDIFF(MINUTE, start_time, submit_time)) < (info.duration / 2)
  AND difficulty = 'hard'
  AND score >= 80

然后再把两者UNION 起来即可。（这里特别要注意括号问题和order by位置，具体用法在上一篇中已提及）

答案：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

SELECT DISTINCT UID UID,
                    'activity1' activity
FROM exam_record
WHERE UID not in
    (SELECT UID
     FROM exam_record
     WHERE score<85
       AND YEAR(submit_time) = 2021 )
UNION
SELECT DISTINCT UID UID,
                    'activity2' activity
FROM exam_record e_r
LEFT JOIN examination_info e_i ON e_r.exam_id = e_i.exam_id
WHERE YEAR(submit_time) = 2021
  AND difficulty = 'hard'
  AND TIMESTAMPDIFF(SECOND, start_time, submit_time) <= duration *30
  AND score>80
ORDER BY UID

连接查询

满足条件的用户的试卷完成数和题目练习数（困难）

描述：

现有用户信息表 user_info（uid 用户 ID，nick_name 昵称, achievement 成就值, level 等级, job 职业方向, register_time 注册时间）：

试卷信息表 examination_info（exam_id 试卷 ID, tag 试卷类别, difficulty 试卷难度, duration 考试时长, release_time 发布时间）：

试卷作答记录表 exam_record（uid 用户 ID, exam_id 试卷 ID, start_time 开始作答时间, submit_time 交卷时间, score 得分）：

题目练习记录表 practice_record（uid 用户 ID, question_id 题目 ID, submit_time 提交时间, score 得分）：

请你找到高难度 SQL 试卷得分平均值大于 80 并且是 7 级的红名大佬，统计他们的 2021 年试卷总完成次数和题目总练习次数，只保留 2021 年有试卷完成记录的用户。结果按试卷完成数升序，按题目练习数降序。

示例数据输出如下：

解释：用户 1001、1003、1004、1006 满足高难度 SQL 试卷得分平均值大于 80，但只有 1001、1003 是 7 级红名大佬；1001 完成了 1 次试卷 1001，练习了 2 次题目；1003 完成了 2 次试卷 9001、9002，未练习题目（因此计数为 0）

思路：

先将条件进行初步筛选，比如先查出做过高难度 sql 试卷的用户

1
2
3
4
5
6
7
8
9

SELECT
	record.uid
FROM
	exam_record record
	INNER JOIN examination_info e_info ON record.exam_id = e_info.exam_id
	JOIN user_info u_info ON record.uid = u_info.uid
WHERE
	e_info.tag = 'SQL'
	AND e_info.difficulty = 'hard'

然后根据题目要求，接着再往里叠条件即可；

但是这里又要注意：

第一：不能YEAR(submit_time)= 2021这个条件放到最后，要在ON条件里，因为左连接存在返回左表全部行，右表为 null 的情形，放在 JOIN条件的 ON 子句中的目的是为了确保在连接两个表时，只有满足年份条件的记录会进行连接。这样可以避免其他年份的记录被包含在结果中。即 1001 做过 2021 年的试卷，但没有练习过，如果把条件放到最后，就会排除掉这种情况。

第二，必须是COUNT(distinct er.exam_id) exam_cnt, COUNT(distinct pr.id) question_cnt，要加 distinct，因为有左连接产生很多重复值。

答案：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

SELECT er.uid AS UID,
       count(DISTINCT er.exam_id) AS exam_cnt,
       count(DISTINCT pr.id) AS question_cnt
FROM exam_record er
LEFT JOIN practice_record pr ON er.uid = pr.uid
AND YEAR (er.submit_time)= 2021
AND YEAR (pr.submit_time)= 2021
WHERE er.uid IN
    (SELECT er.uid
     FROM exam_record er
     LEFT JOIN examination_info ei ON er.exam_id = ei.exam_id
     LEFT JOIN user_info ui ON er.uid = ui.uid
     WHERE tag = 'SQL'
       AND difficulty = 'hard'
       AND LEVEL = 7
     GROUP BY er.uid
     HAVING avg(score) > 80)
GROUP BY er.uid
ORDER BY exam_cnt,
         question_cnt DESC

可能细心的小伙伴会发现，为什么明明将条件限制了tag = 'SQL' AND difficulty = 'hard'，但是用户 1003 仍然能查出两条考试记录，其中一条的考试tag为 C++; 这是由于LEFT JOIN的特性，即使没有与右表匹配的行，左表的所有记录仍然会被保留。

每个 6/7 级用户活跃情况（困难）

描述：

现有用户信息表 user_info（uid 用户 ID，nick_name 昵称, achievement 成就值, level 等级, job 职业方向, register_time 注册时间）：

试卷信息表 examination_info（exam_id 试卷 ID, tag 试卷类别, difficulty 试卷难度, duration 考试时长, release_time 发布时间）：

试卷作答记录表 exam_record（uid 用户 ID, exam_id 试卷 ID, start_time 开始作答时间, submit_time 交卷时间, score 得分）：

题目练习记录表 practice_record（uid 用户 ID, question_id 题目 ID, submit_time 提交时间, score 得分）：

请统计每个 6/7 级用户总活跃月份数、2021 年活跃天数、2021 年试卷作答活跃天数、2021 年答题活跃天数，按照总活跃月份数、2021 年活跃天数降序排序。由示例数据结果输出如下：

解释：6/7 级用户共有 5 个，其中 1006 在 202109、202108、202008 共 3 个月活跃过，2021 年活跃的日期有 20210907、20210804、20210803、20210802 共 4 天，2021 年在试卷作答区 20210907 活跃 1 天，在题目练习区活跃了 3 天。

思路：

这题的关键在于CASE WHEN THEN的使用，不然要写很多的left join 因为会产生很多的结果集。

CASE WHEN THEN语句是一种条件表达式，用于在 SQL 中根据条件执行不同的操作或返回不同的结果。

语法结构如下：

1
2
3
4
5
6

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE result
END

在这个结构中，可以根据需要添加多个WHEN子句，每个WHEN子句后面跟着一个条件（condition）和一个结果（result）。条件可以是任何逻辑表达式，如果满足条件，将返回对应的结果。

最后的ELSE子句是可选的，用于指定当所有前面的条件都不满足时的默认返回结果。如果没有提供ELSE子句，则默认返回NULL。

例如：

1
2
3
4
5
6
7
8

SELECT score,
    CASE
        WHEN score >= 90 THEN '优秀'
        WHEN score >= 80 THEN '良好'
        WHEN score >= 60 THEN '及格'
        ELSE '不及格'
    END AS grade
FROM student_scores;

在上述示例中，根据学生成绩（score）的不同范围，使用 CASE WHEN THEN 语句返回相应的等级（grade）。如果成绩大于等于 90，则返回”优秀”；如果成绩大于等于 80，则返回”良好”；如果成绩大于等于 60，则返回”及格”；否则返回”不及格”。

那了解到了上述的用法之后，回过头看看该题，要求列出不同的活跃天数。

1
2
3
4

count(distinct act_month) as act_month_total,
count(distinct case when year(act_time)='2021'then act_day end) as act_days_2021,
count(distinct case when year(act_time)='2021' and tag='exam' then act_day end) as act_days_2021_exam,
count(distinct case when year(act_time)='2021' and tag='question'then act_day end) as act_days_2021_question

这里的 tag 是先给标记，方便对查询进行区分，将考试和答题分开。

找出试卷作答区的用户

1
2
3
4
5
6
7
8
9

SELECT
		uid,
		exam_id AS ans_id,
		start_time AS act_time,
		date_format( start_time, '%Y%m' ) AS act_month,
		date_format( start_time, '%Y%m%d' ) AS act_day,
		'exam' AS tag
	FROM
		exam_record

紧接着就是答题作答区的用户

1
2
3
4
5
6
7
8
9

SELECT
		uid,
		question_id AS ans_id,
		submit_time AS act_time,
		date_format( submit_time, '%Y%m' ) AS act_month,
		date_format( submit_time, '%Y%m%d' ) AS act_day,
		'question' AS tag
	FROM
		practice_record

最后将两个结果进行UNION 最后别忘了将结果进行排序 （这题有点类似于分治法的思想）

答案：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

SELECT user_info.uid,
       count(DISTINCT act_month) AS act_month_total,
       count(DISTINCT CASE
                          WHEN YEAR (act_time)= '2021' THEN act_day
                      END) AS act_days_2021,
       count(DISTINCT CASE
                          WHEN YEAR (act_time)= '2021'
                               AND tag = 'exam' THEN act_day
                      END) AS act_days_2021_exam,
       count(DISTINCT CASE
                          WHEN YEAR (act_time)= '2021'
                               AND tag = 'question' THEN act_day
                      END) AS act_days_2021_question
FROM
  (SELECT UID,
          exam_id AS ans_id,
          start_time AS act_time,
          date_format(start_time, '%Y%m') AS act_month,
          date_format(start_time, '%Y%m%d') AS act_day,
          'exam' AS tag
   FROM exam_record
   UNION ALL SELECT UID,
                    question_id AS ans_id,
                    submit_time AS act_time,
                    date_format(submit_time, '%Y%m') AS act_month,
                    date_format(submit_time, '%Y%m%d') AS act_day,
                    'question' AS tag
   FROM practice_record) total
RIGHT JOIN user_info ON total.uid = user_info.uid
WHERE user_info.LEVEL IN (6,
                          7)
GROUP BY user_info.uid
ORDER BY act_month_total DESC,
         act_days_2021 DESC

Vue.js 基础知识与特性

1. 简单使用总结

1.1 使用 Vue 实例管理 DOM

• 核心思想：Vue 实例将页面元素和数据联系起来，通过双向绑定机制，使开发者只需关注数据和事件的处理，无需手动修改视图。
• DOM 与数据/事件绑定：Vue 提供了简洁的语法来实现数据绑定和事件监听，使得开发更加高效。

1.2 Vue 实例的工作过程

1. HTML 加载：首先加载 HTML 文件，包括容器（如 <div>）。
2. Vue 初始化：创建 Vue 实例时，Vue 会解析绑定的容器中的模板，生成处理后的新容器，并替换原有的容器。
3. 模板解析：容器中的代码成为模板，Vue 通过 {{}} 占位符进行插值，类似于 MyBatis 中的 ${} 和 #{}。
4. 容器与实例关系：容器和 Vue 实例是一对一的关系。在真实开发中，通常只会有一个 Vue 实例，并且会配合组件一起使用。

1.3 模板语法

• 插值：用于插入数据到标签内容中，如 {{ message }}。
• 指令：用于绑定属性、事件等，所有指令都以 v- 开头，例如 v-bind、v-on。

2. 组件化编程

2.1 组件的本质

• 组件是 Vue 实例：组件本质上是一个 Vue 实例，因此它可以接收 data、methods、生命周期函数等选项。
• 无 el 属性：组件不会直接与页面元素绑定，否则无法复用。因此，组件没有 el 属性。
• template 属性：组件需要一个 HTML 模板，因此增加了 template 属性，其值为 HTML 模板。

2.2 组件定义

• 全局组件：定义完毕后，任何 Vue 实例都可以直接在 HTML 中通过组件名称来使用组件。
• 局部组件：仅在特定的 Vue 实例或父组件中注册和使用。

2.3 数据与方法

• data 必须是函数：为了确保每个组件实例都有独立的数据副本，data 必须是一个返回对象的函数，而不是直接的对象。
• 原型链：组件的构造函数（如 VueComponent）具有 prototype 属性，实例对象（如 vc）则通过 __proto__ 隐式引用构造函数的原型对象。

2.4 示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

<!-- 全局组件 -->
<template>
  <div>
    <h1>{{ title }}</h1>
    <p>{{ content }}</p>
  </div>
</template>

<script>
export default {
  name: 'MyComponent',
  data() {
    return {
      title: 'Hello Vue!',
      content: 'This is a Vue component.'
    };
  },
  methods: {
    greet() {
      alert('Hello!');
    }
  }
};
</script>

3. 生命周期

3.1 生命周期钩子

每个 Vue 实例在被创建时都要经过一系列的初始化过程，Vue 为生命周期中的每个状态都设置了钩子函数（监听函数）。每当 Vue 实例处于不同的生命周期阶段时，对应的函数就会被触发调用。

3.2 常见钩子函数

3.3 生命周期图示

1

beforeCreate → created → beforeMount → mounted → beforeUpdate → updated → beforeDestroy → destroyed

4. 运行流程

4.1 页面加载

1. 进入页面：首先加载 index.html 和 main.js 文件。
2. 导入模块：main.js 导入了 vue、app、router 等模块，并创建 Vue 实例，关联 index.html 页面的元素。
3. 使用路由：main.js 导入了 App 组件，并通过 <router-view> 标签引用该组件。
4. 默认显示：第一次访问时，默认显示 App 组件。App 组件包含一个图片和一个代表路由视图的 <router-view>。由于路由路径默认使用 HASH 模式（/#/），因此会显示 HelloWorld 组件。

4.2 组件跳转

• 自定义组件：可以尝试自己编写一个组件，并将其加入路由配置中。
• 点击跳转：通过点击按钮或其他方式，可以触发路由跳转，显示不同的组件。

4.3 示例

1
2
3
4
5
6
7
8
9
10
11
12
13

<!-- index.html -->
<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Vue App</title>
</head>
<body>
  <div id="app"></div>
  <script src="/dist/main.js"></script>
</body>
</html>

1
2
3
4
5
6
7
8
9

// main.js
import Vue from 'vue';
import App from './App.vue';
import router from './router';

new Vue({
  router,
  render: h => h(App)
}).$mount('#app');

1
2
3
4
5
6
7
8
9
10
11
12
13
14

// router/index.js
import Vue from 'vue';
import Router from 'vue-router';
import HelloWorld from '@/components/HelloWorld.vue';
import MyComponent from '@/components/MyComponent.vue';

Vue.use(Router);

export default new Router({
  routes: [
    { path: '/', component: HelloWorld },
    { path: '/my-component', component: MyComponent }
  ]
});

1
2
3
4
5
6
7
8
9
10
11
12
13

<!-- App.vue -->
<template>
  <div>
    <img src="@/assets/logo.png" alt="Logo">
    <router-view></router-view>
  </div>
</template>

<script>
export default {
  name: 'App'
};
</script>

1
2
3
4
5
6
7
8
9
10
11
12

<!-- HelloWorld.vue -->
<template>
  <div>
    <h1>Hello World!</h1>
  </div>
</template>

<script>
export default {
  name: 'HelloWorld'
};
</script>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

<!-- MyComponent.vue -->
<template>
  <div>
    <h1>My Custom Component</h1>
    <button @click="goToHome">Go to Home</button>
  </div>
</template>

<script>
export default {
  name: 'MyComponent',
  methods: {
    goToHome() {
      this.$router.push('/');
    }
  }
};
</script>

通用思想：

按照和是否等于k的情况，可以分为***++查找问题、搜索问题++***（本质上是一样的），因此可以用++特殊数据结构来存储和查找（哈希表、前缀数组）、暴力搜索、递归、分治，回溯法其实就是暴力法，进一步地记忆化搜索、动态规划来处理++。

先考虑该问题是否可以用特殊的数据结构来解决，再考虑算法。

更进一步地，按照题目给定数组的条件，使用滑动窗口、双指针、二分法都可以来解决，通常辅以前缀数组、后缀数组会大大简化。

通过前缀数组，区间和可以转换为两个前缀数组的差。

0-1背包问题就是动态规划的运用。

注意数组中是否有负数的情况又不一样，数组中均为正数的情况，会更简单些，往往可以直接用二分或者滑动窗口。

有负数时，可以考虑用单调栈、队列等辅助的数据结构，来进行滑动窗口。（leetcode862）

和为k的子数组：

相对于原问题更简单，附加了连续性，即缩小了范围，增加了遍历条件线索。

方法：

双层循环O(N2)

前缀和+map。这个表对应的映射项可以是最早出现这个sum的index（以此来求最长子数组的长度），也可以是对应这个sum出现的次数（对应求满足条件的子数组个数）。

变式：

在原问题基础上，增加了数组中的值均为正数的约束，因此数组和天然存在了递增的特性，可以采用滑动窗口的思想。

子数组和小于等于定值

子数组中均为正整数：

子数组中有负数：

这个题目应该是求和小于等于K的最长子数组。按理说，存在负数的话，是不能使用滑动窗口的，但是下面的解法借助两个辅助数组

使用两个辅助数组：min_value、min_index：min_value[i]表示以i位置开始往后加的最小累加和；min_index表示min_value对应的最小累加和的右边界；这两个辅助数组是能够在O(n)时间复杂内计算出来的：倒序遍历，min_value[i] 只需要判断min_value[i+1]的值是不是负数，如果是负数就加上，不是就到本身这里结尾。得到这样一个数组以后我们就可能轻易得到从某一个位置开始和最小的子数组。

有了这两个辅助数组以后，就可以采用滑动窗口的思想，左右两个指针都不回退，右指针以上面辅助数组进行累加，左指针正常遍历，使得总体的时间复杂度为O(n)，参考LeetCode总结——和为定值 | Sixzeroo (liuin.cn)

题目来源于：牛客题霸 - SQL 必知必会

检索数据

SELECT 用于从数据库中查询数据。

从 Customers 表中检索所有的 ID

现有表 Customers 如下：

编写 SQL 语句，从 Customers 表中检索所有的 cust_id。

答案：

1
2

SELECT cust_id
FROM Customers

检索并列出已订购产品的清单

表 OrderItems 含有非空的列 prod_id 代表商品 id，包含了所有已订购的商品（有些已被订购多次）。

编写 SQL 语句，检索并列出所有已订购商品（prod_id）的去重后的清单。

答案：

1
2

SELECT DISTINCT prod_id
FROM OrderItems

知识点：DISTINCT 用于返回列中的唯一不同值。

检索所有列

现在有 Customers 表（表中含有列 cust_id 代表客户 id，cust_name 代表客户姓名）

需要编写 SQL 语句，检索所有列。

答案：

1
2

SELECT cust_id, cust_name
FROM Customers

排序检索数据

ORDER BY 用于对结果集按照一个列或者多个列进行排序。默认按照升序对记录进行排序，如果需要按照降序对记录进行排序，可以使用 DESC 关键字。

检索顾客名称并且排序

有表 Customers，cust_id 代表客户 id，cust_name 代表客户姓名。

从 Customers 中检索所有的顾客名称（cust_name），并按从 Z 到 A 的顺序显示结果。

答案：

1
2
3

SELECT cust_name
FROM Customers
ORDER BY cust_name DESC

对顾客 ID 和日期排序

有 Orders 表：

编写 SQL 语句，从 Orders 表中检索顾客 ID（cust_id）和订单号（order_num），并先按顾客 ID 对结果进行排序，再按订单日期倒序排列。

答案：

1
2
3
4
5

# 根据列名排序
# 注意：是 order_date 降序，而不是 order_num
SELECT cust_id, order_num
FROM Orders
ORDER BY cust_id,order_date DESC

知识点：order by 对多列排序的时候，先排序的列放前面，后排序的列放后面。并且，不同的列可以有不同的排序规则。

按照数量和价格排序

假设有一个 OrderItems 表：

编写 SQL 语句，显示 OrderItems 表中的数量（quantity）和价格（item_price），并按数量由多到少、价格由高到低排序。

答案：

1
2
3

SELECT quantity, item_price
FROM OrderItems
ORDER BY quantity DESC,item_price DESC

检查 SQL 语句

有 Vendors 表：

下面的 SQL 语句有问题吗？尝试将它改正确，使之能够正确运行，并且返回结果根据vend_name 逆序排列。

1
2
3

SELECT vend_name,
FROM Vendors
ORDER vend_name DESC

改正后：

1
2
3

SELECT vend_name
FROM Vendors
ORDER BY vend_name DESC

知识点：

• 逗号作用是用来隔开列与列之间的。
• ORDER BY 是有 BY 的，需要撰写完整，且位置正确。

过滤数据

WHERE 可以过滤返回的数据。

下面的运算符可以在 WHERE 子句中使用：

返回固定价格的产品

有表 Products：

【问题】从 Products 表中检索产品 ID（prod_id）和产品名称（prod_name），只返回价格为 9.49 美元的产品。

答案：

1
2
3

SELECT prod_id, prod_name
FROM Products
WHERE prod_price = 9.49

返回更高价格的产品

有表 Products：

【问题】编写 SQL 语句，从 Products 表中检索产品 ID（prod_id）和产品名称（prod_name），只返回价格为 9 美元或更高的产品。

答案：

1
2
3

SELECT prod_id, prod_name
FROM Products
WHERE prod_price >= 9

返回产品并且按照价格排序

有表 Products：

【问题】编写 SQL 语句，返回 Products 表中所有价格在 3 美元到 6 美元之间的产品的名称（prod_name）和价格（prod_price），然后按价格对结果进行排序。

答案：

1
2
3
4
5
6
7
8
9
10

SELECT prod_name, prod_price
FROM Products
WHERE prod_price BETWEEN 3 AND 6
ORDER BY prod_price

# 或者
SELECT prod_name, prod_price
FROM Products
WHERE prod_price >= 3 AND prod_price <= 6
ORDER BY prod_price

返回更多的产品

OrderItems 表含有：订单号 order_num，quantity产品数量

【问题】从 OrderItems 表中检索出所有不同且不重复的订单号（order_num），其中每个订单都要包含 100 个或更多的产品。

答案：

1
2
3
4

SELECT order_num
FROM OrderItems
GROUP BY order_num
HAVING SUM(quantity) >= 100

高级数据过滤

AND 和 OR 运算符用于基于一个以上的条件对记录进行过滤，两者可以结合使用。AND 必须 2 个条件都成立，OR只要 2 个条件中的一个成立即可。

检索供应商名称

Vendors 表有字段供应商名称（vend_name）、供应商国家（vend_country）、供应商州（vend_state）

【问题】编写 SQL 语句，从 Vendors 表中检索供应商名称（vend_name），仅返回加利福尼亚州的供应商（这需要按国家[USA]和州[CA]进行过滤，没准其他国家也存在一个 CA）

答案：

1
2
3

SELECT vend_name
FROM Vendors
WHERE vend_country = 'USA' AND vend_state = 'CA'

检索并列出已订购产品的清单

OrderItems 表包含了所有已订购的产品（有些已被订购多次）。

【问题】编写 SQL 语句，查找所有订购了数量至少 100 个的 BR01、BR02 或 BR03 的订单。你需要返回 OrderItems 表的订单号（order_num）、产品 ID（prod_id）和数量（quantity），并按产品 ID 和数量进行过滤。

答案：

1
2
3

SELECT order_num, prod_id, quantity
FROM OrderItems
WHERE prod_id IN ('BR01', 'BR02', 'BR03') AND quantity >= 100

返回所有价格在 3 美元到 6 美元之间的产品的名称和价格

有表 Products：

【问题】编写 SQL 语句，返回所有价格在 3 美元到 6 美元之间的产品的名称（prod_name）和价格（prod_price），使用 AND 操作符，然后按价格对结果进行升序排序。

答案：

1
2
3
4

SELECT prod_name, prod_price
FROM Products
WHERE prod_price >= 3 and prod_price <= 6
ORDER BY prod_price

检查 SQL 语句

供应商表 Vendors 有字段供应商名称 vend_name、供应商国家 vend_country、供应商省份 vend_state

【问题】修改正确下面 sql，使之正确返回。

1
2
3
4

SELECT vend_name
FROM Vendors
ORDER BY vend_name
WHERE vend_country = 'USA' AND vend_state = 'CA';

修改后：

1
2
3
4

SELECT vend_name
FROM Vendors
WHERE vend_country = 'USA' AND vend_state = 'CA'
ORDER BY vend_name

ORDER BY 语句必须放在 WHERE 之后。

用通配符进行过滤

SQL 通配符必须与 LIKE 运算符一起使用

在 SQL 中，可使用以下通配符：

检索产品名称和描述（一）

Products 表如下：

【问题】编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中包含 toy 一词的产品名称。

答案：

1
2
3

SELECT prod_name, prod_desc
FROM Products
WHERE prod_desc LIKE '%toy%'

检索产品名称和描述（二）

Products 表如下：

【问题】编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中未出现 toy 一词的产品，最后按”产品名称“对结果进行排序。

答案：

1
2
3
4

SELECT prod_name, prod_desc
FROM Products
WHERE prod_desc NOT LIKE '%toy%'
ORDER BY prod_name

检索产品名称和描述（三）

Products 表如下：

【问题】编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中同时出现 toy 和 carrots 的产品。有好几种方法可以执行此操作，但对于这个挑战题，请使用 AND 和两个 LIKE 比较。

答案：

1
2
3

SELECT prod_name, prod_desc
FROM Products
WHERE prod_desc LIKE '%toy%' AND prod_desc LIKE "%carrots%"

检索产品名称和描述（四）

Products 表如下：