postgresql - 将 Postgres 查询拆分为可读的片段

Question

我有一个相对的，不复杂但很长的查询字符串，例如:

SELECT array_to_json(array_agg(row_to_json(exp)))
    FROM (
      SELECT label as name, (
    SELECT array_to_json(array_agg(row_to_json(opt)))
        FROM (
            SELECT value as name, count as size FROM countoptions 
      ) opt
    ) as children
    FROM countoptions GROUP BY label 
    ) exp;

稍后这个查询会变得更复杂，所以我正在寻找一种技术来将查询拆分为更易读的部分。

Answer 1

是Common Table Expressions你在找什么？

WITH provides a way to write auxiliary statements for use in a larger query. These statements, which are often referred to as Common Table Expressions or CTEs, can be thought of as defining temporary tables that exist just for one query. Each auxiliary statement in a WITH clause can be a SELECT, INSERT, UPDATE, or DELETE; and the WITH clause itself is attached to a primary statement that can also be a SELECT, INSERT, UPDATE, or DELETE