SQL 如何在一个 PostgreSQL 查询中使用多个 WITH 语句？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38136854/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use multiple WITH statements in one PostgreSQL query?
提问by Greg
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 documentationand researched into using multiple WITHstatements and was unable to find an answer.
我已经阅读了PostgreSQL 文档并研究了使用多个WITH语句，但无法找到答案。
回答by Matt
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 WITHwith 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 中工作，后者通常您将继续WITH使用分号 ( ;WTIH)，但那是因为通常 sql-server 人员（包括我自己）不会结束需要在定义 CTE 之前结束的先前语句...
Note however that you had a second syntax issue in regards to your WHEREstatement. WHERE date IN table_2is not valid because you never actually reference a value/column from table_2. I prefer INNER JOINover INor Existsso here is a syntax that should work with a JOIN:
但是请注意，您的WHERE语句存在第二个语法问题。 WHERE date IN table_2无效，因为您从未实际引用 table_2 中的值/列。我喜欢INNER JOIN过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，你需要在你的 where 中使用实际的 SELECT 语句。
SELECT *
FROM
table_1 t1
WHERE t1.date IN (SELECT date FROM table_2);
IN is very problematic when datecould potentially be NULLso if you don't want to use a JOINthen I would suggest EXISTS. AS follows:
INdate可能会出现问题，NULL如果您不想使用，JOIN那么我建议EXISTS. 如下：
SELECT *
FROM
table_1 t1
WHERE EXISTS (SELECT * FROM table_2 t2 WHERE t2.date = t1.date);
回答by Suz'l Shrestha
You can also chain your results using WITH statement. Eg:
您还可以使用 WITH 语句链接结果。例如：
WITH tab1 as (Your SQL statement),
WITH tab2 as ( SELECT ... FROM tab1 WHERE your filter),
WITH tab3 as ( SELECT ... FROM tab2 WHERE your filter)
SELECT * FROM tab3;
