SQL Select where value = to another select

我有兩張桌子。訂單和 OrderParts。一個訂單可以有多個 OrderPart。

我想要訂單表中所有order.customerID數量的OrderParts ordered = Total_number_parts列表。

下面是我到目前為止所到的地方，但我該把where sum(partqty)=total_number_parts位放在哪裡！！

Select a.customerID, a.Total_number_parts
From Orders a
Join (Select sum(PartQty) from OrderParts Group by customerID) B
  on B.customerID = a.customerID
Group By a.CustomerID

您可以使用CTE進行這項工作：

WITH ctSum AS
(
   SELECT   customerId, SUM(PartQty) as TotalQty
   FROM     OrdersParts
   GROUP BY customerID
)
SELECT a.customerID, a.Total_number_parts
FROM   Orders a
JOIN   ctSum b
ON     b.customerID = a.customerID
WHERE  a.Total_number_parts = b.TotalQty;

或者，如果您更喜歡另一種語法：

SELECT a.customerID, a.Total_number_parts
FROM   Orders a
JOIN   (SELECT   customerId, SUM(PartQty) as TotalQty
       FROM     OrdersParts
       GROUP BY customerID) b
ON     b.customerID = a.customerID
WHERE  a.Total_number_parts = b.TotalQty;

您可以使用HAVING子句並擺脫派生表：

SELECT a.customerID, SUM(b.PartQty)
FROM   Orders a
JOIN   OrdersParts b
   ON b.customerID = a.customerID
GROUP BY a.customerID
HAVING a.Total_number_parts = SUM(b.PartQty);

HAVING``GROUP BY在與 相反之後進行評估WHERE，這就是您可以在HAVING子句中使用聚合函式的原因。

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