postgresql - Redshift - 基于嵌套公用表表达式创建表

Question

根据从“嵌套”公用表表达式返回的结果创建新表的语法是什么（下面的示例）？

示例：

   WITH allCustomers as (SELECT Customerid FROM Customer_tbl),
    customer_purchasing as (SELECT Customerid, 
                                   Item, 
                                   Price 
                            FROM allCustomers 
                            JOIN purchases_tbl 
                              ON allCustomers.Customerid = purchases_tbl.Customerid
                               ) 
SELECT * FROM customer_purchasing 

问题：如何根据customer_purchasing 示例中的 CTE创建新表

标签： postgresqlamazon-redshiftcreate-table

Answer 1

Found the answer while writing my question: A table can be created based on nested CTE's by adding the following syntax to the example query.

CREATE TEMPORARY TABLE testtable1 as (
   WITH allCustomers as (SELECT Customerid FROM Customer_tbl),
    customer_purchasing as (SELECT Customerid, 
                                   Item, 
                                   Price 
                            FROM allCustomers 
                            JOIN purchases_tbl 
                              ON allCustomers.Customerid = purchases_tbl.Customerid)
SELECT * FROM customer_purchasing)