如何在一个PostgreSQL查询中使用多个WITH语句？ [英] How to use multiple WITH statements in one PostgreSQL query?

问题描述

我想使用WITH语句声明哪些实际上是多个TEMP表。
我要执行的查询如下：

I would like to "declare" what are effectively multiple TEMP tables using the WITH statement. The query I am trying to execute is along the lines of:

WITH table_1 AS (
SELECT GENERATE_SERIES('2012-06-29', '2012-07-03', '1 day'::INTERVAL) AS date
)

WITH table_2 AS (
SELECT GENERATE_SERIES('2012-06-30', '2012-07-13', '1 day'::INTERVAL) AS date
)

SELECT * FROM table_1
WHERE date IN table_2

我已阅读 PostgreSQL文档，并研究使用多个 WITH 语句，但找不到答案。

I've read PostgreSQL documentation and researched into using multiple WITH statements and was unable to find an answer.

推荐答案

在其他注释中，第二个公共表表达式[CTE]前面是逗号而不是WITH语句，所以

Per the other comments the second Common Table Expression [CTE] is preceded by a comma not a WITH statement so

WITH cte1 AS (SELECT...)
, cte2 AS (SELECT...)
SELECT *
FROM
    cte1 c1
    INNER JOIN cte2 c2
    ON ........

就您的实际查询而言，此语法应在PostgreSql，Oracle和sql-server中起作用，那么以后通常您将继续进行 以分号（; WTIH ），但这是因为通常sql-server人员（包括我自己）不会结束需要在CTE之前结束的先前语句被定义...

In terms of your actual query this syntax should work in PostgreSql, Oracle, and sql-server, well the later typically you will proceed WITH with a semicolon (;WTIH), but that is because typically sql-server folks (myself included) don't end previous statements which need to be ended prior to a CTE being defined...

但是请注意，关于 WHERE 语句，您还有第二个语法问题。 表_2中的日期无效，因为您从未实际引用表_2中的值/列。我更喜欢 INNER JOIN 比 IN 或 Exists ，所以这是可以与 JOIN 一起使用的语法：

Note however that you had a second syntax issue in regards to your WHERE statement. WHERE date IN table_2 is not valid because you never actually reference a value/column from table_2. I prefer INNER JOIN over IN or Exists so here is a syntax that should work with a JOIN:

WITH table_1 AS (
SELECT GENERATE_SERIES('2012-06-29', '2012-07-03', '1 day'::INTERVAL) AS date
)

, table_2 AS (
SELECT GENERATE_SERIES('2012-06-30', '2012-07-13', '1 day'::INTERVAL) AS date
)

SELECT * 
FROM
     table_1 t1
     INNER JOIN 
     table_2 t2
     ON t1.date = t2.date
;

如果您想保持原样，通常EXISTS会比IN更好，但对于在IN中使用IN则需要一个实际的SELECT语句。

If you want to keep the way you had it which typically EXISTS would be better than IN but to to use IN you need an actual SELECT statement in your where.

SELECT * 
FROM
     table_1 t1
WHERE t1.date IN (SELECT date FROM table_2);

当日期可能存在问题时，IN非常有问题是 NULL ，因此，如果您不想使用 JOIN ，那么我建议 EXISTS 。如以下所示：

IN is very problematic when date could potentially be NULL so if you don't want to use a JOIN then I would suggest EXISTS. AS follows:

SELECT * 
FROM
     table_1 t1
WHERE EXISTS (SELECT * FROM table_2 t2 WHERE t2.date = t1.date);

这篇关于如何在一个PostgreSQL查询中使用多个WITH语句？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！