更改distinct，可以进行内部联接以确保：

SELECT
  A.year, 
  count(DISTINCT A.id), 
  count (DISTINCT B.id)
FROM
  purchase as A 
  INNER JOIN purchase2 B ON B.id = A.id
WHERE 
  A.year>2010
GROUP BY 
  A.year,
  B.year

我想 union all 聚合是最好的方法。从每个id/年的信息开始：

select id, year, max(in_a), max(in_b)
from ((select distinct id, year, 1 as in_a, 0 as in_b
       from purchase
      ) union all
      (select distinct id, year, 0 as in_a, 1 as in_b
       from purchase2
      ) 
     ) ab
group by id, year;

然后按年度汇总：

select year,
       sum(in_a) as total_a,
       sum(in_b) as total_b,
      sum(in_a * in_b) as in_both
from (select id, year, max(in_a), max(in_b)
      from ((select distinct id, year, 1 as in_a, 0 as in_b
             from purchase
            ) union all
            (select distinct id, year, 0 as in_a, 1 as in_b
             from purchase2
            ) 
           ) ab
      group by id, year
     ) iy
group by year;