sql中的一些CTE和开窗函数相关用法

CTE的优势

1. 提高可读性：将复杂查询分解为更简单的部分
2. 避免重复子查询：同一CTE可以在查询中多次引用
3. 递归查询：处理层次结构数据
4. 模块化SQL：将复杂查询拆分成逻辑模块
5. 
   

注意事项

1. CTE只在定义它的查询中有效
2. 递归CTE必须包含一个非递归部分和一个递归部分，用UNION ALL连接
3. 递归CTE必须有终止条件，否则会导致无限循环
4. MySQL 8.0之前的版本不支持CTE
5. sql server中默认CTE最大支持层级100
6. mysql中需要添加关键字RECURSIVE
7. 
   

1.基本语法

WITH cte_name AS (
    SELECT 查询语句
)
SELECT * FROM cte_name;

2.常见用法

WITH employee_salaries AS (
    SELECT id, name, department, salary
    FROM employees
    WHERE salary > 5000
)
SELECT * FROM employee_salaries;

3.多个cte用逗号分隔

WITH dept_avg AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
),
high_salary AS (
    SELECT e.id, e.name, e.department, e.salary, d.avg_salary
    FROM employees e
    JOIN dept_avg d ON e.department = d.department
    WHERE e.salary > d.avg_salary
)
SELECT * FROM high_salary;

4.1递归CTE(MySql中需要添加RECURSIVE关键字)

WITH RECURSIVE employee_hierarchy AS (
    -- 基础查询（非递归部分）
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    -- 递归查询
    SELECT e.id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;

4.2.sql server中需要注意（Sql Server中需要注意默认最大层级是100，超过会报错，可以在语句后面添加配置option(maxrecursion 0);不限制，以下语句会生成一个连续的日期列表：2025-05-01到2025-12-31）

with cte as
(
   select cast('2025-05-01' as datetime) as date1
   union all
   select dateadd(day,1,date1) from cte where date1<'2025-12-31'
)
select * from cte
option(maxrecursion 0);

4.3查询指定员工的所有层级下属员工，表结构如下

with cte2 as 
(
 select id,name,position,manager_id,department,salary,1 as level, CAST(name AS nVARCHAR(1000)) AS pname  
 from employees where name='李四'
 union all
 select e.id,e.name,e.position,e.manager_id,e.department,e.salary,c.level+1,
 cast(concat(c.pname,' => ',e.name) as nvarchar(1000)) 
 from employees e 
 inner join cte2 c on e.manager_id=c.id 
)
select * from cte2;

5.在DML语句中使用CTE

WITH to_update AS (
    SELECT id FROM employees WHERE department = 'IT'
)
UPDATE employees e
JOIN to_update t ON e.id = t.id
SET e.salary = e.salary * 1.1;

6.在CTE中使用窗口函数

WITH ranked_employees AS (
    SELECT 
        id, 
        name, 
        department, 
        salary,
        RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
    FROM employees
)
SELECT * FROM ranked_employees WHERE dept_rank <= 3;

7.环比和同比分析

WITH monthly_sales AS (
    SELECT 
        DATE_FORMAT(order_date, '%Y-%m') AS month,
        SUM(amount) AS total_sales
    FROM orders
    GROUP BY DATE_FORMAT(order_date, '%Y-%m')
),
comparison AS (
    SELECT 
        current.month,
        current.total_sales,
        prev.total_sales AS prev_month_sales,
        (current.total_sales - prev.total_sales) / prev.total_sales * 100 AS growth_rate
    FROM monthly_sales current
    LEFT JOIN monthly_sales prev ON current.month = DATE_FORMAT(DATE_ADD(STR_TO_DATE(prev.month, '%Y-%m'), INTERVAL 1 MONTH), '%Y-%m')
)
SELECT * FROM comparison ORDER BY month;

7.1累计汇总

WITH running_totals AS (
    SELECT 
        order_date,
        amount,
        SUM(amount) OVER (ORDER BY order_date) AS cumulative_sales
    FROM orders
)
SELECT * FROM running_totals;

8.寻找最短路径

WITH RECURSIVE paths AS (
    -- 基础查询：起点
    SELECT 
        from_node, 
        to_node, 
        cost, 
        from_node AS path, 
        cost AS total_cost
    FROM connections
    WHERE from_node = 'A'
    UNION ALL
    -- 递归查询：扩展路径
    SELECT 
        c.from_node, 
        c.to_node, 
        c.cost,
        CONCAT(p.path, ',', c.to_node) AS path,
        p.total_cost + c.cost AS total_cost
    FROM connections c
    JOIN paths p ON c.from_node = p.to_node
    WHERE FIND_IN_SET(c.to_node, p.path) = 0 -- 避免环路
)
SELECT * FROM paths WHERE to_node = 'Z' ORDER BY total_cost LIMIT 1;

9.最后我们看个开窗函数的强大功能吧！仍然以上面的员工表为例，需求是希望获取每个部门工资排名前三的员工,如果没有开窗函数，你们会怎么实现？

SELECT e1.id, e1.name, e1.department, e1.salary
FROM employees e1
WHERE (
    SELECT COUNT(DISTINCT e2.salary)
    FROM employees e2
    WHERE e2.department = e1.department
    AND e2.salary >= e1.salary
) <=3
ORDER BY e1.department, e1.salary DESC;

接下来我们看看开窗函数多简单，结果和上面一致！

with cte as(
select id,name,department,salary,DENSE_RANK() over(PARTITION by department order by salary desc) as num from employees 
)
select * from cte where num<=3 order by department,salary desc

9.2在看一个例子吧，可以很容实现统计每个人及所在部门的工资总和，以及整个公司的工资总和

select id,name,department,salary,
sum(salary) over(PARTITION by department) as '部门工资总和',
sum(salary) over() as '公司工资总和' from employees

还有更多用法，留给大家自己去探寻吧！