一些简单高级的 SQL 查询技巧记录

张彦峰ZYF

已于 2024-05-04 17:30:07 修改

阅读量2.1w

点赞数

分类专栏： MySQL基本学习与应用文章标签： mysql

于 2023-10-21 23:55:13 首次发布

本文链接：https://blog.csdn.net/xiaofeng10330111/article/details/133967931

版权

MySQL基本学习与应用专栏收录该内容

16 篇文章 23 订阅

订阅专栏

练习二：使用 CTE 来分解复杂的查询逻辑

五、了解 EXCEPT 和 NOT IN 操作符

（一）快速了解

（二）上手演练

六、自联结（Self-Join）

七、排名函数：ROW_NUMBER、RANK 和 DENSE_RANK

（一）快速了解

（二）上手演练

八、计算不同时期之间的差值（Delta 值）

一、常用表表达式（CTEs）

（一）快速了解

常用表表达式（CTE）允许在查询中创建临时的命名结果集。CTE 通过使用 WITH 关键字定义，并在查询中重复引用，使得复杂的查询可以更清晰、更易读地表达。CTE 主要由两部分组成：WITH 子句和查询语句。

WITH 子句包含一个或多个以逗号分隔的命名子查询，每个子查询都有一个名称和一个查询。这些子查询可以在后续的查询语句中被引用。
CTE 的查询语句紧随其后，使用这些命名的子查询执行实际的数据检索、过滤、聚合等操作。

CTE 的主要优势在于它能够将复杂的查询逻辑分解为简单的部分，并且可以重用已定义的子查询，从而提高查询的可读性和可维护性。此外，CTE 还可以用于实现递归查询、数据转换、逻辑分割等高级操作，使得 SQL 查询更加灵活、强大。

（二）上手演练

练习一：使用 CTE 来分解一般查询逻辑

假设我们有三个表：employees（员工表）、departments（部门表）和 salaries（工资表）。employees 表包含了员工的基本信息，departments 表包含了部门的信息，salaries 表包含了员工的薪水信息。

我们的目标是找出在销售部门（Sales）工作的女性员工的平均薪水，并列出选择薪水高于平均薪水姓名和薪水。

当不使用 CTE 时，可以使用子查询来实现

我们首先在 employees 表中选择在销售部门工作且性别为女性的员工。然后，我们使用子查询来计算这些女性员工的平均薪水，并将其与每个员工的薪水进行比较。最终，我们仅列出薪水高于平均薪水的员工的姓名和薪水。

SELECT name, salary
FROM employees
WHERE department_id = (
    SELECT department_id FROM departments WHERE name = 'Sales'
    )
AND gender = 'Female'
AND salary >= (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = (
        SELECT department_id FROM departments WHERE name = 'Sales'
        )
        AND gender = 'Female'
);

使用 CTE 来分解复杂的查询逻辑后

我们首先创建了一个 CTE sales_department选择销售部门的所有员工。然后，我们使用另一个 CTE female_sales_employees 来选择在销售部门工作的女性员工，并获取他们的姓名和薪水。接着，我们计算了这些女性员工的平均薪水，并将其存储在 avg_female_sales_salary CTE 中。最后，我们从 female_sales_employees 中选择薪水高于平均薪水的员工，并列出他们的姓名和薪水。

WITH sales_department AS (
    SELECT employee_id
    FROM employees
    WHERE department_id = (
        SELECT department_id FROM departments WHERE name = 'Sales'
    )
),
female_sales_employees AS (
    SELECT employee_id, name, salary
    FROM employees
    JOIN sales_department 
    ON employees.employee_id = sales_department.employee_id
    WHERE gender = 'Female'
),
avg_female_sales_salary AS (
    SELECT AVG(salary) AS avg_salary
    FROM female_sales_employees
)

SELECT name, salary
FROM female_sales_employees
WHERE salary >= (SELECT avg_salary FROM avg_female_sales_salary);

