显示订购总数量大于或等于 100 的已订购物料的物料代码、供应商名称和总数量

Question

显示订购商品的物料代码、供应商名称和总数量，其订购总量大于或等于 100。

表格是：

报价

(报价ID，名称，项目代码，报价价格，QDate，QStatus(

订单(订单ID，

报价ID，订购数量，订单Dtae(

select  Q.itemcode ,Q.sname, O.QtyOrdered as TOTALQUANTITY 
from Quotation Q
    inner join Orders O on Q.QuotationId = O.QuotationId
where QtyOrdered >= 100 

查询结果 ：

ITEMCODE  SNAME           TOTALQUANTITY
I1008     EBATs               100
I1009     VV Electronics      100
I1009     VV Electronics      150

预期结果 ：

TEMCODE  SNAME            TOTALQUANTITY
I1009    VV Electronics      250
I1008    EBATs               150

Answer 1

SELECT Q.ITEMCODE,Q.SNAME, SUM(O.QTYORDERED) TOTALQUANTITY 
FROM QUOTATION Q
    INNER JOIN ORDERS O ON Q.QUOTATIONID= O.QUOTATIONID
 GROUP BY Q.ITEMCODE, Q.SNAME HAVING SUM(O.QTYORDERED)>=100

Answer 2

您可以通过以下方式获得预期结果：

select  Q.itemcode ,Q.sname, SUM(O.QtyOrdered) as TOTALQUANTITY 
from Quotation Q
    inner join Orders O on (Q.QuotationId = O.QuotationId
and O.QtyOrdered >= 100) GROUP BY Q.itemcode, Q.sname