Postgresql官方文档之Query

7.1. Overview

查询（query）的基本概念和作用。在 SQL 中，用 SELECT 命令来指定查询。查询的一般语法是 [WITH with_queries] SELECT select_list FROM table_expression [sort_specification]。本节还介绍了查询列表（select list）、表达式（table expression）和排序规范（sort specification）的细节。WITH 查询被放在最后讨论，因为它是一个高级特性。简单的查询示例是 SELECT * FROM table1;，假设有一个名为 table1 的表，此命令将检索 table1 中的所有行和所有用户定义的列。查询列表的指定（*）意味着表达式提供的所有列。查询列表还可以选择可用列的子集或使用列进行计算。例如，如果 table1 有名为 a, b, c 的列（可能还有其他的），可以进行如下查询：SELECT a, b + c FROM table1;（假设 b 和 c 是数值数据类型）。

7.2. Table Expressions

7.2.1. The FROM Clause

FROM 子句用于从一个或多个表中派生出一个表，这些表在逗号分隔的表引用列表中给出。表引用可以是一个表名（可能需要模式限定），或者是一个派生表，如子查询、JOIN 构造，或者是这些的复杂组合。如果 FROM 子句中列出了多于一个的表引用，这些表将被交叉连接，即形成它们行的笛卡尔积。结果是一个中间的虚拟表，然后可以通过 WHERE、GROUP BY 和 HAVING 子句进行转换，并最终成为整体表达式的结果。当表引用命名一个表，该表是表继承层次结构的父表时，表引用将产生不仅该表的行，还有其所有后代表的行，除非在表名之前使用了关键字 ONLY。然而，引用仅产生在命名表中出现的列——在子表中添加的任何列都将被忽略。
此外，本节还解释了 JOIN 表的概念，即根据特定联接类型的规则从两个其他表（实际或派生的）派生出的表。内联接、外联接和交叉联接都是可用的，每种类型的联接都有其特定的语法和用途。
这一部分的内容为理解 SQL 查询中的表达式提供了基础，特别是如何从多个源表中构造查询所需的数据集。

7.2.1.1. Joined Tables

JOIN 操作允许根据两个表中的共同属性来合并行，是数据库查询中非常重要的一个概念。

1. Cross Join：
   • 通过 T1 CROSS JOIN T2 的形式，对于 T1 和 T2 中的每一对可能的行组合，联结表都将包含一个由 T1 中所有列后跟 T2 中所有列组成的行。如果表 T1 有 N 行，表 T2 有 M 行，那么联结表将有 N * M 行。
   • FROM T1 CROSS JOIN T2 等价于 FROM T1, T2，以及 FROM T1 INNER JOIN T2 ON TRUE。
2. Qualified Joins（限定联结）：
   • 包括内联结（INNER JOIN）、左外联结（LEFT OUTER JOIN）、右外联结（RIGHT OUTER JOIN）和全外联结（FULL OUTER JOIN）。
   • 联结条件可以在 ON 子句中指定，或者通过 USING 子句指定联结列，或者通过 NATURAL 关键字隐式指定。
   • INNER JOIN：对于 T1 中的每一行 R1，联结表中将有来自 T2 的每一行，这些行与 R1 满足联结条件。
   • LEFT OUTER JOIN：首先执行内联结，然后对于 T1 中没有与 T2 中任何行满足联结条件的行，添加一个联结行，并在 T2 的列中填充 NULL 值。
   • RIGHT OUTER JOIN 和 FULL OUTER JOIN 类似，但方向相反，分别关注 T2 和同时关注 T1 与 T2 中未能匹配的行。

7.2.1.2. Table and Column Aliases

在 SQL 查询中如何使用表别名（table alias）和列别名（column alias）来简化查询或解决命名冲突。别名是临时的名称，仅在查询执行期间有效，可以为表或者查询结果的列指定。这些别名在查询的其余部分中用于引用。

1. 表别名（Table Aliases）：
   • 使用 FROM table_reference AS alias 或 FROM table_reference alias 形式为表引用指定别名。关键字 AS 是可选的。
   • 表别名主要用于两种情况：简化长表名的引用以保持查询的可读性；在自连接查询中区分同一表的不同实例。
   • 一旦为表引用指定了别名，就必须在查询的其余部分中使用这个别名来引用表，而不能使用原始表名。
2. 列别名（Column Aliases）：
   • 在 SELECT 列表中，可以使用 AS 关键字为输出列指定别名，例如 SELECT column_name AS alias。
   • 如果没有使用 AS 指定输出列的别名，系统将为结果列分配默认名称。对于简单的列引用，这通常是被引用列的名称。对于函数调用，通常是函数的名称。
   • 指定列别名是有用的，特别是当查询结果被用作更复杂查询的一部分时，或者当需要改善查询结果的可读性时。
     使用表和列的别名可以使查询更加清晰和易于维护，尤其是在处理复杂的查询和多表连接时。

7.2.1.3. Subqueries

在 SQL 查询中如何使用子查询作为派生表。子查询是嵌套在另一个查询中的查询，它允许在一个查询的 FROM 子句中使用另一个 SELECT 语句的结果。这种方式可以让查询在执行时动态地创建临时表，进而在主查询中引用这个临时表的数据。

1. 基本用法：
   • 子查询必须被包含在圆括号中，并且可以被赋予一个表别名，这样在主查询中就可以通过这个别名引用子查询的结果。例如：FROM (SELECT * FROM table1) AS alias_name。
   • 子查询在 FROM 子句中的使用，使得可以在一个查询中组合或转换数据，然后在外层查询中进一步处理这些数据。
