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

这不是 sql 服务器的有效语法。可以使用 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;

此选项将仅保留此批处理，不需要丢弃 - 就像任何其他变量一样。