我正在获取在整个购买历史中花费超过X金额的所有客户(ID, Email, Name)的查询

Question

这是我拥有的3个表，我正在获取在整个购买历史中花费超过X金额的所有客户(ID, Email, Name)的查询。

CREATE TABLE Sales (
Sales ID int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
Customer ID int FOREIGN KEY REFERENCES Customer(CustomerID),
Product ID int FOREIGN KEY REFERENCES Product(SKU ID),
Quantity int,
Date Datetime
);
CREATE TABLE Product (
SKU ID int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
Product Name vchar(255),
Price int,
Discount Price double,
In Stock int
);
CREATE TABLE Customer (
CustomerID int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
EmailAddress vchar(255),
FirstName vchar(255),
LastName vchar(255),
Gender vchar(255)
);

这是我写的查询，但我仍然不能得到我需要的结果。

SELECT c.customerid, c.emailaddress, c.firstname
FROM customer c
INNER JOIN sales s ON s.customerid = c.customerid
INNER JOIN product p ON s.productid = p.productid
HAVING SUM(s.quantity * p.price) > Xamount;

Answer 1

您缺少一个GROUP BY条款

SELECT c.customerid, c.emailaddress, c.firstname
FROM customer c
INNER JOIN sales s ON s.customerid = c.customerid
INNER JOIN product p ON s.productid = p.productid
GROUP BY c.customerid, c.emailaddress, c.firstname
HAVING SUM(s.quantity * p.price) > @amount;

也可以使用半连接，使用EXISTS或相关子查询

SELECT c.customerid, c.emailaddress, c.firstname
FROM customer c
WHERE EXISTS (
SELECT 1
FROM sales s
INNER JOIN product p ON s.productid = p.productid
WHERE s.customerid = c.customerid
HAVING SUM(s.quantity * p.price) > @amount
);
-- alternatively
WHERE (
SELECT SUM(s.quantity * p.price)
FROM sales s
INNER JOIN product p ON s.productid = p.productid
WHERE s.customerid = c.customerid
) > @amount;

在支持它的dbms中，您也可以使用APPLY或LATERAL JOIN

SELECT c.customerid, c.emailaddress, c.firstname
FROM customer c
CROSS APPLY (
SELECT SUM(s.quantity * p.price) AS total
FROM sales s
INNER JOIN product p ON s.productid = p.productid
WHERE s.customerid = c.customerid
GROUP BY ()
HAVING SUM(s.quantity * p.price) > @amount
) p;