oracle SQL - 从 Select + 用户定义的列和值创建表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27380438/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL - Create table from Select + user defined columns and values
提问by tomaytotomato
Currently I have the following SELECT statement:
目前我有以下 SELECT 语句：
CREATE TABLE TEST AS
SELECT ROW_ID,
PROM_INTEG_ID,
INTEGRATION_ID,
BILL_ACCNT_ID,
SERV_ACCT_ID,
CFG_STATE_CD
FROM PRODUCTS
WHERE PROD_ID = 'TestProduct'
AND STATUS_CD = 'Active';
However I have to add some additional columns which do not exist in the PRODUCTS table and define them with my own name .e.g HIERARCHY
但是，我必须添加一些在 PRODUCTS 表中不存在的附加列，并用我自己的名称定义它们。例如 HIERARCHY
I tried using the WITH operand in my SQL query but it keeps failing as the syntax is wrong.
我尝试在我的 SQL 查询中使用 WITH 操作数，但由于语法错误，它一直失败。
CREATE TABLE TEST AS
SELECT ROW_ID,
PROM_INTEG_ID,
INTEGRATION_ID,
BILL_ACCNT_ID,
SERV_ACCT_ID,
CFG_STATE_CD
WITH
PRODUCT_HEIRARCHY varchar2(30) 'Test123Value'
FROM PRODUCT
WHERE PROD_ID = 'TestProduct'
AND STATUS_CD = 'Active';
So in summary, I want to pull in columns from an existing table as well as defining some of my own.
总而言之，我想从现有表中提取列并定义我自己的一些列。
Any help appreciated
任何帮助表示赞赏
回答by Gordon Linoff
Just add the columns to the select:
只需将列添加到选择中：
CREATE TABLE TEST AS
SELECT ROW_ID, PROM_INTEG_ID, INTEGRATION_ID, BILL_ACCNT_ID, SERV_ACCT_ID, CFG_STATE_CD,
CAST('Test123Value' AS VARCHAR2(30)) as PRODUCT_HIERARCHY
FROM PRODUCTS
WHERE PROD_ID = 'TestProduct' AND STATUS_CD = 'Active';
Note that the cast()is not necessary. But it is a good idea if you want the column to have a specific type.
请注意，cast()不是必需的。但如果您希望该列具有特定类型，这是一个好主意。
回答by Lalit Kumar B
Also using CTEi.e. WITH clauseas known commonly, you could create table.
同样使用众所周知的CTEie WITH clause，您可以create table.
CREATE TABLE t
AS
WITH data AS (
SELECT...
)
SELECT *
FROM data
