如何在SQL中使用“ With”子句创建表

user2023068 发表于 Dev

用户名

我正在尝试使用WITH子句创建一个持久表，但是却出现了错误。

对于上下文，我目前找到的答案是

CREATE TABLE my_table
AS 
WITH my_tables_data AS ( 
   SELECT another_table.data1 AS some_value 
   FROM another_table
) 
SELECT * 
FROM some_data;

但是，我收到一个错误

消息319，级别15，状态1，第5行关键字“ with”附近的语法错误。如果此语句是公用表表达式，xmlnamespaces子句或更改跟踪上下文子句，则前一条语句必须以分号终止。

我的代码是

CREATE TABLE SalesOrdersPerYear  
WITH t1 AS (
    -- Define the CTE expression name and column list.  
    WITH Sales_CTE (SalesPersonID, BaseSalary)  
    AS  
    -- Define the CTE query.  
    (  
        SELECT SALES_PERSON.SALES_PERSON_ID, SALES_PERSON.BASE_SALARY  
        FROM SALES_PERSON  
        WHERE SALES_PERSON_ID IS NOT NULL  
    )  
    -- Define the outer query referencing the CTE name.  
    SELECT SalesPersonID, BaseSalary AS TotalSales  
    FROM Sales_CTE   
    ORDER BY SalesPersonID, BaseSalary;  
)

有人可以为此提供一些指导吗？

提前谢谢了！

豆蛙

这对于SQL Server无效的语法。您可以使用CREATE TABLE并指定列名称和类型来创建表，也可以执行SELECT INTO包含数据的语句。

方法1：创建表，然后填充：

CREATE TABLE SalesOrdersPerYear 
( SalesPersonID int, BaseSalary float)
;

WITH Sales_CTE (SalesPersonID, BaseSalary)  
AS  
(  
    SELECT SALES_PERSON.SALES_PERSON_ID, SALES_PERSON.BASE_SALARY  
    FROM SALES_PERSON  
    WHERE SALES_PERSON_ID IS NOT NULL 
)  
insert into SalesOrdersPerYear  
SELECT SalesPersonID, BaseSalary AS TotalSales  
FROM Sales_CTE   
ORDER BY SalesPersonID, BaseSalary;

方法2-一站式

WITH Sales_CTE (SalesPersonID, BaseSalary)  
AS  
(  
    SELECT SALES_PERSON.SALES_PERSON_ID, SALES_PERSON.BASE_SALARY  
    FROM SALES_PERSON  
    WHERE SALES_PERSON_ID IS NOT NULL 
)  
select  SalesPersonID, BaseSalary AS TotalSales  
into SalesOrdersPerYear
FROM Sales_CTE   
ORDER BY SalesPersonID, BaseSalary;

当您需要指定有关表的更多信息（主键，索引，foregin键等）时，请使用方法1。

对于更临时的事物，请使用方法2。（您通常会在此处使用#SalesOrdersPerYear这样的临时表）。

无论哪种方式，数据现在都存储在表中，您可以再次使用它。

使用临时表：

-- Check for existence and drop first to avoid errors if it already exists.
if OBJECT_ID('tempdb..#SalesOrdersPerYear') is not null
    drop table #SalesOrdersPerYear

WITH Sales_CTE (SalesPersonID, BaseSalary)  
AS  
(  
    SELECT SALES_PERSON.SALES_PERSON_ID, SALES_PERSON.BASE_SALARY  
    FROM SALES_PERSON  
    WHERE SALES_PERSON_ID IS NOT NULL 
)  
select  SalesPersonID, BaseSalary AS TotalSales  
into #SalesOrdersPerYear
FROM Sales_CTE   
ORDER BY SalesPersonID, BaseSalary;

您还可以将其定义为表变量，这在方法之间有点交叉：

declare @SalesOrdersPerYear table
( SalesPersonID int, BaseSalary float)
;

WITH Sales_CTE (SalesPersonID, BaseSalary)  
AS  
(  
    SELECT SALES_PERSON.SALES_PERSON_ID, SALES_PERSON.BASE_SALARY  
    FROM SALES_PERSON  
    WHERE SALES_PERSON_ID IS NOT NULL 
)  
insert into @SalesOrdersPerYear  
SELECT SalesPersonID, BaseSalary AS TotalSales  
FROM Sales_CTE   
ORDER BY SalesPersonID, BaseSalary;

此选项将仅在该批处理中保留，并且不需要删除-就像其他任何变量一样。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。