mysql查询指定日期之间的情况或数据

Question

对于每个客户经理，显示所有客户及其总订单，上周订购超过 5 次或过去 14 天内订购超过 10 次的客户，有两个表格

1.订单[日期，相对。经理]，
2.客户[cid,cname]。

我正在尝试这样，谢谢。

select o.RelationshipManager,c.Name,count(*) total_orders
case
when o.OrderedDate >curdate() -interval 7 day then count(*)
else
o.OrderedDate >curdate() -interval 14 day then count(*)
end
from customer c
join orders o on c.customerid=o.customerid;

Answer 1

SELECT o.RelationshipManager, c.Name
   , COUNT(*) total_orders
   , COUNT(CASE WHEN o.OrderedDate > curdate() - INTERVAL 7 DAY THEN 1 ELSE NULL END) AS oneWeekCount
   , COUNT(CASE WHEN o.OrderedDate > curdate() - INTERVAL 14 DAY THEN 1 ELSE NULL END) AS twoWeekCount
FROM customer AS c
JOIN orders AS o ON c.customerid=o.customerid
-- WHERE o.OrderedDate > curdate() - INTERVAL 14 DAY 
GROUP BY o.RelationshipManager, c.Name
HAVING oneWeekCount > 5 OR twoWeekCount > 10
;

COUNT 仅计算非空值，WHERE 是可选的，但会更改结果(它应该减少检查的记录数，但会使 total_orders 与 twoWeekCount 相同)； HAVING 在执行聚合/计数后过滤结果。

根据我的经验，聚合函数很少适用于条件内部；我什至不能 100% 确定是否存在适合此类使用的场景。