嵌套计算列“无效的列名"错误(T-SQL 列别名)

2021-10-26 00:00:00 alias calculated-columns tsql sql-server

我创建了 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]"别名计算列，则不会发生错误，但我需要总价.我该如何解决这个问题?似乎在达到 Final_TotalPrice 时还没有创建其他别名.

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，其他数据库(例如 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.)

我喜欢这个解决方案，因为你可以创建任意嵌套的表达式而不必担心缩进:

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 ;

注意事项:

  • 我发现 [ 和 ] 根本无法帮助我阅读或编写查询.
  • 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.

相关文章