從使用者定義的函式中獲取執行總計

我有以下查詢。

Select x1.TotalAmount, [ReturnTaxAmount](x1.TotalAmount,@TaxRate) AS TaxAmount, 
      x2.DocumentType 
FROM table1 X1 
 INNER JOIN table2 x2 on t1.id = t2.t1id

現在我如何獲得自我申報的 TaxAmount 的總和？我想做這樣的事情

Select x1.TotalAmount, 
      SUM([ReturnTaxAmount](x1.TotalAmount,@TaxRate) AS TaxAmount) OVER (PARTITION BY x3.InvId
         ORDER BY x3.createddate
         ROWS BETWEEN UNBOUNDED PRECEDING
         AND 1 PRECEDING) AS TaxAmount, 
      x2.DocumentType 
FROM table1 X1 
 INNER JOIN table2 x2 on t1.id = t2.t1id

$$ ReturnTaxAmount $$是一個將返回計算的十進制值的函式。 這是我嘗試但失敗的方法，懷疑是因為表還沒有真正生成。

是否可以這樣做，或者我必須創建一個新的聲明/臨時表來儲存該表並再次選擇該表以獲得它的執行總數。

您的語法略有錯誤。它應該是：

SELECT 
   x1.TotalAmount, 
   SUM([ReturnTaxAmount](x1.TotalAmount,@TaxRate))    -- no AS TaxAmount here
       OVER (PARTITION BY x3.InvId
             ORDER BY x3.createddate
             ROWS BETWEEN UNBOUNDED PRECEDING
             AND 1 PRECEDING) AS TaxAmount,           -- it's ok here
   x2.DocumentType 
FROM table1 x1 
 INNER JOIN table2 x2 ON t1.id = t2.t1id 
--- the rest of the code, that joins x3, etc
 ;

別名應該只有一次，在整個列表達式之後，其中包括SUM(...) OVER (...)

您可以使用派生表或 CTE 作為您的想法（不需要臨時表）。這將是等效的：

SELECT 
   t.TotalAmount, 
   SUM(t.TaxAmount)                    -- here we use the alias, defined in 
       OVER (PARTITION BY t.InvId      -- the derived table, below
             ORDER BY t.createddate
             ROWS BETWEEN UNBOUNDED PRECEDING
             AND 1 PRECEDING) AS TaxAmount,  
   t.DocumentType 
FROM 
   ( SELECT 
         x1.TotalAmount, 
         [ReturnTaxAmount](x1.TotalAmount,@TaxRate) AS TaxAmount, 
         x2.DocumentType,
         x3.InvId,
         x3.createddate
     FROM table1 x1 
       INNER JOIN table2 x2 ON t1.id = t2.t1id
     --- the rest of the code, that joins x3, etc
   ) AS t ;

引用自：https://dba.stackexchange.com/questions/152465