MySQL 连接两个表

Question

我在 MySQL 中有两个表。我想使用每个表中的位来生成一个包含 3 列的表。月份、创建的客户数量和联系的客户数量。注意:联系的客户数量是当月创建的客户数量。另请注意:我只是在寻找与客户的一个联系点。下面的代码不起作用

select month, count(tb_id), sum(contacted)
from (select ta.id, month(ta.created_at) as month,
             if(ta.client_id is null, 0, 1) as contacted
      from (select c.id, l.created_at, t1.client_id
            from client c left join
                 (select distinct client_id
                  from interactions
                  where created_at >= '2015-01-01'
                 ) t1
                on t1.client_id = c.id
           where c.created_at >= '2015-01-01'
          ) ta
     ) tb
group by month;   

Answer 1

这就是你想要的吗？

select 
    month(c.created_at) as month, 
    count(distinct c.id) as created, 
    sum(month(i.created_month) = month(c.created_at)) as contacted
from client c
left join (
    select distinct month(created_at) created_month, client_id 
    from interactions
) i on c.id = i.client_id 
   and month(c.created_at) = i.created_month
where c.created_at >= '2015-01-01' 
group by month(c.created_at);