6 月和 7 月每月消费都满 100 的顾客
LeetCode 1511 · 按月汇总、每月都满足、年份过滤
面试怎么回答
先用日期区间限定 2020 年 6 月到 7 月，再按顾客分组，HAVING 里分别对 6 月和 7 月做条件求和，两个都 >= 100。「每个月都满足」要分别判断，不能用合计代替；日期过滤要带上年份。
先把这段讲顺；面试官追问时，再展开下面的原理和细节。
详细讲解
题意
顾客表、产品表（单价）、订单表（产品、数量、日期）。找出在 2020 年 6 月和 7 月，每个月消费金额都不少于 100 的顾客。
表结构
- Customers：customer_id（主键）、name、country
- Product：product_id（主键）、description、price
- Orders：order_id（主键）、customer_id、product_id、order_date、quantity
思路
先算每个顾客每个月的消费额（数量 × 单价），只保留 6、7 两个月；再要求两个月都满足 ≥ 100。可以先过滤出达标的（顾客, 月份），再要求每个顾客达标月份数为 2；也可以按顾客分组后用两个条件求和直接判断。原解法只按月份过滤，没有限定 2020 年，其他年份的 6、7 月订单也会被算进来。
参考解法
select customer_id,name
from Customers
where customer_id in
(select customer_id
from
(select customer_id, month(order_date) as month , sum(quantity*price) as total
from Orders o left join Product p on o.product_id = p.product_id
where month(order_date) = 6 or month(order_date)=7
group by customer_id,month(order_date)
) as t1
where total >=100
group by customer_id
having count(*)>=2
)另一种写法：按顾客分组，两个月分别求和判断
SELECT c.customer_id, c.name
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
JOIN Product p ON o.product_id = p.product_id
WHERE o.order_date >= '2020-06-01' AND o.order_date < '2020-08-01'
GROUP BY c.customer_id, c.name
HAVING SUM(IF(MONTH(o.order_date) = 6, o.quantity * p.price, 0)) >= 100
AND SUM(IF(MONTH(o.order_date) = 7, o.quantity * p.price, 0)) >= 100易错点
- 只写 MONTH(order_date) IN (6, 7) 会把其他年份的数据算进来。
- 「每个月都满足」不能写成「两个月合计 ≥ 200」。