在 where 語句中使用查詢的值

我正在尋找如何做到這一點，但找不到。

這是我的查詢：

SELECT
   S_Product.Name AS "Name"
   SUM(S_Item.Quantity) AS "QtySold"
FROM s_product
INNER JOIN S_Item ON S_Product.ID = S_Item.Product_ID
WHERE "QtySold" > 500 
--(SUM(S_Item.Quantity)) cannot be used in a WHERE statement since it's a group function
GROUP BY S_Product.Name

雖然這不起作用我想你明白我的想法，我怎麼能在 WHERE 語句中使用“QtySold”值？

答案是將任何聚合測試放入一個HAVING子句中：

SELECT
   S_Product.Name AS "Name"
   SUM(S_Item.Quantity) AS "QtySold"
FROM s_product
INNER JOIN S_Item ON S_Product.ID = S_Item.Product_ID
GROUP BY S_Product.Name
HAVING SUM(S_Item.Quantity) > 500 ;

根據您的 SQL 伺服器可以執行的操作，還有其他方法可以在另一個查詢的條件下使用查詢結果，例如。

SELECT * FROM Orders WHERE CustomerID IN
   ( SELECT CustomerID FROM Customers WHERE ExpressDelivery = 1 )
;
SELECT SomeSubQuery.column1
FROM
   (
   SELECT SUM(Quantity) AS column1, Name AS Name
   FROM s_product GROUP BY Name
   ) AS SomeSubQuery  /* needs a name this time */
;
WITH SomeQuery (Quantities, Name) AS
( SELECT SUM(Quantity) AS Quantities, Name 
 FROM s_product GROUP BY Name )
SELECT Quantities, Name FROM SomeQuery WHERE Quantities > 500
;

尤其是最後兩個，可能是 Microsoft SQL Server 特有的？（更新）或者可以正常工作。

我寫了一首關於我的第一個例子的詩：

Expedite supply
When prime customers desire
Seasonal produce.

我想要一個可以執行它的伺服器。 （但是，它想要我嗎？）

引用自：https://dba.stackexchange.com/questions/176328