mysql - 具有多个表的复杂sql查询

Question

我有 3 个表:

客户

ID_CUSTOMER
NAME

产品

ID_PRODUCT
PRODUCTNAME
PRICE

订单

ID_ORDER
CUSTOMER_ID
PRODUCT_ID
QUANTITY

如何选择所有订购金额超过 1 万美元的客户？

Answer 1

尝试类似的东西:

SELECT C.ID_CUSTOMER, C.Name, 
      SUM(P.PRICE * O.QUANTITY) AS Total FROM Customers C 
      JOIN Orders O ON C.CUSTOMER_ID = O.CUSTOMER_ID
      JOIN Procucts P ON P.PRODUCT_ID = O.PRODUCT_ID
      GROUP BY C.ID_CUSTOMER, C.Name
      HAVING SUM(P.PRICE * O.QUANTITY) >= 10000