练习二：使用 CTE 来分解复杂的查询逻辑

假设我们不仅想要找出在销售部门工作的薪水高于平均薪水的女性员工，还想要找出他们的平均工作年限，并列出那些工作年限超过平均工作年限的员工的姓名、薪水和工作年限。

当不使用 CTE 时，可以使用子查询来实现

首先在子查询中计算了销售部门中女性员工的平均薪水和平均工作年限，然后主查询选择了在销售部门工作且性别为女性的员工，并筛选出薪水高于平均薪水且工作年限大于平均工作年限的员工，并列出他们的姓名、薪水和工作年限。

SELECT 
    name, 
    salary, 
    EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM hire_date) AS years_worked
FROM 
    employees e
JOIN 
    (
        SELECT 
            AVG(salary) AS avg_salary,
            AVG(EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM hire_date)) AS avg_years_worked
        FROM 
            employees
        WHERE 
            department_id = (
                SELECT department_id FROM departments WHERE name = 'Sales'
            )
            AND gender = 'Female'
    ) AS avg_stats ON 1=1 -- 确保每行都能和avg_stats匹配上
WHERE 
    department_id = (
        SELECT department_id FROM departments WHERE name = 'Sales'
    )
    AND gender = 'Female'
    AND salary >= (SELECT avg_salary FROM avg_stats)
    AND EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM hire_date) > (SELECT avg_years_worked FROM avg_stats);

使用 CTE 来分解复杂的查询逻辑后

我们首先计算每位女性销售员工的平均工作年限和平均薪水，并将其存储在 avg_female_sales_stats CTE 中。然后，在主查询中，我们除了列出员工的姓名、薪水和工作年限外，还筛选出了工作年限超过平均工作年限的员工。

WITH sales_department AS (
    SELECT employee_id
    FROM employees
    WHERE department_id = (
        SELECT department_id FROM departments WHERE name = 'Sales'
    )
),
female_sales_employees AS (
    SELECT employee_id, name, salary, hire_date
    FROM employees
    JOIN sales_department 
    ON employees.employee_id = sales_department.employee_id
    WHERE gender = 'Female'
),
avg_female_sales_stats AS (
    SELECT 
        AVG(salary) AS avg_salary,
        AVG(EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM hire_date)) AS avg_years_worked
    FROM female_sales_employees
)

SELECT 
    name, 
    salary, 
    EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM hire_date) AS years_worked
FROM 
    female_sales_employees
JOIN 
    avg_female_sales_stats ON 1=1 -- 确保每行都能和avg_female_sales_stats匹配上
WHERE 
    salary >= (SELECT avg_salary FROM avg_female_sales_stats)
    AND EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM hire_date) > (SELECT avg_years_worked FROM avg_female_sales_stats);

二、递归CTEs

（一）快速了解

递归CTE类似于Python中的递归函数，适用于处理组织结构图、文件系统、网页链接图等层次化数据。递归CTE通常包含三个部分：

锚定成员（Anchor Member）：递归CTE的起始部分，返回CTE的基本结果的初始查询。
递归成员（Recursive Member）：引用了CTE自身，是所有递归查询的联合体。
终止条件（Termination Condition）：可选部分，用于指定递归查询何时停止。

（二）上手演练

假设我们有一个名为employees的表，其中包含了员工的ID、姓名和直接上级的ID。我们想要查询每个员工及其所有下属的层级结构：

