問(wèn)題描述

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

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] 

控制臺(tái)返回此錯(cuò)誤消息:

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]"別名計(jì)算列，則不會(huì)發(fā)生錯(cuò)誤，但我需要總價(jià).我該如何解決這個(gè)問(wèn)題?似乎在達(dá)到 Final_TotalPrice 時(shí)還沒(méi)有創(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.

推薦答案

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

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.)

我喜歡這個(gè)解決方案，因?yàn)槟憧梢詣?chuàng)建任意嵌套的表達(dá)式而不必?fù)?dān)心縮進(jìn):

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 ;

注意事項(xiàng):

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

• 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.

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