問題描述

我創(chuàng)建了 3 個計算列作為別名，然后使用別名列來計算總成本.這是查詢:

I've create 3 computed columns as alias and then used the aliased columns to calculate the total cost. This is the query:

SELECT TOP 1000 [Id]
      ,[QuantityOfProduct]
      ,[Redundant_ProductName]
      ,[Order_Id]
      ,(CASE 
            WHEN [PriceForUnitOverride] is NULL 
                THEN [Redundant_PriceForUnit]
            ELSE
                [PriceForUnitOverride]
        END
        ) AS [FinalPriceForUnit]

      ,(CASE 
            WHEN [QuantityUnit_Override] is NULL 
                THEN [Redundant_QuantityUnit]
            ELSE
                [QuantityUnit_Override]
        END
        ) AS [FinalQuantityUnit]

      ,(CASE 
            WHEN [QuantityAtomic_Override] is NULL 
                THEN [Redundant_QuantityAtomic]
            ELSE
                [QuantityAtomic_Override]
        END
        ) AS [Final_QuantityAtomic]

         --***THIS IS WHERE THE QUERY CREATES AN ERROR***--
        ,([QuantityOfProduct]*[FinalPriceForUnit]*
  ([Final_QuantityAtomic]/[FinalQuantityUnit])) AS [Final_TotalPrice]


  FROM [dbo].[ItemInOrder]

  WHERE [IsSoftDeleted] = 0
  ORDER BY [Order_Id] 

控制臺返回此錯誤消息:

The console returns this ERROR message:

Msg 207, Level 16, State 1, Line 55
Invalid column name 'FinalPriceForUnit'.
Msg 207, Level 16, State 1, Line 55
Invalid column name 'Final_QuantityAtomic'.
Msg 207, Level 16, State 1, Line 55
Invalid column name 'FinalQuantityUnit'.

如果我刪除AS [Final_TotalPrice]"別名計算列，則不會發(fā)生錯誤，但我需要總價.我該如何解決這個問題?似乎在達到 Final_TotalPrice 時還沒有創(chuàng)建其他別名.

If I remove the "AS [Final_TotalPrice]" alias computed column, no error occurs, but I need the total price. How can I solve this issue? It seems as the other aliases have not been created when the Final_TotalPrice is reached.

推薦答案

不能在同一個選擇中使用表別名.正常的解決方案是 CTE 或子查詢.但是，SQL Server 也提供 APPLY.(Oracle 還支持 APPLY，其他數(shù)據(jù)庫(例如 Postgres)支持使用 LATERAL 關鍵字進行橫向連接.)

You can't use table aliases in the same select. The normal solution is CTEs or subqueries. But, SQL Server also offers APPLY. (Oracle also supports APPLY and other databases such as Postgres support lateral joins using the LATERAL keyword.)

我喜歡這個解決方案，因為你可以創(chuàng)建任意嵌套的表達式而不必擔心縮進:

I like this solution, because you can create arbitrarily nested expressions and don't have to worry about indenting:

SELECT TOP 1000 io.Id, io.QuantityOfProduct, io.Redundant_ProductName,
       io.Order_Id,
       x.FinalPriceForUnit, x.FinalQuantityUnit, x.Final_QuantityAtomic,
       (x.QuantityOfProduct * x.FinalPriceForUnit * x.Final_QuantityAtomic / x.FinalQuantityUnit
       ) as Final_TotalPrice
FROM dbo.ItemInOrder io OUTER APPLY
     (SELECT COALESCE(PriceForUnitOverride, Redundant_PriceForUnit) as FinalPriceForUnit,
             COALESCE(QuantityUnit_Override, Redundant_QuantityUnit) as FinalQuantityUnit
             COALESCE(QuantityAtomic_Override, Redundant_QuantityAtomic) as Final_QuantityAtomic
     ) x
WHERE io.IsSoftDeleted = 0
ORDER BY io.Order_Id ;

注意事項:

• 我發(fā)現(xiàn) [ 和 ] 根本無法幫助我閱讀或編寫查詢.
• COALESCE() 比您的 CASE 語句簡單得多.
• 使用 COALESCE()，您可能會考慮只將 COALESCE() 表達式放入最終計算中.

• I don't find that [ and ] help me read or write queries at all.
• COALESCE() is much simpler than your CASE statements.
• With COALESCE() you might consider just putting the COALESCE() expression in the final calculation.

這篇關于嵌套計算列“無效的列名"錯誤(T-SQL 列別名)的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！