1、子查询

子查询是一个嵌套在其他查询中的查询，用于提供额外的数据过滤、计算或子集。子查询可以出现在SELECT, FROM, WHERE或HAVING子句中。子查询是一个非常强大的工具，它可以让在单个查询中执行多级数据计算。正确使用子查询能大大增强SQL的功能，使复杂数据关系的查询变得可能。

1）在WHERE子句中使用子查询

有一个名为employees的表格，其中包含员工的信息，包括他们的部门和工资。要找出工资高于其部门平均工资的员工，如下，

SELECT 
    employee_id,
    name,
    salary
FROM 
    employees
WHERE 
    salary > (SELECT AVG(salary) FROM employees AS e WHERE e.department_id = employees.department_id);

2） 在SELECT子句中使用子查询

需要在查询结果中显示每个员工的工资与其部门平均工资的差异，如下，

SELECT 
    employee_id,
    name,
    salary,
    (SELECT AVG(salary) FROM employees AS e WHERE e.department_id = employees.department_id) AS department_avg,
    salary - (SELECT AVG(salary) FROM employees AS e WHERE e.department_id = employees.department_id) AS diff_from_avg
FROM 
    employees;

3）在FROM子句中使用子查询

可以使用子查询创建一个临时表格，然后从这个表格中进行选择。如找出每个部门工资最高的员工，如下，

SELECT 
    e.*
FROM 
    employees e
INNER JOIN 
    (SELECT department_id, MAX(salary) AS max_salary FROM employees GROUP BY department_id) AS max_salaries
ON 
    e.department_id = max_salaries.department_id AND e.salary = max_salaries.max_salary;

4）在HAVING子句中使用子查询

要找出平均工资高于公司整体平均工资的部门，如下，

SELECT 
    department_id,
    AVG(salary) AS average_salary
FROM 
    employees
GROUP BY 
    department_id
HAVING 
    AVG(salary) > (SELECT AVG(salary) FROM employees);

2、公用表表达式 (CTE)

公用表表达式（Common Table Expressions，简称CTE）是SQL查询中的一个强大工具，可以将查询结果暂时命名并在后续的查询中重复使用。CTE的使用可以使查询变得更加模块化、易于理解和维护。CTE尤其有用于复杂查询的构建，可以将复杂的逻辑分解成简单、模块化的部分。

1）基础CTE用法

有一个员工表employees，其中包含员工的ID、姓名和部门ID。要找出每个部门的员工数量，如下，

WITH DepartmentCounts AS (
    SELECT department_id, COUNT(*) AS num_employees
    FROM employees
    GROUP BY department_id
)
SELECT *
FROM DepartmentCounts;

2）多层CTE

公用表表达式可以嵌套使用，使得处理多步骤的数据分析变得更简单。比如，要从员工表中找到每个部门平均工资最高的三个部门，如下，

WITH DepartmentSalary AS (
    SELECT department_id, AVG(salary) AS average_salary
    FROM employees
    GROUP BY department_id
),
TopDepartments AS (
    SELECT department_id
    FROM DepartmentSalary
    ORDER BY average_salary DESC
    LIMIT 3
)
SELECT e.department_id, e.name, e.salary
FROM employees e
JOIN TopDepartments td ON e.department_id = td.department_id;

3）递归CTE

递归CTE可以用来处理层级或递归关系，如组织结构或树形结构。假设我们有一个组织结构表organization，其中包含员工ID和上级ID，要为每个员工创建一个层级列表，如下 ，

WITH RECURSIVE OrgChart AS (
    SELECT employee_id, supervisor_id, 1 AS depth
    FROM organization
    WHERE supervisor_id IS NULL
    UNION ALL
    SELECT o.employee_id, o.supervisor_id, oc.depth + 1
    FROM organization o
    INNER JOIN OrgChart oc ON o.supervisor_id = oc.employee_id
)
SELECT *
FROM OrgChart;

3、 窗口函数

窗口函数（Window Functions）是SQL的强大功能之一，可以在数据集的"窗口"上执行计算，这些窗口根据指定的条件对行进行分组。窗口函数对于执行如排名、累计、移动平均等操作非常有用，而且不需要将数据实际分组到单独的输出行中，因此可以保留详细的行级数据。窗口函数是一种特殊的函数，可以对查询结果集中的一组行进行计算，并返回一个与每行关联的值。常见的窗口函数包括排名函数（ROW_NUMBER(), RANK(), DENSE_RANK()）、聚合函数（SUM(), AVG(), COUNT()）以及累积函数（SUM()OVER()）等。

1）排名函数

ROW_NUMBER()：为每一行分配一个唯一的连续整数。

RANK()：为相同值的行分配相同的排名，但会在下一个值上留出空白（例如 1, 2, 2, 4）。

DENSE_RANK()：与RANK()类似，但排名之间没有间隔（例如 1, 2, 2, 3）。

SELECT 
  product_id,
  sales,
  ROW_NUMBER() OVER (ORDER BY sales DESC) AS row_number,
  RANK() OVER (ORDER BY sales DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY sales DESC) AS dense_rank
FROM sales_records;

2）聚合窗口函数

SUM()、AVG()、MAX()、MIN()：在窗口中计算总和、平均值、最大值和最小值。计算每个员工在每个部门的累计销售总额，如下，

SELECT 
  employee_id,
  department_id,
  sales,
  SUM(sales) OVER (PARTITION BY department_id ORDER BY sales_date) AS cumulative_sales
FROM sales_records;

3） 移动平均和累积

LEAD()、LAG()：获取窗口中当前行的前一行或后一行的数据。

移动平均：使用窗口函数计算移动平均值。

计算每个产品的当前月销售与前两月销售的平均值，如下，

SELECT 
  product_id,
  sales_month,
  sales,
  AVG(sales) OVER (PARTITION BY product_id ORDER BY sales_month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_average
FROM monthly_sales;

4）首值与尾值

窗口函数通过指定OVER()子句来定义窗口的分组方式和排序规则。PARTITION BY子句用于在不同的分区中独立计算每个窗口，而ORDER BY子句则用于数据排序。FIRST_VALUE()、LAST_VALUE()是在窗口内获取一组值的第一个或最后一个。显示每个部门中薪资最高的员工的薪资。如下，

SELECT 
  employee_id,
  department_id,
  salary,
  FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary DESC) AS highest_salary
FROM employees;