本文属于机器翻译版本。若本译文内容与英语原文存在差异，则一律以英文原文为准。

WITH 子句

子WITH句是位于查询SELECT列表之前的可选子句。该WITH子句定义了一个或多个公用表表达式。每个公用表表达式 (CTE) 都定义一个临时表，该表类似于视图定义。可以在FROM子句中引用这些临时表。它们仅在它们所属的查询运行时使用。WITH子句CTE中的每一个都指定一个表名、一个可选的列名列表和一个计算结果为表的查询表达式（SELECT语句）。

WITH子句子查询是一种定义表的有效方法，可以在单个查询的整个执行过程中使用这些表。在所有情况下，通过在SELECT语句的主体中使用子查询可以获得相同的结果，但是子WITH句子查询的编写和读取可能更简单。在可能的情况下，多次引用的子WITH句子查询会被优化为常见的子表达式；也就是说，可以对WITH子查询进行一次求值并重复使用其结果。（请注意，常见的子表达式不仅限于子句中定义的子表达式。）WITH

语法

[ WITH common_table_expression [, common_table_expression , ...] ]

其中 common_table_expression 可以是非递归的。以下是非递归形式：

CTE_table_name AS ( query )

参数

使用说明

可以在以下WITH语句中使用子SQL句：

如果包含FROM子句的查询子WITH句未引用该子句定义的任何表，则该WITH子WITH句将被忽略，查询将照常运行。

由子WITH句子查询定义的表只能在该子WITH句开始的SELECT查询范围内引用。例如，可以在SELECT列表、子句或HAVING子FROM句中子查询的子WHERE句中引用这样的表。您不能在子查询中使用子WITH句并在主查询或其他子查询的FROM子句中引用其表。此查询模式会导致WITH子句表中出现格式relation table_name doesn't exist为的错误消息。

不能在WITH子句子查询中指定其他子WITH句。

您不能向前引用由子WITH句子查询定义的表。例如，以下查询返回一个错误，因为在表 W1 的定义中对表 W2 进行了前向引用：

with w1 as (select * from w2), w2 as (select * from w1)
select * from sales;
ERROR:  relation "w2" does not exist

示例

以下示例显示了包含WITH子句的查询的最简单情况。名为的WITH查询VENUECOPY选择VENUE表中的所有行。反过来，主查询会从中选择所有行VENUECOPY。该VENUECOPY表仅在此查询期间存在。

with venuecopy as (select * from venue)
select * from venuecopy order by 1 limit 10;

 venueid |         venuename          |    venuecity    | venuestate | venueseats
---------+----------------------------+-----------------+------------+------------
1 | Toyota Park                | Bridgeview      | IL         |          0
2 | Columbus Crew Stadium      | Columbus        | OH         |          0
3 | RFK Stadium                | Washington      | DC         |          0
4 | CommunityAmerica Ballpark  | Kansas City     | KS         |          0
5 | Gillette Stadium           | Foxborough      | MA         |      68756
6 | New York Giants Stadium    | East Rutherford | NJ         |      80242
7 | BMO Field                  | Toronto         | ON         |          0
8 | The Home Depot Center      | Carson          | CA         |          0
9 | Dick's Sporting Goods Park | Commerce City   | CO         |          0
v     10 | Pizza Hut Park             | Frisco          | TX         |          0
(10 rows)

以下示例显示了一个WITH子句，该子句生成了两个名为 VENUE _ SALES 和 TOP _ 的表VENUES。第二个WITH查询表从第一个查询表中进行选择。反过来，主查询块的子WHERE句包含一个约束 TOP _ VENUES 表的子查询。

with venue_sales as
(select venuename, venuecity, sum(pricepaid) as venuename_sales
from sales, venue, event
where venue.venueid=event.venueid and event.eventid=sales.eventid
group by venuename, venuecity),

top_venues as
(select venuename
from venue_sales
where venuename_sales > 800000)

select venuename, venuecity, venuestate,
sum(qtysold) as venue_qty,
sum(pricepaid) as venue_sales
from sales, venue, event
where venue.venueid=event.venueid and event.eventid=sales.eventid
and venuename in(select venuename from top_venues)
group by venuename, venuecity, venuestate
order by venuename;

        venuename       |   venuecity   | venuestate | venue_qty | venue_sales
------------------------+---------------+------------+-----------+-------------
August Wilson Theatre   | New York City | NY         |      3187 |  1032156.00
Biltmore Theatre        | New York City | NY         |      2629 |   828981.00
Charles Playhouse       | Boston        | MA         |      2502 |   857031.00
Ethel Barrymore Theatre | New York City | NY         |      2828 |   891172.00
Eugene O'Neill Theatre  | New York City | NY         |      2488 |   828950.00
Greek Theatre           | Los Angeles   | CA         |      2445 |   838918.00
Helen Hayes Theatre     | New York City | NY         |      2948 |   978765.00
Hilton Theatre          | New York City | NY         |      2999 |   885686.00
Imperial Theatre        | New York City | NY         |      2702 |   877993.00
Lunt-Fontanne Theatre   | New York City | NY         |      3326 |  1115182.00
Majestic Theatre        | New York City | NY         |      2549 |   894275.00
Nederlander Theatre     | New York City | NY         |      2934 |   936312.00
Pasadena Playhouse      | Pasadena      | CA         |      2739 |   820435.00
Winter Garden Theatre   | New York City | NY         |      2838 |   939257.00
(14 rows)

以下两个示例演示了基于子WITH句子查询的表引用范围的规则。第一个查询运行，但第二个查询失败，并出现意料中的错误。第一个查询在主查询的SELECT列表中包含子WITH句子查询。WITH子句 (HOLIDAYS) 定义的表在SELECT列表中子查询的子FROM句中被引用：

select caldate, sum(pricepaid) as daysales,
(with holidays as (select * from date where holiday ='t')
select sum(pricepaid)
from sales join holidays on sales.dateid=holidays.dateid
where caldate='2008-12-25') as dec25sales
from sales join date on sales.dateid=date.dateid
where caldate in('2008-12-25','2008-12-31')
group by caldate
order by caldate;

caldate   | daysales | dec25sales
-----------+----------+------------
2008-12-25 | 70402.00 |   70402.00
2008-12-31 | 12678.00 |   70402.00
(2 rows)

第二个查询失败，因为它尝试在主查询和SELECT列表子查询中引用该表。HOLIDAYS主查询引用超出范围。

select caldate, sum(pricepaid) as daysales,
(with holidays as (select * from date where holiday ='t')
select sum(pricepaid)
from sales join holidays on sales.dateid=holidays.dateid
where caldate='2008-12-25') as dec25sales
from sales join holidays on sales.dateid=holidays.dateid
where caldate in('2008-12-25','2008-12-31')
group by caldate
order by caldate;

ERROR:  relation "holidays" does not exist