2. 列别名：
   • 子查询除了可以被赋予表别名外，还可以为其结果列指定别名。如果子查询产生的列数量少于指定的列别名数量，那么未指定别名的列将保持原有的列名。
   • 为子查询结果指定列别名对于清晰地引用这些结果中的特定数据非常有用，尤其是当子查询结果被用作更复杂查询的一部分时。
3. 高级应用：
   • 子查询不仅可以用于简单的数据选择，还可以用于更复杂的情况，如在子查询中进行分组或聚合。
   • 子查询还可以是一个 VALUES 列表，这为在查询中直接生成数据提供了一种方式，例如：FROM (VALUES (anne, smith), (bob, jones)) AS names(first, last)。
     子查询是 SQL 查询设计中的一个强大工具，它提供了一种灵活的方式来创建复杂的查询，允许在一个查询的范围内执行多个数据处理步骤。

7.2.1.4. Table Functions

表函数是一类特殊的函数，它们返回一组行，这些行可以是基本数据类型（标量类型）或复合数据类型（表行）。在查询的 FROM 子句中使用表函数，就像使用表、视图或子查询一样。表函数返回的列可以包含在 SELECT、JOIN 或 WHERE 子句中，就像表、视图或子查询的列一样。

1. 基本用法：
   • 表函数可以单独使用，或者通过 ROWS FROM 语法与其他表函数组合使用，返回的结果在并行列中；结果集中的行数是最大函数结果的行数，较小的结果集将用 NULL 值填充以匹配最大结果集的行数。
   • 使用表函数时，可以通过 WITH ORDINALITY 添加一个额外的列，该列为 bigint 类型，为函数结果集中的行编号，从 1 开始。
2. 别名和列别名：
   • 如果没有指定表别名，函数名将作为表名使用；在 ROWS FROM() 构造中，如果没有指定表别名，将使用第一个函数的名称。
   • 如果没有提供列别名，则对于返回基本数据类型的函数，列名同样是函数名；对于返回复合类型的函数，结果列将获取类型的各个属性的名称。
3. 示例：
   • 创建一个表函数并在查询中使用它，例如：

     
     CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
     SELECT * FROM foo WHERE fooid = $1;$$ LANGUAGE SQL;
     SELECT * FROM getfoo(1) AS t1;
     复制

   • 在 FROM 子句中使用 VALUES 列表作为表函数的一种形式：

     
     FROM (VALUES (anne, smith), (bob, jones), (joe, blow)) AS names(first, last)
     复制

4. 高级应用：
   • 表函数可以用于定义可以返回不同列集的函数，这些函数根据它们被调用的方式返回不同的结果。这种灵活性允许表函数在查询中用于多种复杂的数据处理场景。
     表函数为 SQL 查询提供了一种强大的方式，以编程方式生成或转换数据集，从而增强了查询的灵活性和表达力。

7.2.1.5. LATERAL Subqueries

LATERAL 关键字在 SQL 查询中的用法，特别是如何使子查询能够引用其它 FROM 子句项中定义的表或列。通常情况下，SQL 查询中的一个表达式不能引用同一 FROM 子句中后面定义的表或列，但是使用 LATERAL 关键字可以打破这一限制，允许子查询访问在同一 FROM 子句中先前定义的任何表或列。

1. 基本概念：
   • LATERAL 可以用于 FROM 子句中的子查询或表函数。当使用 LATERAL 时，每个子查询或表函数可以引用在它之前的 FROM 子句项中定义的表或列。
   • 在没有 LATERAL 的情况下，子查询是独立计算的，这意味着它们不能访问外部查询中定义的表或列。
2. 应用场景：
   • LATERAL 特别有用于子查询需要访问从另一个表中获取的行或列的情况。例如，可以使用 LATERAL 来为每行执行一个可能返回多行的函数调用，或者基于前一个表的列值来过滤另一个表的行。
3. 示例：
   • 一个简单的 LATERAL 使用示例是：

     
     SELECT * FROM foo, LATERAL (SELECT * FROM bar WHERE bar.id = foo.bar_id) ss;
     复制

     这个查询展示了如何使用 LATERAL 来引用同一 FROM 子句中先前定义的表 foo 的列 bar_id。
4. 高级用法：
   • LATERAL 还可以与 LEFT JOIN 结合使用，使得即使 LATERAL 子查询没有返回任何行，源表的行也会出现在结果中。这对于执行可选的扩展查询特别有用，例如，尝试从另一个表中获取相关信息，但不要求这些信息对于每个源表行都存在。
     LATERAL 子查询为 SQL 查询提供了更大的灵活性和表达能力，特别是在处理复杂的数据关系和依赖于其他查询结果的情况时。

7.2.2. The WHERE Clause

WHERE 子句在 SQL 查询中的作用。WHERE 子句用于对从 FROM 子句得到的表进行过滤，只返回满足特定条件的行。这个条件是一个返回布尔值的表达式，可以涉及表中的列和各种运算符。

1. 基本用法：
   • WHERE 子句的基本语法是 WHERE search_condition，其中 search_condition 是一个返回布尔类型结果的值表达式。
   • 在处理 FROM 子句得到的虚拟表之后，每一行都会根据 WHERE 子句中的搜索条件进行检查。如果条件的结果为 true，则该行会被保留在输出表中；如果结果为 false 或 null，则该行会被丢弃。
2. 示例：
   • 例如，SELECT ... FROM fdt WHERE c1 > 5 会返回表 fdt 中所有 c1 列值大于 5 的行。
   • SELECT ... FROM fdt WHERE c1 IN (1, 2, 3) 会返回 c1 列值为 1、2 或 3 的行。
   • SELECT ... FROM fdt WHERE c1 IN (SELECT c1 FROM t2) 使用子查询作为条件，返回 fdt 表中 c1 列与 t2 表中 c1 列匹配的行。
