QL语句通常不是很容易理解，特别是你阅读别人已经写好的语句。因此，很多人指出我们应该遵循在其他语言中遵循的原则，像加上注释和功能模块化。我最新注意到一个很多人都没有使用的Postgres关键特性，也就

本文介绍如何利用SQL中的公用表表达式(CTE)来提高复杂查询的可读性和维护性。通过示例展示了如何分步骤构建查询，并讨论了CTE在性能上的考量。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

QL语句通常不是很容易理解，特别是你阅读别人已经写好的语句。因此，很多人指出我们应该遵循在其他语言中遵循的原则，像加上注释和功能模块化。我最新注意到一个很多人都没有使用的Postgres关键特性，也就是 @timonk在AWS Re:Invent 大会关于数据仓库服务Redshift主题演讲时指出的一个特性。这个特性实际上使得SQL兼具了可读性和模块性。在以前，我回头阅读自己的几个月前的SQL语句，通常很难理解，而现在我可以做到这一点。

这个特性就是CTEs，也就是公用表表达式，你有可能称做它为WITH 语句。和数据库中视图一样，它的主要好处就是，它允许你在当前事务中创建临时表。你可以大量使用它，因为它允许你思路清晰的构建模块，别人很容易就理解你在做什么。

让我们举个简单的例子

 
        WITH 
         users_tasks 
        AS 
         (
       
        SELECT
       
        users.email,
       
        array_agg(tasks.
        name
        ) 
        as 
         task_list,
       
        projects.title
       
        FROM
       
        users,
       
        tasks,
       
        project
       
        WHERE
       
        users.id = tasks.user_id
       
        projects.title = tasks.project_id
       
        GROUP 
         BY
       
        users.email,
       
        projects.title
       
        )

通过这样定义临时表users_tasks，我就可以在后面加上对users_tasks基本查询语句，像：

 
        SELECT 
         *
       
        FROM 
         users_tasks;

有趣的是你可以将它们连在一起。当我知道分配给每个用户的任务量时，也许我想知道在一个指定的任务上，谁因为对这个任务负责超过了50%而因此造成瓶颈。为了简化，我们可以使用多种方式，先计算每个任务的总量，然后是每人针对每个任务的负责总量。

 
        total_tasks_per_project 
        AS 
         (
       
        SELECT
       
        project_id,
       
        count
        (*) 
        as 
         task_count
       
        FROM 
         tasks
       
        GROUP 
         BY 
         project_id
       
        ),
       
        tasks_per_project_per_user 
        AS 
         (
       
        SELECT
       
        user_id,
       
        project_id,
       
        count
        (*) 
        as 
         task_count
       
        FROM 
         tasks
       
        GROUP 
         BY 
         user_id, project_id
       
        ),

现在我们将组合一下然后发现超过50%的用户

 
        overloaded_users 
        AS 
         (
       
        SELECT 
         tasks_per_project_per_user.user_id,
       
        FROM 
         tasks_per_project_per_user,
       
        total_tasks_per_project
       
        WHERE 
         tasks_per_project_per_user.task_count > (total_tasks_per_project / 2)
       
        )

最终目标，我想获得超负荷工作这的用户和任务的逗号分隔列表。我们只要简单地对overloaded_users和 users_tasks的初始列表进行join操作。放在一起可能有点长，但是可读性强。作为额外帮助，我又在每一层加了注释。

 
        --- Created by Craig Kerstiens 11/18/2013
       
        --- Query highlights users that have over 50% of tasks on a given project
       
        --- Gives comma separated list of their tasks and the project
       
        --- Initial query to grab project title and tasks per user
       
        WITH 
         users_tasks 
        AS 
         (
       
        SELECT
       
        users.id 
        as 
         user_id,
       
        users.email,
       
        array_agg(tasks.
        name
        ) 
        as 
         task_list,
       
        projects.title
       
        FROM
       
        users,
       
        tasks,
       
        project
       
        WHERE
       
        users.id = tasks.user_id
       
        projects.title = tasks.project_id
       
        GROUP 
         BY
       
        users.email,
       
        projects.title
       
        ),
       
        --- Calculates the total tasks per each project
       
        total_tasks_per_project 
        AS 
         (
       
        SELECT
       
        project_id,
       
        count
        (*) 
        as 
         task_count
       
        FROM 
         tasks
       
        GROUP 
         BY 
         project_id
       
        ),
       
        --- Calculates the projects per each user
       
        tasks_per_project_per_user 
        AS 
         (
       
        SELECT
       
        user_id,
       
        project_id,
       
        count
        (*) 
        as 
         task_count
       
        FROM 
         tasks
       
        GROUP 
         BY 
         user_id, project_id
       
        ),
       
        --- Gets user ids that have over 50% of tasks assigned
       
        overloaded_users 
        AS 
         (
       
        SELECT 
         tasks_per_project_per_user.user_id,
       
        FROM 
         tasks_per_project_per_user,
       
        total_tasks_per_project
       
        WHERE 
         tasks_per_project_per_user.task_count > (total_tasks_per_project / 2)
       
        )
       
        SELECT
       
        email,
       
        task_list,
       
        title
       
        FROM
       
        users_tasks,
       
        overloaded_users
       
        WHERE
       
        users_tasks.user_id = overloaded_users.user_id