用公式除以各列的總和，并將結果格式化為無效。

我有下面的SQL代碼，它回傳一個錯誤資訊，說明AvgPYs列是無效的。這也影響了我的Auth列。我的公式有問題嗎？希望得到任何幫助。

SELECT [tblCeiling]. [Proj Code], [tblCeiling]. [Act Code], [tblCeiling]. [Cost Ctr], [tblCeiling]. [Date], [tblCeiling].[Ref2], [tblCeiling].[Analyst], [tblCeiling].[Type], [tblCeiling].[B or O], [tblCeiling].[July], [tblCeiling].[Aug] 。
                  [tblCeiling].[9月], [tblCeiling].[10月], [tblCeiling].[11月], [tblCeiling].[Dec], [tblCeiling].[1月], [tblCeiling]. [2], [tblCeiling]. [3], [tblCeiling]. [4], [tblCeiling]. [5], [tblCeiling]. [6], [tblCeiling]. [Perm], [tblCeiling]. [Temp], [tblCeiling]. [LimitedTerm] 。
                  [tblCeiling].[LTDate], [tblCeiling].[Sal_Rate], [tblCeiling] 。 [New], [Perm]   [Temp]   [LimitedTerm] AS Monthly, Format([tblCeiling]. [July]   [tblCeiling]. [8月]   [tblCeiling]. [9月]   [tblCeiling]. [10月]   [tblCeiling] 。 [11月]   [tblCeiling]. [Dec]   [tblCeiling]. [1月]  
                  [tblCeiling]. [Feb]   [tblCeiling]. [Mar]   [tblCeiling]. [四月]   [tblCeiling].[五月]   [tblCeiling].[六月]) / 12, '0.0####'/span>) AS AvgPYs, 
                  ((([Sal_Rate] * [AvgPYs]) * 1000) / 1000) AS Auth, [Dollar Adj]   [Auth] AS Budget, [tblCeiling]. [進口], [tblCeiling].[Dollar Adj], [tblCeiling].[OngoingOrOneTime], [tblCeiling]. [OneTimeEndingDate]
FROM (SELECT DISTINCT *)
                  FROM [tblactcode]) AS [tblactcode] RIGHT JOIN
                      (SELECT DISTINCT *)
                       FROM [tblCeiling]) AS [tblCeiling] ON [tblactcode].[Act Code] = [tblCeiling].[Act Code]
