LeetCode（数据库）- 消费者下单频率

-- 解决方案(1)
select c.customer_id,c.name
from customers c
join orders o on o.customer_id=c.customer_id
join product p on p.product_id=o.product_id
group by c.customer_id, c.name
having sum(case when left(o.order_date,7)='2020-06' then p.price*o.quantity else 0 end)>=100 and
sum(case when left(o.order_date,7)='2020-07' then p.price*o.quantity else 0 end)>=100
-- 解决方案(2)
WITH t AS(SELECT customer_id, DATE_FORMAT(order_date, '%Y-%m') fdate, SUM(quantity * price) total
FROM Product p JOIN Orders o USING(product_id)
WHERE DATE_FORMAT(order_date, '%Y-%m') = '2020-06' OR DATE_FORMAT(order_date, '%Y-%m') = '2020-07'
GROUP BY customer_id, fdate)
SELECT customer_id, name
FROM t JOIN Customers USING(customer_id)
WHERE total >= 100
GROUP BY customer_id
HAVING COUNT(*) = 2