GaussDB的with子句

with子句在GaussDB（基于PostgreSQL内核）里也被称作公共表表达式（CTE），它能定义临时的命名结果集，在查询里可以多次引用这些结果集。CTE 能够让复杂的查询更易读、易维护，并且在某些情况下还能提升查询性能。

-- 定义一个名为dept_employee_count的CTE
WITH dept_employee_count AS (
    SELECT 
        department_id,
        COUNT(employee_id) AS employee_count
    FROM 
        employees
    GROUP BY 
        department_id
)
-- 主查询，将CTE与departments表连接
SELECT 
    d.department_name,
    dec.employee_count
FROM 
    departments d
JOIN 
    dept_employee_count dec ON d.department_id = dec.department_id;