WITH RECURSIVE EmployeeHierarchy AS (
    -- 锚定成员：查询根节点（没有直接上级的员工）
    SELECT 
        employee_id,
        name,
        manager_id,
        1 AS level
    FROM 
        employees
    WHERE 
        manager_id IS NULL -- 根节点没有直接上级
    
    UNION ALL
    
    -- 递归成员：连接直接下属和它们的下属
    SELECT 
        e.employee_id,
        e.name,
        e.manager_id,
        eh.level + 1 -- 层级加一
    FROM 
        employees e
    JOIN 
        EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
-- 查询结果：包含每个员工及其所有下属的层级结构
SELECT * 
FROM EmployeeHierarchy;

递归CTE EmployeeHierarchy 包含了两个部分：

锚定成员部分选择了根节点，即没有直接上级的员工。
递归成员部分连接了直接下属和它们的下属，并递归地获取了所有下属员工的层级关系。

递归查询会持续地向下遍历员工的层级结构，直到所有的下属都被检索到为止。

三、利用临时函数简化查询逻辑

（一）快速了解

在处理数据时，简洁而易读的查询语句是提高工作效率和代码可维护性的关键。然而，随着查询逻辑的复杂化，常常会导致查询语句变得冗长且难以理解。

临时函数的引入为解决这一问题提供了一种简洁而有效的方法：

简化查询语句：通过使用临时函数，可以将复杂的逻辑封装在函数内部，使查询语句更简单明了。
提高可读性：使用临时函数可以提高查询的可读性，使其更容易理解和维护。
重复使用性：一旦定义了临时函数，您可以在多个查询中重复使用它，从而提高了代码的重用性和可维护性。

通过将复杂的逻辑封装在临时函数中，我们可以使查询语句更加简洁明了，易于理解和维护，不仅提高了查询的可读性，还提高了代码的重用性和灵活性，使得数据处理工作更加高效和便捷。

（二）上手演练

假设我们有一个需求是将员工的薪水按照特定比例进行调整，这个比例是根据员工的职务级别来确定的。我们想要根据不同的职务级别调整员工的薪水，并且希望查询语句简洁明了，易于维护。

首先，让我们看一下不使用临时函数的情况下的查询语句：

SELECT 
    name,
    CASE 
        WHEN position = 'Manager' THEN salary * 1.1
        WHEN position = 'Supervisor' THEN salary * 1.05
        ELSE salary
    END AS adjusted_salary
FROM 
    employees;

这种方法虽然可以实现功能，但是在查询语句中嵌入了逻辑，使得语句变得冗长且难以理解。而且，如果要添加新的职务级别或者调整现有的比例，就需要修改查询语句，增加了维护的难度。

接下来，我们使用临时函数来实现同样的功能：

CREATE TEMPORARY FUNCTION adjust_salary(position STRING, salary FLOAT64) AS (
    CASE 
        WHEN position = 'Manager' THEN salary * 1.1
        WHEN position = 'Supervisor' THEN salary * 1.05
        ELSE salary
    END
);

SELECT 
    name,
    adjust_salary(position, salary) AS adjusted_salary
FROM 
    employees;

使用临时函数，我们将调整薪水的逻辑封装在一个函数中。这样，查询语句变得简洁明了，易于理解和维护。如果需要修改调整逻辑，只需修改临时函数的定义，而不需要修改每个查询语句。此外，我们还可以在其他查询中重复使用这个临时函数，提高了代码的重用性。

四、数据的枢转（Pivot）

（一）快速了解

在SQL查询中，数据的枢转（Pivot）可以使数据从行形式转换为列形式，从而更加直观和易于分析。利用CASE WHEN语句方式来实现数据的枢转，我们根据特定的条件动态地将数据重新组织成新的列，为数据分析提供了更多的可能性。

（二）上手演练

假设我们有一个销售数据表格，记录了不同产品在不同月份的销售额。我们想要将这些数据进行枢转，以便每个月份都有一个单独的列来显示销售额。

首先，让我们看一下原始的销售数据表格：

Product	Month	Sales
A	Jan	100
B	Jan	200
A	Feb	150
B	Feb	250
A	Mar	120
B	Mar	220

我们想要将上面的表格转换成以下形式，以便每个月份都有一个单独的列来显示销售额：

Product	Jan_Sales	Feb_Sales	Mar_Sales
A	100	150	120
B	200	250	220

现在让我们使用CASE WHEN语句进行数据的枢转：

SELECT 
    Product,
    SUM(CASE WHEN Month = 'Jan' THEN Sales END) AS Jan_Sales,
    SUM(CASE WHEN Month = 'Feb' THEN Sales END) AS Feb_Sales,
    SUM(CASE WHEN Month = 'Mar' THEN Sales END) AS Mar_Sales
FROM 
    SalesData
GROUP BY 
    Product;

我们使用了CASE WHEN语句来根据月份条件对销售额进行分类，并将每个月份的销售额分别计算求和。最后，通过GROUP BY子句按产品进行分组，以得到每个产品在不同月份的销售额总和。

这样，我们就成功地利用CASE WHEN语句进行了数据的枢转，将原始表格中的行数据转换为了列数据，得到了更直观和易于分析的结果表格。

五、了解 EXCEPT 和 NOT IN 操作符

（一）快速了解

在SQL中，我们经常需要比较两个查询或表之间的数据，以找出它们之间的差异或相似之处。EXCEPT和NOT IN是两个常用的操作符，用于实现这样的比较。尽管它们的作用相似，但在使用时需要注意一些微妙的差别。

EXCEPT 操作符

定义： EXCEPT操作符用于从第一个查询结果中过滤掉在第二个查询结果中也出现的行，并返回仅在第一个结果中出现的行。
特点：
- 自动删除重复行，因此只返回不同的行。
- 不需要手动指定比较的列，会对两个查询结果的所有列进行比较。

示例：

SELECT column1, column2 FROM table1 
EXCEPT 
SELECT column1, column2 FROM table2;

NOT IN 操作符：

定义： NOT IN操作符用于比较两个查询结果中的特定列，并返回在第一个查询结果中出现但在第二个查询结果中不存在的值。
特点：
- 需要手动指定要比较的列。
- 不会自动删除重复行。

示例：

SELECT column1, column2 
FROM table1 
WHERE 
column1 NOT IN (SELECT column1 FROM table2);

总结： 虽然EXCEPT和NOT IN都用于比较查询结果，但在使用时需要根据具体的需求选择合适的操作符。如果想要返回不同的行并自动删除重复行，可以使用EXCEPT操作符；如果想要比较特定列并手动指定条件，则应使用NOT IN操作符。

（二）上手演练

假设我们有两个表格：一个是所有员工的表格，另一个是所有经理的表格。我们想要找出所有不是经理的员工。我们可以使用EXCEPT和NOT IN操作符来实现这个任务。

员工表格（employees）：

EmployeeID	Name
1	Alice
2	Bob
3	Charlie
4	David

经理表格（managers）：

ManagerID	Name
3	Charlie
4	David

现在，我们想要找出所有不是经理的员工。我们可以使用EXCEPT操作符来实现：

SELECT Name
FROM employees
EXCEPT
SELECT Name
FROM managers;

这将返回以下结果：

Name
Alice
Bob

另一种方法是使用NOT IN操作符：

SELECT Name
FROM employees
WHERE EmployeeID NOT IN (SELECT ManagerID FROM managers);

这也将返回相同的结果：

Name
Alice
Bob

通常来说，NOT IN 操作符在处理大型数据集时可能会比 EXCEPT 操作符更有效率。这是因为 NOT IN 操作符可以利用索引来快速定位和过滤结果集中的匹配值，而 EXCEPT 操作符需要对两个结果集执行排序和去重操作，这可能会增加查询的执行时间，特别是当数据量较大时。

六、自联结（Self-Join）

自联结（Self-Join）是指在同一个表格中进行连接操作。虽然听起来有些奇怪，但在实际应用中它是非常常见的，特别是当所有相关数据都存储在同一个表格中时。

假设我们有一个名为 "employees" 的表，包含员工的姓名、工资和直接经理的ID。我们想要找出工资高于其直接经理的员工。以下是这个表的示例数据：

EmployeeID	Name	Salary	ManagerID
1	Alice	60000	3
2	Bob	70000	3
3	Charlie	80000	4
4	David	90000	NULL

现在，我们需要编写一个 SQL 查询来找出工资高于其直接经理的员工。我们可以使用自联结来解决这个问题：

SELECT e1.Name AS EmployeeName, e1.Salary AS EmployeeSalary, 
       e2.Name AS ManagerName, e2.Salary AS ManagerSalary
FROM employees e1
JOIN employees e2 ON e1.ManagerID = e2.EmployeeID
WHERE e1.Salary > e2.Salary;

我们首先将 "employees" 表自联结为两个不同的别名 "e1" 和 "e2"，分别代表员工和其直接经理。然后，我们使用 JOIN 条件将员工表中的每个员工与其直接经理进行连接。最后，通过 WHERE 子句筛选出工资高于其直接经理的员工，并返回员工的姓名、工资以及直接经理的姓名和工资。

通过这样的自联结，我们可以清晰地找出工资高于其直接经理的员工，这在实际工作中是一个常见的需求。

七、排名函数：ROW_NUMBER、RANK 和 DENSE_RANK

（一）快速了解

当我们需要在 SQL 中对数据进行排名时，我们可以使用三种常见的排名函数：ROW_NUMBER、RANK 和 DENSE_RANK：

函数	特点	示例
ROW_NUMBER	为每行分配唯一的数字，不考虑行之间的关系	`ROW_NUMBER() OVER (ORDER BY column_name)`
RANK	为每行分配唯一的数字，相同值会跳过排名	`RANK() OVER (ORDER BY column_name)`
DENSE_RANK	为每行分配唯一的数字，相同值不会跳过排名	`DENSE_RANK() OVER (ORDER BY column_name)`

如果你需要为每行分配唯一的排名，并且不在意相同值的排名，可以使用 ROW_NUMBER。
如果你需要为每行分配排名，并且对于相同值的行希望跳过排名，可以使用 RANK。
如果你需要为每行分配排名，并且希望保持连续的排名编号，可以使用 DENSE_RANK。

（二）上手演练

假设我们有一个学生成绩表格 student_grades，包含学生姓名 Name 和 GPA 成绩 GPA。我们想要为每个学生按照 GPA 成绩进行排名，并比较三种不同的排名函数的结果。

首先，我们使用 ROW_NUMBER()、RANK() 和 DENSE_RANK() 函数来为每个学生的 GPA 成绩进行排名：

SELECT Name, 
       GPA, 
       ROW_NUMBER() OVER (ORDER BY GPA DESC) AS RowNumber, 
       RANK() OVER (ORDER BY GPA DESC) AS Rank, 
       DENSE_RANK() OVER (ORDER BY GPA DESC) AS DenseRank 
FROM student_grades;

假设我们的 student_grades 表格如下所示：

Name	GPA
Alice	3.9
Bob	3.8
Carol	3.9
Dave	3.7
Eve	4.0

运行上述查询后，我们得到以下结果：

Name	GPA	RowNumber	Rank	DenseRank
Eve	4.0	1	1	1
Alice	3.9	2	2	2
Carol	3.9	3	2	2
Bob	3.8	4	4	3
Dave	3.7	5	5	4

通过以上结果可以看出：

ROW_NUMBER() 函数为每个学生分配了唯一的排名，无论 GPA 成绩是否相同。
RANK() 函数为相同 GPA 成绩的学生分配了相同的排名，并在下一行跳过相应数量的排名。
DENSE_RANK() 函数为相同 GPA 成绩的学生分配了相同的排名，但不会跳过排名编号，保持了连续的排名编号。

八、计算不同时期之间的差值（Delta 值）

（一）快速了解

LEAD() 和 LAG() 函数是 SQL 中用于比较相邻行数据的重要工具。

LEAD() 函数用于获取当前行之后的行的数据，而 LAG() 函数则用于获取当前行之前的行的数据。

通过这两个函数，我们可以轻松地比较不同时段的数据，计算增长率、变化趋势等。它们在数据分析领域有着广泛的应用，尤其适用于时间序列数据的分析和预测。

（二）上手演练

假设我们有一个名为 monthly_sales 的表，包含每月的销售数据，其中有月份（month）和销售额（sales）两个字段。我们想要比较每个月的销售额与上个月的销售额之间的差异，并计算增长率。

首先，我们可以使用 LAG() 函数获取上个月的销售额，并将其与当前月份的销售额相减，以得到销售额的变化量。然后，我们可以将变化量除以上个月的销售额，得到增长率。

SELECT 
    month,
    sales,
    sales - LAG(sales, 1) OVER (ORDER BY month) AS sales_change,
    ROUND((sales - LAG(sales, 1) OVER (ORDER BY month)) / LAG(sales, 1) OVER (ORDER BY month) * 100, 2) AS growth_rate
FROM 
    monthly_sales;

我们首先使用 LAG() 函数获取上个月的销售额，然后将当前月份的销售额与上个月的销售额相减，得到销售额的变化量。接着，我们计算增长率，将销售额的变化量除以上个月的销售额，并乘以 100，以得到百分比形式的增长率。这样，我们就可以方便地分析每个月销售额的变化情况，了解销售业绩的发展趋势。

九、计算运行总数

（一）快速了解

数据库中，计算运行总数是一项常见的任务，特别是在分析时间序列数据或者累积值的情况下。窗口函数中的 SUM() 函数为我们提供了一种简单而强大的方法来实现这一目标。

通过结合 ORDER BY 子句，我们可以按照特定的顺序计算累积值。

（二）上手演练

假设我们有一个名为 monthly_revenue 的表格，其中包含每个月的销售额数据。我们可以使用窗口函数来计算每个月销售额的累积总数，以便更好地了解销售情况的趋势和变化。

Month	Revenue
Jan	10000
Feb	15000
Mar	12000
Apr	18000

现在，我们想要计算每个月销售额的累积总数。我们可以使用如下的 SQL 查询：

SELECT 
    Month,
    Revenue,
    SUM(Revenue) OVER (ORDER BY Month) AS Cumulative_Revenue
FROM 
    monthly_revenue;

执行上述查询后，我们将得到以下结果：

Month	Revenue	Cumulative_Revenue
Jan	10000	10000
Feb	15000	25000
Mar	12000	37000
Apr	18000	55000

通过这个案例，我们可以清晰地看到每个月销售额的累积总数是如何随着时间的推移逐步增加的。

十、日期时间

在SQL中，日期时间数据的处理是非常常见和重要的。它涉及许多方面，包括日期的比较、计算日期之间的间隔、提取日期的部分等等。为了更好地理解日期时间数据的操纵，我们需要掌握一些常用的日期函数和技巧。

举例来说，假设我们有一个名为Weather的表格，其中包含每天的温度记录。现在，我们想找出哪些日期的温度比前一天更高。我们可以使用SQL中的日期函数和联结来实现这个目标。

首先，我们需要使用DATEDIFF函数计算每个日期与前一天日期之间的天数间隔。然后，我们可以将结果与温度进行比较，找出温度比前一天更高的日期。

SELECT
    a.Id
FROM
    Weather a,
    Weather b
WHERE
    a.Temperature > b.Temperature
    AND DATEDIFF(a.RecordDate, b.RecordDate) = 1;

张彦峰ZYF

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
0
评论
一些简单高级的 SQL 查询技巧记录

介绍数据库查询中一些高级技巧和常用表达式的使用方法：涵盖常用表表达式（CTEs）、递归CTEs、临时函数、数据的枢转、EXCEPT 和 NOT IN 操作符、自联结、排名函数、计算差值和总数、以及日期时间处理等内容。
复制链接

扫一扫