sql-server - 如何在sql server中创建一个带有结果集的表

Question

我想创建一个表来存储另一个查询的结果集。

我试过这个:

create table avgcost_product (
    name varchar(50),
    productnumber varchar(50),
    averagecost money
)
    

创建表后我尝试了这个查询:

INSERT into avgcost_product SELECT * FROM
(SELECT p.[Name], p.ProductNumber,
        CONVERT(varchar, cost.AvgCost,1) as 'Average Cost'
FROM Production.Product p
CROSS APPLY fnGetAvgCost(p.ProductID) as cost
WHERE cost.AvgCost IS NOT NULL
ORDER BY cost.AvgCost desc)

但它显示如下错误:

Msg 1033, Level 15, State 1, Line 284

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

如何在我的表中写入我需要的数据？

Answer 1

只需从内部选择中删除 ORDER BY 或者您甚至可以进一步简化您的查询:

INSERT into avgcost_product 
SELECT p.[Name]
       , p.ProductNumber
       , CONVERT(varchar, cost.AvgCost,1) as 'Average Cost'
FROM Production.Product p
CROSS APPLY fnGetAvgCost(p.ProductID) as cost
WHERE cost.AvgCost IS NOT NULL