3. 高级应用：
   • WHERE 子句不仅可以用于简单的条件过滤，还可以结合子查询和聚合函数来实现更复杂的查询逻辑。
   • 例如，SELECT ... FROM fdt WHERE EXISTS (SELECT c1 FROM t2 WHERE c2 > fdt.c1) 会返回 fdt 表中，对于 t2 表存在 c2 列值大于 fdt 表中 c1 列值的情况的所有行。
     WHERE 子句是 SQL 查询中非常重要的一个组成部分，它为数据检索提供了强大的过滤功能，使得用户能够精确地获取所需的数据。

7.2.3. The GROUP BY and HAVING Clauses

SQL 查询中如何使用 GROUP BY 和 HAVING 子句对查询结果进行分组和过滤。这两个子句通常与聚合函数（如 SUM、AVG、COUNT 等）一起使用，以对数据集进行汇总和分析。

1. GROUP BY 子句：
   • GROUP BY 子句用于将查询结果集中的行分组成小的分组，基于一个或多个列的值。在每个组内，可以对列进行聚合计算。
   • 例如，SELECT department, SUM(salary) FROM employees GROUP BY department; 会按部门对员工的薪资进行总和计算，每个部门的薪资总和作为一个分组的结果返回。
2. HAVING 子句：
   • HAVING 子句用于对 GROUP BY 生成的分组进行条件过滤。它与 WHERE 子句类似，但 WHERE 子句在数据分组前对行进行过滤，而 HAVING 子句在数据分组后对组进行过滤。
   • 例如，SELECT department, SUM(salary) FROM employees GROUP BY department HAVING SUM(salary) > 100000; 会返回那些薪资总和超过 100000 的部门和它们的薪资总和。
3. 使用场景：
   • GROUP BY 和 HAVING 子句通常用于数据分析，比如计算每个分类的平均值、总和、最大或最小值等。
   • 它们使得可以从大量数据中提取有意义的信息，如计算每个产品类别的平均销售额，或找出销售额超过某个阈值的所有店铺。
4. 注意事项：
   • 在 GROUP BY 子句中引用的列必须是 SELECT 列表中的列或表达式的一部分。
   • HAVING 子句中的条件通常涉及聚合函数，以便对组的聚合结果进行过滤。
     这两个子句的结合使用提供了 SQL 查询中强大的数据分析和处理能力，允许用户对数据进行分组、汇总和基于聚合结果的条件过滤。

7.2.4. GROUPING SETS, CUBE, and ROLLUP

SQL 查询中用于高级分组操作的三个概念：GROUPING SETS、CUBE 和 ROLLUP。这些概念用于在单个查询中执行多种类型的聚合，提供了对数据分析更灵活的控制。

1. GROUPING SETS：
   • GROUPING SETS 允许在一个查询中指定多个分组集。这意味着可以单独对每个指定的分组集进行聚合计算，而不是对所有可能的分组组合进行聚合。
   • 例如，SELECT brand, size, SUM(sales) FROM items_sold GROUP BY GROUPING SETS ((brand), (size), ()); 这个查询将会分别计算按品牌分组、按大小分组以及总计的销售总额。
2. CUBE：
   • CUBE 生成指定列的所有可能的分组组合。它用于执行多维数据分析，可以快速生成数据的多个视图。
   • 例如，SELECT brand, size, SUM(sales) FROM items_sold GROUP BY CUBE (brand, size); 这个查询将会生成按品牌分组、按大小分组、按品牌和大小同时分组以及总计的销售总额。
3. ROLLUP：
   • ROLLUP 是一种分层的数据汇总方法，它按照指定列的顺序生成分组的子集，从而可以从最详细的数据汇总到最汇总的级别。
   • 例如，SELECT brand, size, SUM(sales) FROM items_sold GROUP BY ROLLUP (brand, size); 这个查询将会生成按品牌和大小同时分组的销售总额，然后是按品牌分组的销售总额，最后是总计的销售总额。
     这三种方法提供了强大的数据分析能力，使得可以在一个查询中从多个角度分析数据，从而获取数据的深入见解。它们特别适用于需要对数据进行全面分析的情况，如财务报告、库存管理和市场分析等。

7.2.5. Window Function Processing

窗口函数是一类特殊的函数，用于对查询结果集的各个部分执行计算，而不会使行被聚合到单一的输出行中，这使得可以在每行上保留更多的细节信息。窗口函数在数据分析中非常有用，尤其是当需要计算运行总计、移动平均或每组内的排名时。

1. 基本概念：
   • 窗口函数在查询的 SELECT 部分被指定，但它们的计算是在查询结果集已经根据 FROM、WHERE、GROUP BY 和 HAVING 子句处理完毕之后进行的。这意味着窗口函数可以访问到由前面的查询部分生成的结果集。
   • 如果查询使用了 GROUP BY 或 HAVING 子句，窗口函数将作用于这些子句生成的分组行上，而不是原始表行。
2. 窗口函数的特性：
   • 窗口函数通过定义 OVER 子句来指定窗口，OVER 子句可以包括 PARTITION BY（用于分区）、ORDER BY（用于排序）和窗口范围（用于指定窗口内行的范围）。
   • 窗口函数不引起行的合并，每行的输出仍然保留，但每行可以携带额外的计算结果，如累积和或排名。
3. 计算顺序：
   • 当查询中存在多个窗口函数时，所有具有相同 PARTITION BY 和 ORDER BY 子句的窗口函数将在单次扫描过程中一起计算，以确保它们看到相同的行顺序。
   • 然而，如果窗口函数具有不同的 PARTITION BY 或 ORDER BY 子句，它们可能需要不同的排序步骤，这些排序步骤之间的顺序是不保证的。
