正确的语法是：

SELECT COUNT(DISTINCT a.ip)
FROM a JOIN
     b
     ON a.ip = b.ip JOIN
     c
     ON a.ip  = c.ip;

这可能在我们有生之年不会结束。更好的方法是：

select ip
from (select distinct a.ip, 1 as which from a union all
      select distinct b.ip, 2 as which from b union all
      select distinct c.ip, 3 as which from c
     ) abc
group by ip
having sum(which) = 6;

我承认 sum(which) = 6 只是说三者都存在。因为 select distinct 在子查询中，您只需执行以下操作：

having count(*) = 3

简单的解决方案：

select      count(*)
from       (select      1
            from        (
                                    select 'a' as tab,ip from a
                        union all   select 'b' as tab,ip from b
                        union all   select 'c' as tab,ip from c
                        ) t
            group by    ip
            having      count(case when tab = 'a' then 1 end) > 0
                    and count(case when tab = 'b' then 1 end) > 0
                    and count(case when tab = 'c' then 1 end) > 0
            ) t

这将不仅为您提供有关3个表交集（in_a=1、in_b=1、in_c=1）的信息，而且还提供有关所有其他组合的信息：

select      in_a
           ,in_b
           ,in_c
           ,count(*)    as ips
from       (select      max(case when tab = 'a' then 1 end)  as in_a
                       ,max(case when tab = 'b' then 1 end)  as in_b
                       ,max(case when tab = 'c' then 1 end)  as in_c
            from        (
                                    select 'a' as tab,ip from a
                        union all   select 'b' as tab,ip from b
                        union all   select 'c' as tab,ip from c
                        ) t
            group by    ip
            ) t
group by    in_a
           ,in_b
           ,in_c

... 还有更多信息：

select      sign(cnt_a)                 as in_a
           ,sign(cnt_b)                 as in_b
           ,sign(cnt_c)                 as in_c
           ,count(*)                    as unique_ips
           ,sum(cnt_total)              as total_ips
           ,sum(cnt_a)                  as total_ips_in_a
           ,sum(cnt_b)                  as total_ips_in_b
           ,sum(cnt_c)                  as total_ips_in_c
from       (select      count(*)                                as cnt_total
                       ,count(case when tab = 'a' then 1 end)   as cnt_a
                       ,count(case when tab = 'b' then 1 end)   as cnt_b
                       ,count(case when tab = 'c' then 1 end)   as cnt_c
            from        (
                                    select 'a' as tab,ip from a
                        union all   select 'b' as tab,ip from b
                        union all   select 'c' as tab,ip from c
                        ) t
            group by    ip
            ) t
group by    sign(cnt_a)
           ,sign(cnt_b)
           ,sign(cnt_c)