为什么在此查询中不能使用WITH（公用表表达式）？ [英] why can't I use WITH (Common Table Expressions) in this query?

问题描述

这只是一个SSCCE：

This is just an SSCCE:

CREATE TABLE test(i INTEGER NOT NULL);

WITH max_i AS (SELECT MAX(i) FROM test)
SELECT * FROM test WHERE max_i - i < 2 AND max_i!=i

PostgreSQL抱怨：

PostgreSQL complains:

ERROR: column "max_i" does not exist

我想这是因为 max_i 是单个值而不是行集，但是如何在我通过复杂查询获得的查询值中仅定义一次，而不必

I guess that's because max_i is a single value and not a rowset, but how do I get to define only once in my query values that I obtain through complex queries instead of having to repeat the subquery whenever they are used?

推荐答案

在最终选择语句中根本没有引用CTE。因此，您无法引用它。另外， where 条件需要引用列而不是表。

You don't reference the CTE at all in your "final" select statement. Therefor you can't reference it. Additionally the where condition needs to reference columns not tables.

在您的语句中 max_i 是表的名称，因此不能在where条件下使用。

In your statement max_i is the name of a "table", thus it cannot be used in a where condition.

WITH t_max AS (
   SELECT MAX(i) as max_i FROM test
)
SELECT * 
FROM test 
  CROSS JOIN t_max as t
WHERE t.max_i - test.i < 2 
  AND t.max_i <> test.i;

交叉连接不执行任何操作伤害，因为我们知道CTE（名为 t_max ）将始终仅返回一行。

The cross join doesn't do any harm because we know the CTE (named t_max) will always return only a single row.

一旦您可以在最终选择中引用CTE，比较就很容易了，但是要做到这一点，您需要为CTE内部选择的列提供别名。

Once you can reference the CTE in the final select, the comparison is easy, but to do that you need to provide an alias for the column of the CTE inner select.

这篇关于为什么在此查询中不能使用WITH（公用表表达式）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！