4. 使用建议：
   • 虽然窗口函数的计算默认会导致数据根据某种方式排序，但如果需要确保结果集的特定排序顺序，最好在查询的最外层显式使用 ORDER BY 子句。
     窗口函数提供了一种强大的方式来执行复杂的数据分析和计算，而不需要将数据聚合到更少的行中，从而在保持数据细节的同时获得洞察。

7.3. Select Lists

7.3.1. Select-List Items

选择列表确定了查询最终返回的列，即指定了哪些列或计算结果应该出现在查询结果集中。

1. 基本用法：
   • 最简单的选择列表是使用星号（*），表示选择所有列，即 SELECT * FROM table1; 会返回 table1 中的所有列。
   • 选择列表也可以是逗号分隔的值表达式列表，这些表达式定义了要返回的确切列或计算结果，例如 SELECT column1, column2, column1 + column2 FROM table1;。
2. 列别名：
   • 在选择列表中，可以使用 AS 关键字为列或计算结果指定别名，以提高查询结果的可读性或为后续的查询操作提供便利，例如 SELECT column1 AS c1, column2 + 10 AS c2 FROM table1;。
3. 使用函数和表达式：
   • 选择列表中的项不仅可以是表的列，还可以是使用列数据进行的计算或函数调用的结果，例如 SELECT UPPER(name), salary * 1.1 FROM employees;。
4. 选择特定列：
   • 如果查询涉及多个表，且这些表中有列名相同，可以使用表名或别名作为前缀来明确指定引用哪个表的列，例如 SELECT table1.column1, table2.column1 FROM table1 JOIN table2 ON ...;。
5. 表达式中的列：
   • 选择列表中的表达式可以使用 FROM 子句中指定的表的列，甚至可以使用其他选择列表项的别名，但需注意作用域和解析顺序。
     选择列表是 SQL 查询的核心部分，它直接定义了查询输出的结构。通过精心设计选择列表，可以有效地从数据库中提取需要的信息，并进行必要的数据转换和计算。

7.3.2. Column Labels

列别名用于改进查询结果的可读性，或者当查询的结果被用作其他查询的一部分时，为了简化引用或解决列名冲突。

1. 基本语法：

   • 为列指定别名的基本语法是在列名或计算表达式后使用 AS 关键字，后跟别名，例如：SELECT column_name AS alias_name FROM table_name;。AS 关键字是可选的，可以省略，直接写列名后跟别名也是可以的。

2. 用途和好处：

   • 使用列别名可以使查询结果更加清晰易懂。例如，当列名是数据库中的技术字段名时，别名可以提供更具描述性的名称。
   • 在进行计算或使用函数时，为结果指定一个别名可以提供更有意义的列名，而不是默认的表达式文本或函数名。
   • 列别名在连接多个表时特别有用，尤其是当不同表中的列名相同时，可以通过别名区分来自不同表的列。

3. 示例：

   • SELECT first_name AS "First Name", last_name AS "Last Name" FROM employees; 这个查询为 first_name 和 last_name 列指定了易读的别名。
   • SELECT COUNT(*) AS "Total Employees" FROM employees; 这个查询计算员工总数，并将结果列标记为 “Total Employees”。

4. 注意事项：

   • 如果列别名与 SQL 关键字冲突，或者别名中包含空格、特殊字符，或者希望别名是大小写敏感的，则需要用双引号将别名括起来。
   • 在查询的其他部分（如 ORDER BY 子句）中引用列别名时，必须使用别名而不是原始的列名或表达式。
     通过使用列别名，可以提高 SQL 查询结果的可读性和灵活性，使数据分析和报告更加直观。

7.3.3. DISTINCT

在 SQL 查询中使用 DISTINCT 关键字来去除重复的行，确保查询结果中的每一行都是唯一的。

1. 基本用法：
   • 在 SELECT 语句中使用 DISTINCT 关键字可以去除结果集中的重复行。基本语法是在 SELECT 关键字之后紧接着写上 DISTINCT，例如：SELECT DISTINCT column1, column2 FROM table_name;。
2. 作用机制：
   • 当使用 DISTINCT 时，数据库会对结果集中的所有行进行比较，只保留唯一的行，即那些在所有指定列上值都不相同的行。如果指定了多个列，那么这些列的组合值将被用来确定行的唯一性。
3. 与其他子句的结合：
   • DISTINCT 可以与 WHERE 子句结合使用，先过滤行，然后去除重复项。但是，它通常不能直接与聚合函数（如 SUM、COUNT 等）结合使用，除非聚合函数是在子查询中使用。
4. DISTINCT ON (expression)：
   • PostgreSQL 提供了 DISTINCT ON (expression) 语法，允许你指定基于哪些表达式或列来去除重复项。这种方式更加灵活，可以在保留一行数据的同时，根据特定列的值去除重复项，例如：SELECT DISTINCT ON (column1) column1, column2 FROM table_name ORDER BY column1, column2;。
   • 需要注意的是，使用 DISTINCT ON 时，通常需要配合 ORDER BY 子句来确保结果的顺序性，因为 DISTINCT ON 会保留基于 ORDER BY 排序后的第一条记录。
5. 应用场景：
   • DISTINCT 关键字在数据分析和报告中非常有用，特别是当需要从包含重复数据的大型数据集中提取唯一值列表时。
   • 它也常用于与 COUNT 函数结合，计算某列或某几列组合的唯一值数量。
     使用 DISTINCT 关键字可以有效地清理数据，去除不必要的重复，使得分析结果更加准确和清晰。

7.4. Combining Queries (UNION, INTERSECT,EXCEPT)

使用 UNION、INTERSECT 和 EXCEPT 关键字来组合多个查询的结果。这些关键字允许你在一个单独的结果集中合并、交叉或排除来自不同查询的行。

1. UNION：
   • UNION 关键字用于合并两个或多个查询的结果集，去除重复的行。如果希望保留所有重复行，可以使用 UNION ALL。
   • 例如：SELECT column_name FROM table1 UNION SELECT column_name FROM table2; 将返回 table1 和 table2 中 column_name 列的唯一值。
2. INTERSECT：
   • INTERSECT 关键字用于返回两个查询共有的行，即两个结果集的交集。INTERSECT ALL 会包含所有重复的交集行。
   • 例如：SELECT column_name FROM table1 INTERSECT SELECT column_name FROM table2; 将返回同时存在于 table1 和 table2 中 column_name 列的行。
3. EXCEPT：
   • EXCEPT 关键字用于从第一个查询的结果集中排除掉第二个查询结果集中存在的行，即返回两个结果集的差集。EXCEPT ALL 会包含所有重复的差集行。
   • 例如：SELECT column_name FROM table1 EXCEPT SELECT column_name FROM table2; 将返回仅存在于 table1 中 column_name 列的行，排除掉同时存在于 table2 中的行。
4. 组合查询的要求：
   • 为了使用 UNION、INTERSECT 或 EXCEPT，每个查询必须返回相同数量的列，并且对应列的数据类型必须兼容或相同。
5. 使用场景：
   • 这些关键字在数据分析时非常有用，特别是当需要合并来自不同数据源的数据、找出共有数据或排除特定数据时。
6. 注意事项：
   • 当使用这些关键字组合查询时，只有最外层的查询可以使用 ORDER BY 子句来排序最终的结果集。如果需要对中间结果进行排序，可以在子查询中使用 ORDER BY 并将其包裹在括号内。
     通过组合查询，你可以灵活地处理和分析数据，实现复杂的数据操作和分析目标。

7.5. Sorting Rows (ORDER BY)

使用 ORDER BY 子句在 SQL 查询中对结果集进行排序。ORDER BY 子句允许你根据一个或多个列的值将查询结果按升序或降序排列，从而使得结果集的顺序更加有意义和易于理解。

1. 基本语法：
   • ORDER BY 子句通常位于查询语句的末尾，其后跟随一个或多个排序条件。每个排序条件可以是列名或者是列的位置编号，并且可以指定 ASC（升序，默认）或 DESC（降序）来定义排序方向。
   • 例如：SELECT column1, column2 FROM table_name ORDER BY column1 ASC, column2 DESC; 会先按 column1 升序排列，对于 column1 相同的行，则按 column2 降序排列。
2. 排序空值：
   • ORDER BY 子句还可以指定 NULLS FIRST 或 NULLS LAST 来控制空值（NULL）的排序位置，即是让空值排在最前面还是最后面。
   • 例如：SELECT column1 FROM table_name ORDER BY column1 ASC NULLS LAST; 在按 column1 升序排列的同时，将所有空值排在非空值之后。
3. 使用列别名和表达式：
   • 在 ORDER BY 子句中，你可以使用 SELECT 列表中定义的别名来指定排序条件，也可以直接使用表达式进行排序。
   • 例如：SELECT column1 AS c1 FROM table_name ORDER BY c1; 使用列别名作为排序条件。
4. 注意事项：
   • 如果查询中包含 GROUP BY 子句，那么 ORDER BY 只能使用 GROUP BY 中的列或聚合函数进行排序。
   • 在使用 UNION、INTERSECT、EXCEPT 等组合查询时，ORDER BY 应用于最终的结果集，而不是单独的子查询。
5. 应用场景：
   • ORDER BY 子句在数据报告和分析中非常重要，它确保了数据以一种可预测和有用的方式展示，特别是当需要按时间、字母顺序或其他标准展示数据时。
     通过使用 ORDER BY 子句，可以确保查询结果的顺序满足特定的需求，从而提高数据的可读性和分析的有效性。

7.6. LIMIT and OFFSET

在 SQL 查询中使用 LIMIT 和 OFFSET 子句来限制查询结果集的大小以及从哪一行开始返回结果。这两个子句通常用于实现分页功能，允许用户在大量数据中浏览和访问特定的数据子集。

1. LIMIT 子句：
   • LIMIT 子句用于限制查询返回的行数。这在查询大型数据库时特别有用，可以减少数据传输量，提高查询性能。
   • 例如：SELECT column_name FROM table_name LIMIT 10; 会返回表中前 10 行的数据。
2. OFFSET 子句：
   • OFFSET 子句用于指定从哪一行开始返回结果。OFFSET 通常与 LIMIT 结合使用，以实现数据的分页显示。
   • 例如：SELECT column_name FROM table_name LIMIT 10 OFFSET 20; 会跳过前 20 行，然后返回从第 21 行开始的 10 行数据。
3. 结合使用 LIMIT 和 OFFSET：
   • 结合使用 LIMIT 和 OFFSET 可以非常灵活地访问数据库中的数据，尤其是在需要分页显示大量数据时。
   • 在实现分页功能时，可以根据用户请求的页码和每页显示的行数计算出相应的 LIMIT 和 OFFSET 值。
4. 注意事项：
   • 使用 LIMIT 和 OFFSET 时，应该总是配合 ORDER BY 子句使用，以确保结果的顺序是确定的。否则，数据库系统可能以任意顺序返回行，这在分页场景下可能导致数据显示不一致。
   • OFFSET 的计算基于 0 开始，即 OFFSET 0 表示从第一行数据开始。
