在where子句中使用计算列 [英] Use a calculated column in a where clause

问题描述

我正在尝试在where子句中使用计算列。

I'm trying to use a calculated column in a where clause.

我已经尝试了从CROSS APPLY到子查询select的所有方法，但它没有

I've trying everything from CROSS APPLY, to sub-query select but it does not give me the anything near what I need.

到目前为止我的查询：

SELECT p.Code, c.AccountNumber, Sales = (SUM(p.UnitPrice) * SUM(od.QtyShipped)) FROM [dbo].Customer c 
    LEFT JOIN [dbo].OrderHeader oh ON oh.CustomerId = c.Id 
    LEFT JOIN [dbo].OrderDetail od ON od.OrderHeaderId = oh.Id
    LEFT JOIN [dbo].Product p ON p.Id = od.ProductId
WHERE Sales > 100
GROUP BY p.Code, c.AccountNumber, Sales

这不起作用，因为销售是无效列

This does not work, as 'Sales' is an invalid column

推荐答案

您需要按顺序将内部查询包装在派生表或CTE中以便能够使用 WHERE 子句中的派生列（另外，请注意，仅在一次之后指定 SUM（）

You'll need to wrap the inner query in a derived table or CTE in order to be able to use derived columns in the WHERE clause (Also, note SUM() is specified just once, after the multiplication):

SELECT x.Code, x.AccountNumber, x.Sales
FROM
(
  SELECT p.Code, c.AccountNumber, SUM(p.UnitPrice *od.QtyShipped) AS Sales 
  FROM [dbo].Customer c 
      LEFT JOIN [dbo].OrderHeader oh ON oh.CustomerId = c.Id 
      LEFT JOIN [dbo].OrderDetail od ON od.OrderHeaderId = oh.Id
      LEFT JOIN [dbo].Product p ON p.Id = od.ProductId
  GROUP BY p.Code, c.AccountNumber
) AS x
WHERE x.Sales > 100;

这篇关于在where子句中使用计算列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！