本文為英文版的機器翻譯版本,如內容有任何歧義或不一致之處,概以英文版為準。
WITH 子句
WITH 子句是查詢中SELECT清單前面的選用子句。WITH 子句定義一或多個 common_table_expressions。每個通用資料表表達式 (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 )
參數
使用須知
您可以在下列SQL陳述式中使用 WITH子句:
-
SELECT、WITH、UNION、INTERSECT、 UNION ALL或 EXCEPT。
如果包含FROM子句的查詢WITH子句未參考WITH子句定義的任何資料表,則會忽略WITH子句,查詢會正常執行。
子WITH句子查詢定義的資料表只能在子WITH句開始的SELECT查詢範圍內參考。例如,您可以在SELECT清單、子FROM句或WHERE子句的子查詢的 HAVING子句中參考這類資料表。您無法在子查詢中使用 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查詢會從VENUE資料表VENUECOPY中選取所有資料列。主要查詢會依序從 選取所有資料列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 子查詢的子查詢FROM子句中會參考子句 (HOLIDAYS) 定義的資料表SELECT:
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