5. 性能考虑：
   • 虽然 OFFSET 可以用于跳过一定数量的行，但如果 OFFSET 值非常大，可能会导致性能问题，因为数据库需要读取并跳过这些行才能获取到实际需要的数据。对于非常大的数据集，可能需要考虑其他策略来优化性能。
     LIMIT 和 OFFSET 子句为处理大型数据集提供了强大的工具，特别是在需要分页或仅需部分数据进行分析时。

7.7. VALUES Lists

在 SQL 查询中使用 VALUES 语句来创建一组临时的行集合，这些行集合可以被用作查询中的数据源。VALUES 列表提供了一种生成“常量表”（constant table）的方式，无需在数据库中实际创建和填充表。

1. 基本用法：
   • VALUES 列表的基本语法是 VALUES (表达式1, 表达式2, ...), (...), ...;，其中每一组括号内的表达式列表代表一行数据，多组数据之间用逗号分隔。
   • 例如：VALUES (1, one), (2, two), (3, three); 创建了一个包含三行两列的临时表。
2. 与查询结合：
   • VALUES 列表可以独立使用，也可以作为更大查询的一部分，例如作为 INSERT 语句的数据源，或者与 SELECT 语句结合使用。
   • 例如：INSERT INTO table_name(column1, column2) VALUES (1, one), (2, two); 使用 VALUES 列表直接向表中插入数据。
3. 作为临时表：
   • 在复杂的查询中，可以使用 VALUES 列表来创建临时表，然后在查询的其他部分引用这个临时表。
   • 例如：WITH temp_table AS (VALUES (1, one), (2, two)) SELECT * FROM temp_table; 这里，VALUES 列表被用作 WITH 子句中的临时表。
4. 指定列别名：
   • 当将 VALUES 列表用作查询的一部分时，可以通过在 VALUES 之后添加 AS 子句来为生成的临时表指定列名。
   • 例如：SELECT * FROM (VALUES (1, one), (2, two)) AS temp_table(column1, column2); 这里指定了临时表的列别名为 column1 和 column2。
5. 应用场景：
   • VALUES 列表在需要在查询中直接使用一组固定值时非常有用，特别是在测试、插入少量数据，或者构建复杂查询中临时使用的小型数据集时。
     VALUES 列表提供了一种灵活的方式来在查询中直接构造和使用数据集，无需预先在数据库中创建和填充表。

7.8. WITH Queries (Common Table Expressions)

使用 WITH 语句在 SQL 查询中定义一种或多种临时的结果集，这些结果集在 SQL 语句的执行过程中就像临时表一样可以被引用。CTEs 提供了一种强大的方式来组织复杂查询，使其更加易读和维护。

1. 基本概念：
   • CTEs 是通过 WITH 关键字引入的，允许你在一个查询中定义一个或多个临时的结果集（CTE）。每个 CTE 都可以在主查询或其他 CTE 中被引用。
   • 例如：WITH cte_name AS (SELECT column1 FROM table_name) SELECT * FROM cte_name; 定义了一个名为 cte_name 的 CTE，并在主查询中引用了它。
2. 递归 CTEs：
   • 递归 CTEs 允许查询引用自身来执行递归操作，这对于处理层次或递归数据（如组织结构、目录树）非常有用。
   • 递归 CTE 的基本形式包括一个锚定成员（基础情况）和一个递归成员，两者通过 UNION ALL 连接。
   • 例如：WITH RECURSIVE cte_name AS (SELECT column1 FROM table_name WHERE condition UNION ALL SELECT t.column1 FROM table_name t JOIN cte_name c ON t.column2 = c.column1) SELECT * FROM cte_name;。
3. 多个 CTEs：
   • 在一个 WITH 语句中可以定义多个 CTE，彼此之间用逗号分隔。这允许在一个查询中构建多个层次或相互独立的数据集。
   • 例如：WITH cte1 AS (SELECT column1 FROM table1), cte2 AS (SELECT column2 FROM table2) SELECT * FROM cte1 JOIN cte2 ON cte1.column1 = cte2.column2;。
4. 使用场景和好处：
   • CTEs 使得复杂查询的组织和理解变得更加容易，特别是当查询涉及多个步骤或需要重用某个查询结果多次时。
   • 它们提供了一种将查询分解成逻辑块的方法，有助于提高 SQL 代码的清晰度和可维护性。
5. 数据修改操作：
   • CTEs 也可以用于数据修改操作（如 INSERT、UPDATE、DELETE），这为执行多步数据变更操作提供了灵活性。
     通过使用 WITH 语句和 CTEs，开发者可以构建出结构清晰、易于理解和维护的复杂 SQL 查询。

7.8.1. SELECT in WITH

在 WITH 语句（也称为公用表表达式或 CTE）中使用 SELECT 语句。WITH 语句允许你将复杂的 SQL 查询分解为更易于管理的部分，通过定义一个或多个临时的结果集（CTE），这些结果集在整个查询中可以被引用。

1. 基本用法：
   • 在 WITH 语句中定义的临时结果集可以通过 SELECT 语句创建，这些结果集在查询的后续部分就像临时表一样被引用。
   • 例如：WITH cte_name AS (SELECT column1, column2 FROM table_name WHERE condition) SELECT * FROM cte_name; 在这个例子中，cte_name 是一个 CTE，它包含了从 table_name 表中根据某个条件筛选出的列，然后在主查询中被引用。
2. 提高可读性和组织性：
   • 使用 WITH 语句可以使复杂查询的结构更加清晰，特别是当查询涉及多个步骤或层次时。每个 CTE 都可以看作是查询逻辑的一个模块，使得整个查询更容易理解和维护。
3. 复用和引用：
   • 在一个查询中定义的 CTE 可以被后续的 SELECT、INSERT、UPDATE 或 DELETE 语句引用。这意味着你可以在查询的不同部分复用同一个 CTE，避免了重复的查询逻辑和数据处理。
