试试这个查询。返回每月和每年所有不同客户计数的结果。

SELECT 
    YEAR(o.date) AS year,
    MONTH(o.date) AS month,
    COUNT(distinct customer_id) AS count
FROM 
    order o
GROUP BY 
    YEAR(o.date),
    MONTH(o.date)
ORDER BY 
    YEAR(o.date) ASC,
    MONTH(o.date) ASC;

它可以工作，但也计算前几个月的客户。例如，在1月份计算的客户不应在2月份计算。我该怎么做？
为了确保每个客户只计算一次，我们将使用groub by和min()获取每个客户的最早日期：

select customer_id, min(date) as min_date
from mytable
group by customer_id

然后在这个数据集上使用group by和having应用你的逻辑：

SELECT DATE_FORMAT(min_date,'%Y-%m') , COUNT(customer_id) as count
FROM (
  select customer_id, min(date) as min_date
  from mytable
  group by customer_id
) o
GROUP BY DATE_FORMAT(o.min_date,'%Y-%m') 
HAVING COUNT(o.customer_id) > 1;

Demo here

您可以首先找出哪些客户每月有更多确切的两个订单：

select customer_id, DATE_FORMAT(date,'%Y-%m') as 'month'
from `order` o
group by customer_id, DATE_FORMAT(date,'%Y-%m')
having count(*) = 2

现在你有了这个列表，你可以计算每个月的客户数量：

select `month`, count(customer_id) 'Customer_cnt'
from (
  select customer_id, DATE_FORMAT(date,'%Y-%m') as 'month'
  from `order` o
  group by customer_id, DATE_FORMAT(date,'%Y-%m')
  having count(*) = 2
) as q
group by `month`

参见db-fiddle