Amazon Redshift
数据库开发人员指南 (API Version 2012-12-01)
AWS 服务或AWS文档中描述的功能,可能因地区/位置而异。点 击 Getting Started with Amazon AWS to see specific differences applicable to the China (Beijing) Region.

WITH 子句

WITH 子句是一个可选子句,该子句在查询中位于 SELECT 列表之前。WITH 子句定义一个或多个子查询。每个子查询均定义一个临时表,与视图定义类似。可在 FROM 子句中引用这些临时表,并且这些临时表仅在执行其所属的查询期间使用。WITH 子句中的每个子查询均指定一个表名、一个可选的列名列表以及一个计算结果为表的查询表达式(SELECT 语句)。

WITH 子句子查询是定义可在单个查询的执行过程中使用的表的有效方式。在所有情况下,在 SELECT 语句的主体中使用子查询可获得相同的结果,不过 WITH 子句子查询可能在编写和阅读方面更加简单。如果可能,会将已引用多次的 WITH 子句子查询优化为常用子表达式;即,可以计算 WITH 子查询一次并重用其结果。(请注意,常用子表达式不只是限于 WITH 子句中定义的子表达式。)

语法

Copy
[ WITH with_subquery [, ...] ]

其中,with_subquery 为:

Copy
with_subquery_table_name [ ( column_name [, ...] ) ] AS ( query )

参数

with_subquery_table_name

临时表的唯一名称,该临时表用于定义 WITH 子句子查询的结果。不能在单个 WITH 子句中使用重复名称。必须为每个子查询提供一个可在 FROM 子句中引用的表名。

column_name

WITH 子句子查询的输出列名的可选列表(用逗号分隔)。指定的列名数目必须等于或小于子查询定义的列数。

query

Amazon Redshift 支持的任何 SELECT 查询。请参阅SELECT

使用说明

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

  • SELECT(在 SELECT 语句中包含子查询)

  • SELECT INTO

  • CREATE TABLE AS

  • CREATE VIEW

  • DECLARE

  • EXPLAIN

  • INSERT INTO...SELECT

  • PREPARE

  • UPDATE(在 WHERE 子句子查询中)

如果包含 WITH 子句的查询的 FROM 子句未引用 WITH 子句所定义的任何表,则将忽略 WITH 子句,并且查询将正常执行。

WITH 子句子查询所定义的表只能在 WITH 子句开始的 SELECT 查询范围内引用。例如,可以在 SELECT 列表的子查询的 FROM 子句、WHERE 子句或 HAVING 子句中引用这样的表。不能在子查询中使用 WITH 子句,也不能在主查询或其他子查询的 FROM 子句中引用其表。此查询模式会为 WITH 子句表生成 relation table_name does not exist 形式的错误消息。

不能在 WITH 子句子查询中指定另一个 WITH 子句。

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

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

WITH 子句子查询不能包含 SELECT INTO 语句;不过,您可以在 SELECT INTO 语句中使用 WITH 子句。

示例

以下示例说明了包含 WITH 语句的查询的最简单示例。在名为 VENUECOPY 的 WITH 查询中,选择 VENUE 表中的所有行。主查询又选择 VENUECOPY 中的所有行。VENUECOPY 表仅在此查询的持续时间内存在。

Copy
with venuecopy as (select * from venue) select * from venuecopy order by 1 limit 10;
Copy
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 表的子查询。

Copy
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;
Copy
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 子句子查询。SELECT 列表中的子查询的 FROM 子句中将引用 WITH 子句定义的表 (HOLIDAYS):

Copy
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 表。主查询引用超出范围。

Copy
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

本页内容: