sql - 在不分组或排序的情况下对 SQL 求和

Question

我有一个像这样的 SQL Server 查询:

CREATE TABLE ##Temp(
    OrderID NVARCHAR(100), 
    ID INT,
    Prices INT,
    Total INT
);

INSERT INTO ##Temp (ID, Prices, OrderID, Total)
SELECT  fc.ID, f.Prices, f.OrderID, 
        (SUM(f.Prices) OVER()) AS Total 
FROM FruitCrates fc
LEFT JOIN Fruits f ON fc.ID = f.FruitCrateID
WHERE  fc.OrderID LIKE '18_1635' 
  AND fc.Rights = 1 
  AND fc.Cancelled = 0 
  AND f.OrderID IS NOT NULL;

SELECT * FROM ##Temp;

但我一直收到错误:

Msg 207, Level 16, State 1, Line 12
Invalid column name 'Total'.

我认为这是因为我没有正确使用 OVER() 但是我不确定如何修复它。

请注意

SELECT 语句在未放在 INSERT 之后时继续运行。

没有插入的结果

╔═════╦════════╦══════════╦═══════╗
║ ID  ║ Prices ║ OrderID  ║ Total ║
╠═════╬════════╬══════════╬═══════╣
║  77 ║      1 ║ 1_370    ║   104 ║
║  19 ║    101 ║ 1811_171 ║   104 ║
║  77 ║      2 ║ 1811_171 ║   104 ║
╚═════╩════════╩══════════╩═══════╝   

Answer 1

错误消息插入 无效的列名称“总计”。 清楚地表明 ##Temp 表与 INSERT INTO (.. .) 语句。

要排除故障，您应该始终在插入之前检查元数据(列名):

SELECT *
FROM ##Temp
WHERE 1=2;

然后您可以很容易地发现它与您假设的不同。

现在您正在使用全局临时表，只要引用它的最后一个连接处于事件状态，它就会一直存在。您应该考虑使用本地临时表。

要解决您的问题，您可以:

1. 使用不同的名字避免冲突

2. 就在您可以之前(如果未在嵌套过程中使用):

   IF OBJECT_ID('tempdb..##temp') IS NOT NULL     DROP TABLE ##tempCREATE TABLE ##temp...

There is also one nasty case when stored procedure called another stored procedure and both (outer and inner) creates temp table with the same name. You should avoid it.

CREATE PROCEDURE #outer
AS
BEGIN
   CREATE TABLE ##temp (id INT, guid UNIQUEIDENTIFIER, col2 VARCHAR(100));
   INSERT INTO ##temp VALUES(1,NEWID(), 'a');
   SELECT * FROM ##temp;
   EXEC [#inner];
END
GO

CREATE PROCEDURE #inner
AS
BEGIN
    CREATE TABLE ##temp (id INT, total INT);  -- no error during creation

    INSERT INTO ##temp(id, total)
    SELECT 2, 10;

    SELECT * FROM ##temp;
END
GO

EXEC #outer
-- Invalid column name 'total'.

LiveDemo

最有趣的是，当您使用具有相同列数(或默认值)的本地临时表并且可以进行隐式转换时，它将通过:

CREATE PROCEDURE #outer
AS
BEGIN
CREATE TABLE #temp (id INT, col2 varchar(10));
   INSERT INTO #temp VALUES(1, 'a');
   SELECT * FROM #temp;
   EXEC [#inner];
END
GO

CREATE PROCEDURE #inner
AS
BEGIN
    CREATE TABLE #temp (id INT, total INT);
    INSERT INTO #temp
    SELECT 2, 10;

    SELECT * FROM #temp;
END
GO

EXEC #outer

LiveDemo2

以及不兼容类型的示例:

CREATE PROCEDURE #outer
AS
BEGIN
CREATE TABLE #temp (id INT, col2 UNIQUEIDENTIFIER);
INSERT INTO #temp VALUES(1, NEWID());
   SELECT * FROM #temp;
   EXEC [#inner];
END
GO

CREATE PROCEDURE #inner
AS
BEGIN
    CREATE TABLE #temp (id INT, total INT);
    INSERT INTO #temp
    SELECT 2, 10;

    SELECT * FROM #temp;
END
GO

EXEC #outer

Operand type clash: int is incompatible with uniqueidentifier