SQL 是一种强大的查询语言，能够高效地从数据库中提取数据。掌握高级 SQL 技巧可以帮助开发者编写更高效、灵活的查询，以满足复杂的数据分析需求。本文将介绍一些高级 SQL 技巧，包括窗口函数、递归查询、CTE（公共表表达式）等。
1. 使用窗口函数
窗口函数（Window Functions）允许我们在不使用 GROUP BY 的情况下执行聚合运算，它为每一行提供了一个窗口，并在该窗口中执行计算。
语法：
SELECT column_name,AGGREGATE_FUNCTION() OVER (PARTITION BY column_name ORDER BY column_name) AS alias
FROM table_name;
示例：计算每个员工的工资在其部门中的排名
SELECT employee_name, department, salary,RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees;
在此示例中，我们使用了 RANK() 窗口函数按部门分组并对工资进行排名。
常用的窗口函数：
ROW_NUMBER()：给每行分配唯一的行号
RANK()：根据排序条件给出排名，允许并列排名
DENSE_RANK()：和 RANK() 类似，但没有空缺排名
SUM()、AVG()、COUNT()：用于累积总和、平均值、计数等
2. 递归查询
递归查询通过 WITH RECURSIVE 可以用于解决层级结构或父子关系的数据查询问题。
示例：查询员工层级结构（管理层）
假设我们有一个员工表，其中包含 employee_id 和 manager_id，要查找某个员工的上下级关系。
WITH RECURSIVE employee_hierarchy AS (SELECT employee_id, employee_name, manager_id, 1 AS levelFROM employeesWHERE employee_id = 1 -- 指定顶层管理者UNION ALLSELECT e.employee_id, e.employee_name, e.manager_id, eh.level + 1FROM employees eINNER JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;
该递归查询通过递归找到所有下属员工，并给出他们相应的层级。
3. 使用公共表表达式（CTE）
公共表表达式（CTE）允许在查询中临时定义一个子查询，并为其命名。CTE 使得 SQL 语句更加易读，尤其是在复杂查询中。
语法：
WITH cte_name AS (SELECT column_nameFROM table_nameWHERE condition
)
SELECT * FROM cte_name;
示例：计算每个部门的平均工资，并显示工资高于平均工资的员工
WITH department_avg AS (SELECT department, AVG(salary) AS avg_salaryFROM employeesGROUP BY department
)
SELECT e.employee_name, e.salary, da.avg_salary
FROM employees e
JOIN department_avg da ON e.department = da.department
WHERE e.salary > da.avg_salary;
在此示例中，CTE department_avg 首先计算每个部门的平均工资，随后主查询使用这个结果来筛选出工资高于平均工资的员工。
4. 使用 CASE 语句进行条件计算
CASE 语句允许在 SQL 查询中进行条件判断，类似于编程语言中的 if-else 语句。
示例：根据销售额分级
SELECT employee_name, sales_amount,CASEWHEN sales_amount > 10000 THEN 'A'WHEN sales_amount BETWEEN 5000 AND 10000 THEN 'B'ELSE 'C'END AS sales_level
FROM sales;
此查询根据销售额的大小将员工分为 A、B、C 三个等级。
5. 复杂的子查询
子查询是嵌套在另一个查询中的查询，可以用于解决更复杂的查询需求。
示例：查询销售额高于平均水平的员工
SELECT employee_name, sales_amount
FROM sales
WHERE sales_amount > (SELECT AVG(sales_amount) FROM sales);
这个查询通过一个子查询计算了平均销售额，然后返回销售额高于平均值的员工。
6. 使用 EXISTS 优化查询
EXISTS 用于检查子查询是否返回结果，通常比 IN 更加高效，尤其是在子查询返回大量数据时。
示例：查询有销售记录的员工
SELECT employee_name
FROM employees e
WHERE EXISTS (SELECT 1 FROM sales s WHERE s.employee_id = e.employee_id);
该查询通过 EXISTS 来检查是否有对应的销售记录，从而返回有销售记录的员工。
7. GROUP BY 和 HAVING 的巧妙使用
GROUP BY 用于对结果集进行分组，HAVING 用于过滤分组后的结果，类似于 WHERE 过滤原始行。
示例：查询销售额超过 5000 的部门
SELECT department, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY department
HAVING SUM(sales_amount) > 5000;
这里通过 HAVING 对分组后的结果进行过滤，只保留总销售额超过 5000 的部门。
8. 使用 COALESCE 处理空值
COALESCE 函数返回第一个非空表达式，常用于处理空值场景。
示例：将空的电子邮件地址替换为默认值
SELECT employee_name, COALESCE(email, 'no-email@example.com') AS email
FROM employees;
这个查询将没有电子邮件地址的员工的邮件字段替换为默认值。
9. 使用 UNION 和 UNION ALL
UNION 将多个查询的结果集合并在一起，并自动去重。而 UNION ALL 则不去重，性能更优。
示例：查询当前和前一年销售额超过 5000 的员工
SELECT employee_name, sales_amount
FROM sales_2024
WHERE sales_amount > 5000
UNION
SELECT employee_name, sales_amount
FROM sales_2023
WHERE sales_amount > 5000;
此查询合并了两个年份的销售数据。
10. 索引和查询优化
编写高效的 SQL 查询还要关注数据库的索引结构。使用合适的索引可以大幅提升查询性能。
示例：检查查询执行计划
EXPLAIN SELECT * FROM employees WHERE employee_id = 1;
通过 EXPLAIN 语句，可以查看查询的执行计划，帮助分析查询的性能瓶颈。
总结
高级 SQL 技巧能够显著提升数据查询的灵活性与效率。通过掌握窗口函数、递归查询、CTE、子查询、CASE 语句等技能，开发者可以更轻松地处理复杂的查询需求。同时，合理使用索引和优化查询也是提升性能的重要环节。