我可以通过使用with
子句形成的查询来创建表格吗?
CREATE TABLE t
AS
WITH some_data AS (
SELECT 1 as some_value
FROM dual
UNION ALL
SELECT 2
FROM dual
)
SELECT *
FROM some_data
CREATE TABLE table_name AS语句根据SELECT语句创建一个表。使用WITH子句的解决方案如下:
CREATE TABLE t
AS
SELECT * FROM (
WITH some_data AS (
SELECT 1 as some_value
FROM dual
UNION ALL
SELECT 2
FROM dual
)
);
对于多个CTE(通用表达式;即多个WITH
子句),我发现相同的语法适用。例如:
CREATE TABLE schema.table_name as
WITH table1 as (SELECT 1),
table2 as (SELECT 2)
select * from table2
将从select
语句创建schema
中的table_name
CREATE TABLE t AS ( WITH some_data AS (...) SELECT * FROM some_data )
, 应该写成CREATE TABLE t AS WITH some_date AS (...) SELECT * FROM some_data
。 - Sander Vanden Hauttecreate as (select * from ( with as ... ))
使其正常工作。就像这里其他答案中所示。从未想过括号必须或不必在语句周围使用。 - user10325516