WHERE ((([tblCeiling]. [Jul]) = iif([jul] IS Null, 0, [jul]) AND (([tblCeiling].[Aug]) = iif([aug] IS NULL, 0, [aug]) AND (([tblCeiling].[Sep]) = iif([sep] IS NULL, 0, [sep]) AND (([tblCeiling]. [Oct]) = iif([oct] IS NULL, 0, [oct]) AND (([tblCeiling]. [Nov]) = iif([nov] IS NULL,
                   0, [nov])) AND (([tblCeiling]. [Dec]) = iif([dec] IS NULL, 0, [dec]) AND (([tblCeiling].[Jan]) = iif([jan] IS NULL, 0, [jan]) AND (([tblCeiling]. [Feb]) = iif([feb] IS NULL, 0, [feb]) AND (([tblCeiling]. [Mar]) = iif([mar] IS NULL, 0, [mar]) AND (([tblCeiling]. [Apr]) 
                  = iif([apr] IS NULL, 0, [apr]) AND (([tblCeiling].[May]) = iif([May] IS NULL, 0, [May]) AND (([tblCeiling].[Jun]) = iif([jun] IS NULL, 0, [jun]) AND (([tblCeiling].[Import]) = 0)
ORDER BY [tblCeiling].[Proj Code], [tblCeiling].[Cost Ctr], [tblCeiling].[Date]

以下是我所指的具體行：

Format(([tblCeiling]. [July]   [tblCeiling]. [8月]   [tblCeiling]. [9月]   [tblCeiling]. [10月]   [tblCeiling]. [11月]   [tblCeiling]. [Dec]   [tblCeiling].[Jan]  。
                  [tblCeiling]. [Feb]   [tblCeiling]. [Mar]   [tblCeiling]. [四月]   [tblCeiling].[五月]   [tblCeiling].[六月]) / 12, '0.0####'/span>) AS AvgPYs,

uj5u.com熱心網友回復：

你遇到的具體問題是試圖在你計算它的同一范圍內使用一個計算值--這是不可能的。你只能在外部查詢中訪問一個計算值。

或者一個巧妙的解決方案是使用CROSS APPLY，它允許你重復使用一個計算，如下所示。一般來說，這樣做的方式是：

select --現有列在AvgPYs之前。
  ，AvgPYs
  -- , 一些取決于AvgPYs的公式。
from (
    -- 現有查詢
) C -- C是Ceiling的一個可接受的簡短別名。
cross 應用 (
    values (公式)
) X (AvgPYs)

在你的案例中，我認為以下做法是正確的：

SELECT C. [Proj Code], C. [Act Code], C. [Cost Ctr], C. [Date], C. [Ref2], C. [Analyst], C. [Type], C. [B or O], C. [July], C. [Aug] 
    , C. [Sep], C. [Oct], C. [Nov], C. [Dec], C. [Jan], C. [Feb], C. [Mar], C. [Apr], C. [May], C. [Jun], C. [Perm], C. [Temp], C. [LimitedTerm]
    , C. [LTDate], C. [Sal_Rate], C. [New], [Perm]   [Temp]   [LimitedTerm] AS Monthly
    , X.AvgPYs 
    , Y.Auth
    , [Dollar Adj]   Y.Auth AS Budget, C. [Import], C. [Dollar Adj], C. [OngoingOrOneTime], C. [OneTimeEndingDate]
FROM (
    SELECT DISTINCT *
    FROM [tblactcode]。
) AS AC
RIGHT JOIN (
    SELECT DISTINCT *
    FROM [tblCeiling].
) AS C ON AC. [Act Code] = C. [Act Code]
CROSS APPLY (
    VALUES (Format((C. [July]   C. [Aug]   C. [Sep]   C. [10月]   C. [11月]   C. [Dec]   C. [1月]  
    C. [2]   C. [3]   C. [4]   C. [5]   C. [6] ) / 12, '0.0####'/span>)
) AS X (AvgPYs)
CROSS APPLY (
    VALUES (((([Sal_Rate] * X.AvgPYs) * 1000) / 1000) )
) Y (Auth)
WHERE (((C. [Jul]) = iif([jul] IS NULL, 0, [jul]) AND ((C. [Aug]) = iif([aug] IS NULL, 0, [aug]) AND ((C. [Sep]) = iif([sep] IS NULL, 0, [sep]) AND ((C. [Oct]) = iif([oct] IS NULL, 0, [oct]) AND ((C. [Nov]) = iif([nov] IS NULL,
    0, [nov])) AND ((C. [Dec]) = iif([dec] IS Null, 0, [dec]) AND ((C. [Jan]) = iif([jan] IS NULL, 0, [jan]) AND ((C. [Feb]) = iif([feb] IS NULL, 0, [feb]) AND ((C. [Mar]) = iif([mar] IS NULL, 0, [mar]) AND ((C. [Apr]) 
    = iif([apr] IS NULL, 0, [apr]) AND ((C. [May]) = iif([May] IS NULL, 0, [May]) AND ((C. [Jun]) = iif([jun] IS NULL, 0, [jun]) AND ((C. [Import]) = 0)
)
ORDER BY C.[Proj Code], C.[Cost Ctr], C.[Date] 。

注意：表別名的一個關鍵目的是對表有一個簡短的參考。請看，使用較短的別名會使閱讀變得更容易。

標籤：

上一篇：Codeigniter4影像水印

下一篇：如何對兩個值中的每一個按日期計算行數？