4. 多个 CTEs：
   • 你可以在一个 WITH 语句中定义多个 CTE，彼此之间用逗号分隔。这为构建相互依赖或独立的多个数据集提供了方便。
   • 例如：WITH cte1 AS (SELECT column1 FROM table1), cte2 AS (SELECT column2 FROM table2 WHERE column2 IN (SELECT column1 FROM cte1)) SELECT * FROM cte2; 在这个例子中，cte2 引用了 cte1 的结果。
5. 递归查询：
   • WITH 语句还支持递归查询，这对于处理层次数据或需要递归逻辑的场景非常有用。递归 CTE 包含一个初始查询（锚点）和一个递归查询，它们通过 UNION ALL 连接。
     通过使用 WITH 语句和其中的 SELECT 查询，开发者可以构建出更加模块化、易于管理和理解的 SQL 查询。

7.8.2. Recursive Queries

在 SQL 中使用递归公用表表达式（CTEs）来执行递归查询。递归查询特别适用于处理具有层次结构的数据，如树结构或图结构，允许查询在自身基础上重复执行，直到满足某个终止条件。

1. 基本概念：
   • 递归 CTE 由两部分组成：基础部分（非递归部分）和递归部分，两者通过 UNION ALL 连接。基础部分为递归提供起始点，而递归部分包含对 CTE 自身的引用，实现递归逻辑。
2. 递归查询的结构：
   • 使用 RECURSIVE 关键字来标识 CTE 是递归的。递归 CTE 的一般形式如下：

     
     WITH RECURSIVE cte_name AS (
       -- 基础部分
     
       SELECT ...
       UNION ALL
     
       -- 递归部分，cte_name 在这里被引用
     
       SELECT ... FROM cte_name WHERE ...
     )
     SELECT * FROM cte_name;
     复制

3. 递归查询的执行过程：
   • 首先执行基础部分，产生初始的结果集。
   • 然后，递归部分使用基础部分的结果作为输入，执行并产生新的结果。
   • 这个过程重复执行，每次都将上一次递归部分的结果作为新的输入，直到递归部分不再产生新的结果为止。
4. 应用场景：
   • 递归查询常用于处理树或图形结构的数据，例如查询组织结构中的所有下属部门、获取社交网络中的朋友网络、或者查找文件系统中的所有文件和目录。
5. 示例：
   • 假设有一个组织结构表，包含部门ID和上级部门ID，要查询某个部门及其所有下属部门，可以使用递归 CTE：

     
     WITH RECURSIVE sub_depts AS (
       SELECT dept_id, parent_dept_id FROM departments WHERE dept_id = ?
       UNION ALL
     
       SELECT d.dept_id, d.parent_dept_id FROM departments d
     
       JOIN sub_depts sd ON d.parent_dept_id = sd.dept_id
     
     )
     SELECT * FROM sub_depts;
     复制

递归 CTE 为 SQL 提供了强大的递归处理能力，使得在关系数据库中处理层次或递归数据结构成为可能。

7.8.2.1. Search Order

在执行递归公用表表达式（CTEs）时，如何通过计算一个排序列来实现结果的深度优先搜索（Depth-First Search, DFS）或广度优先搜索（Breadth-First Search, BFS）顺序。这对于控制递归查询结果的顺序非常有用，尤其是当处理层次化数据或需要特定遍历顺序时。

1. 深度优先搜索顺序：
   • 在深度优先搜索中，查询会优先深入到每个分支的最底部，再回溯到下一个分支。为了实现这一点，可以在递归 CTE 中计算一个路径数组或字符串，该数组或字符串在每一步递归中都会被扩展，用以记录到达当前节点的路径。
   • 示例：使用数组记录路径，实现深度优先搜索顺序。

     
     WITH RECURSIVE search_path AS (
       SELECT id, ARRAY[id] AS path
     
       FROM nodes
     
       WHERE parent_id IS NULL
     
       UNION ALL
     
       SELECT n.id, sp.path || n.id
     
       FROM nodes n
     
       JOIN search_path sp ON n.parent_id = sp.id
     
     )
     SELECT id, path FROM search_path ORDER BY path;
     复制

2. 广度优先搜索顺序：
   • 在广度优先搜索中，查询会先访问起始节点的所有直接子节点，然后再对每个子节点做同样的处理。为了实现这一点，可以在递归 CTE 中添加一个表示深度的列，每当进入新的递归层级时，深度值增加。
   • 示例：使用深度列，实现广度优先搜索顺序。

     
     WITH RECURSIVE search_level AS (
       SELECT id, 1 AS depth
     
       FROM nodes
     
       WHERE parent_id IS NULL
     
       UNION ALL
     
       SELECT n.id, sl.depth + 1
     
       FROM nodes n
     
       JOIN search_level sl ON n.parent_id = sl.id
     
     )
     SELECT id FROM search_level ORDER BY depth;
     复制

3. 排序稳定性：
   • 为了获得稳定的排序结果，除了根据路径或深度排序外，还可以根据其他列（如节点ID）作为次要排序条件，以确保在相同路径或深度的节点之间有确定的顺序。
     通过在递归查询中添加路径或深度信息，开发者可以控制查询结果的遍历顺序，使其符合特定的业务逻辑或数据分析需求。

7.8.2.2. Cycle Detection

在执行递归公用表表达式（CTEs）时，如何检测并处理潜在的循环引用，以避免无限递归的情况。循环引用发生在递归查询中，当数据结构允许从某个节点出发，经过一系列的步骤后，再次回到该节点，形成一个闭环。在处理层次或图形结构的数据时，循环检测变得尤为重要。

