如何在一个PostgreSQL查询中使用多个WITH语句？

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语句“声明”什么是有效的多个TEMP表。我试图执行的查询是：

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

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

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

1 个解决方案

#1

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

根据其他注释，第二个公用表表达式[CTE]前面是逗号，而不是WITH语句

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

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...

就你的实际查询而言，这个语法应该在PostgreSql，Oracle和sql-server中运行，以后通常你会用分号（; WTIH）继续使用，但这是因为通常sql-server伙伴（包括我自己）不要结束之前需要在CTE定义之前结束的陈述......

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:

但请注意，您的WHERE语句有第二个语法问题。 WHERE日期IN table_2无效，因为您实际上从未实际引用table_2中的值/列。我更喜欢INNER JOIN over IN或Exists所以这里的语法应该适用于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
;

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.

如果你想保持你拥有它的方式，通常EXISTS会优于IN，但要使用IN，你需要在你的位置使用一个实际的SELECT语句。

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

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:

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

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

#1