在where子句中使用计算列。

Question

我正在尝试在 WHERE 子句中使用一个计算列。

我已经尝试了从 CROSS APPLY 到子查询选择等各种方法，但是没有给我我需要的结果。

到目前为止我的查询如下：

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

这个不起作用，因为“Sales”是无效的列

- JadedEric

---

你能否使用HAVING (SUM(p.UnitPrice) * SUM(od.QtyShipped)) > 100代替WHERE？（参见http://www.w3schools.com/sql/sql_having.asp） - Jonny

Answer 1

在谓词中使用派生列

您需要将内部查询包装在派生表或CTE中，以便能够在 WHERE 子句中使用派生列（此外，注意只使用 SUM（）一次，使用乘法的结果）：

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;

在 HAVING 子句中重复使用派生列

如 @Jonny 的评论所述，另一种方式不是将计算的列DRY起来，而是重复计算。在应用了 GROUP BY 之后，使用 HAVING 而不是 WHERE 。

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
HAVING SUM(p.UnitPrice * od.QtyShipped) > 100;

请注意，根据下面的评论，计算表达式应为SUM(p.UnitPrice * od.QtyShipped)而不是SUM(p.UnitPrice) * SUM(od.QtyShipped)。

- StuartLC

---

嗨，斯图尔特，我收到了以下信息：Msg 130，Level 15，State 1，Line 4 无法在包含聚合或子查询的表达式上执行聚合函数。 SQL 不是我的强项，所以不知道这个消息的含义 :$ - JadedEric

销售计算应该只是这样的: SUM(p.UnitPrice * od.QtyShipped)。 - StuartLC

我看到你做的了，谢谢。所以我也在学习中 :). 谢谢。 - JadedEric

如果在 having 子句中多次重复使用派生列与将其封装起来相比，会对性能产生什么影响？ - kamaci

Answer 2

您可以使用常用表达式来实现此功能。

;WITH CTE AS
    (
    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
    
    GROUP BY p.Code, c.AccountNumber, Sale
    
    )
    
SELECT *
FROM CTE WHERE CTE.Sales>100

- mindbdev

Answer 3

如果它是一个计算列，你可以使用 "HAVING"。

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
GROUP BY p.Code, c.AccountNumber, Sales
HAVING SALES > 100;

- Muran

---

很遗憾，不可以。你可以在“ORDER BY”中使用列别名，但不能在“HAVING”中使用——你需要在“HAVING”语句中重复完整的计算（如@Jonny的评论所示），或者对计算列进行包装。演示在这里。 - StuartLC