MySQL CTE (Common Table Expressions)的介绍

1

2

3

4

WITH cte_name AS (

    SELECT ...  -- CTE查询定义

)

SELECT * FROM cte_name;  -- 主查询

1

2

3

4

5

6

7

8

9

10

11

12

13

14

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;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

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;

1

2

3

4

5

6

7

8

9

WITH RECURSIVE cte_name AS (

    -- 基础部分（种子查询）

    SELECT ... WHERE ...

    UNION [ALL]

    -- 递归部分

    SELECT ... FROM cte_name JOIN ...

    WHERE ...

)

SELECT * FROM cte_name;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

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;

1

2

3

4

5

6

WITH RECURSIVE number_sequence AS (

    SELECT 1 AS n

    UNION ALL

    SELECT n + 1 FROM number_sequence WHERE n < 10

)

SELECT * FROM number_sequence;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

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;

1

2

3

4

5

6

7

8

9

10

11

12

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;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

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;

1

SET SESSION cte_max_recursion_depth = 2000;

1

2

3

4

WITH cte_name AS (

    SELECT /*+ MERGE() */ * FROM table_name

)

SELECT * FROM cte_name;