1. 基本概念：
   • 为了防止递归查询无限循环，可以在递归 CTE 中添加额外的逻辑来检测并避免循环。常见的方法是在递归过程中跟踪已访问的节点，并在尝试重新访问这些节点时终止递归。
2. 实现方法：
   • 一个常见的实现方式是使用数组或列表来存储递归过程中访问过的节点ID，每次递归时检查当前节点是否已在该数组中。如果发现当前节点已被访问过，则识别出循环，并可以选择性地避免进一步递归该路径。
   • 示例代码片段：

     
     WITH RECURSIVE cte AS (
       SELECT id, parent_id, ARRAY[id] AS path
     
       FROM my_table
     
       WHERE parent_id IS NULL  -- 起始条件
     
       UNION ALL
     
       SELECT m.id, m.parent_id, path || m.id
     
       FROM my_table m
     
       JOIN cte ON m.parent_id = cte.id
     
       WHERE NOT m.id = ANY(cte.path)  -- 避免循环
     
     )
     SELECT * FROM cte;
     复制

     在这个示例中，path 数组用于存储从起始节点到当前节点的路径。通过检查 m.id 是否已存在于 path 中，可以避免循环。
3. 注意事项：
   • 在实现循环检测时，需要注意性能问题，因为数组的大小和检查数组中元素的操作可能会随着递归深度的增加而变得昂贵。
   • 在某些情况下，可能需要在发现循环时采取特定的行动，例如记录循环发生的情况或调整查询逻辑以避免这些路径。
     通过在递归 CTEs 中实现循环检测，可以确保递归查询在面对复杂的数据结构时能够正确终止，避免无限循环带来的问题。

7.8.3. Common Table Expression Materialization

公用表表达式（CTE）的物化（materialization）行为及其对查询性能的影响。物化是指在查询执行过程中，将 CTE 的结果集临时存储在内存或磁盘上，以便后续操作可以重复使用这些结果，而不是重新执行 CTE 中的查询。

1. 物化的基本概念：
   • 物化可以提高查询效率，特别是当一个 CTE 被查询中的多个部分引用时。通过物化 CTE，可以避免对同一个 CTE 的重复计算，从而节省计算资源和执行时间。
   • 然而，物化也可能增加内存或磁盘的使用量，因为需要存储 CTE 的结果集。
2. 物化的控制：
   • PostgreSQL 允许开发者通过在 CTE 声明中使用 MATERIALIZED 或 NOT MATERIALIZED 提示来显式控制 CTE 的物化行为。
   • MATERIALIZED 提示强制 PostgreSQL 物化 CTE 的结果集，这在你知道一个 CTE 会被多次引用时很有用。
   • NOT MATERIALIZED 提示告诉 PostgreSQL 不要物化 CTE 的结果集，而是每次需要时重新计算。这可以在 CTE 被引用次数少或计算成本低时减少资源使用。
3. 默认行为：
   • PostgreSQL 的默认行为是自动选择是否物化 CTE 的结果集。这个决策基于查询优化器的成本估算，旨在平衡执行时间和资源使用。
   • 对于非递归 CTE，如果它只被引用一次，通常不会物化；如果被多次引用，则可能会物化以避免重复计算。
4. 性能考虑：
   • 在决定是否显式指定 CTE 的物化行为时，需要考虑查询的具体情况。如果 CTE 非常复杂且被多次引用，强制物化可能会提高性能。相反，如果 CTE 简单或仅被引用一次，不物化可能更有效率。
   • 显式控制物化行为也使得查询的性能表现更可预测，因为它不再依赖于查询优化器的动态决策。
     通过理解和合理利用 CTE 的物化行为，开发者可以在提高查询性能和节省资源使用之间做出更好的平衡。

7.8.4. Data-Modifying Statements in WITH

在公用表表达式（CTE）中使用数据修改语句，包括 INSERT、UPDATE 和 DELETE 操作。这个特性允许开发者在单个查询中执行多个数据修改操作，以及将这些操作与数据检索操作结合起来，从而提高了 SQL 语句的灵活性和表达能力。

1. 基本用法：
   • 在 WITH 语句中，除了可以定义常规的 SELECT 查询外，还可以包含数据修改语句。这些数据修改语句可以引用其他在同一个 WITH 语句中定义的 CTE，也可以被主查询引用。
   • 例如：

     
     WITH updated AS (
       UPDATE my_table SET column1 = value1 WHERE condition RETURNING id
     
     )
     INSERT INTO another_table (column2)
     SELECT id FROM updated;
     复制

     在这个例子中，首先执行 UPDATE 操作，并通过 RETURNING 子句返回被更新行的 id。然后，这些 id 被用于随后的 INSERT 操作。
2. 数据修改和检索结合：
   • 数据修改语句在 WITH 中的使用，允许在执行数据修改操作的同时进行数据检索，这对于需要基于修改结果进行进一步处理的场景非常有用。
   • 例如，可以在更新一组记录后，立即选择这些记录的新状态，或者在删除记录后，将删除的记录插入到另一个表中作为备份。
3. 递归与数据修改：
   • 虽然递归 CTE 通常用于 SELECT 查询，但你也可以在递归查询的基础上执行数据修改操作。这为处理层次数据提供了额外的灵活性。
4. 注意事项：
   • 在 WITH 语句中使用数据修改语句时，所有的修改操作和数据检索操作都在同一个事务中执行。这意味着，如果查询的任何部分失败，整个事务都会回滚。
   • 数据修改语句在 WITH 中的执行顺序不能保证。因此，如果多个数据修改操作之间有依赖关系，需要仔细设计以确保操作的正确性。
     通过在 WITH 语句中包含数据修改语句，SQL 提供了一种强大的机制来组织复杂的数据库操作，使得可以在单个查询中完成多个步骤，同时保持代码的清晰和结构化。