MySQL CTE (Common Table Expressions)示例全解析

with cte_name as (
    select ...  -- cte查询定义
)
select * from cte_name;  -- 主查询

with department_stats as (
    select 
        department_id, 
        count(*) as employee_count,
        avg(salary) as avg_salary
    from employees
    group by department_id
)
select 
    d.department_name,
    ds.employee_count,
    ds.avg_salary
from departments d
join department_stats ds on d.department_id = ds.department_id;

with 
high_earners as (
    select * from employees where salary > 100000
),
it_employees as (
    select * from employees where department_id = 10
)
select 
    h.employee_id,
    h.name,
    'high earner' as category
from high_earners h
union all
select 
    i.employee_id,
    i.name,
    'it employee' as category
from it_employees i;

with recursive cte_name as (
    -- 基础部分（种子查询）
    select ... where ...
    union [all]
    -- 递归部分
    select ... from cte_name join ...
    where ...
)
select * from cte_name;

with recursive org_hierarchy 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,
        oh.level + 1
    from employees e
    join org_hierarchy oh on e.manager_id = oh.employee_id
)
select * from org_hierarchy order by level, employee_id;

with recursive number_sequence as (
    select 1 as n
    union all
    select n + 1 from number_sequence where n < 10
)
select * from number_sequence;

with 
monthly_sales as (
    select 
        date_format(order_date, '%y-%m') as month,
        sum(amount) as total_sales
    from orders
    group by month
),
growth_rate as (
    select 
        month,
        total_sales,
        lag(total_sales) over (order by month) as prev_sales,
        (total_sales - lag(total_sales) over (order by month)) / 
        lag(total_sales) over (order by month) * 100 as growth_pct
    from monthly_sales
)
select * from growth_rate;

with 
raw_data as (
    select * from source_table where quality_check = 1
),
cleaned_data as (
    select 
        id,
        trim(name) as name,
        case when age < 0 then null else age end as age
    from raw_data
)
select * from cleaned_data;

with recursive path_finder as (
    select 
        start_node as path,
        start_node,
        end_node,
        1 as length
    from graph
    where start_node = 'a'
    union all
    select 
        concat(pf.path, '->', g.end_node),
        g.start_node,
        g.end_node,
        pf.length + 1
    from graph g
    join path_finder pf on g.start_node = pf.end_node
    where find_in_set(g.end_node, replace(pf.path, '->', ',')) = 0
)
select * from path_finder;

set session cte_max_recursion_depth = 2000;

with cte_name as (
    select /*+ merge() */ * from table_name
)
select * from cte_name;