mysql - 带有小计和限制的一对多查询

Question

要求:选择从 2011 年 9 月到当前日期有 5 张或更多发票总计超过 3000 美元的交易的客户。

数据库管理系统:MySQL 5.6

表格:

• 客户:客户 ID (...)
• 发票:customerID,invoice_no,order_date,order_total (...)

我写了几个 MySQL 查询。 “最接近”工作的那个出现在下面。结果的问题是双重的:

1. 它查看每个客户的所有发票总额，而不仅仅是日期范围内的发票。

2. 它会提取一些(但不是全部)日期范围之外的记录。

这里是查询:

#Customers with 5 or more invoices Totaling more than $3000 From Sept 2011 to current
SELECT distinct c2.customerID,c2.firstname,c2.lastname,c2.company,c2.address,c2.address2,c2.city,c2.state,c2.country,c2.phone,c2.email,SUM(c1.order_total)
FROM
    customers c2 LEFT JOIN invoice c1 
    ON c2.customerID = c1.customerID
         AND ((date(c1.order_date)) between '2011-09-01'  and date(now())) 


GROUP BY
    c1.customerID
HAVING

COUNT(c1.invoice_no)>=7 and sum(c1.order_total) >=3000

如有任何帮助，我们将不胜感激。

谢谢。

Answer 1

应该这样做:

select c.*, SUM(i.order_total) total, COUNT(*) order_count
FROM customers c
JOIN invoice i ON c.customerID = i.customerID
WHERE i.order_date >= '2011-09-01'
GROUP BY c.customerID
HAVING order_count >= 5 and total > 3000