mysql - 将多个相同的 select 语句合并为一个(JOIN 或 UNION)？

Question

我有以下 4 个相同的选择，它们返回相同的列输出但结果不同。

类似的东西:

1) select a, count(*) from table a;

2) select a, count(*) from table b;

3) select a, count(*) from table c;

4) select a, count(*) from table d;

他们都输出类似的东西:

=================
a     |count(*) (Table a)| 
=================
hi    | 2
hello | 3    
why   | 4

=================
a     |count(*) (Table b)| 
=================
hi    | 4
hello | 6
why   | 8

表 c 和 d 类似。

我想对所有 4 个表进行连接，这样我就可以得到一个表，其中相同的列被汇总和分组并显示为一个，像这样

=================
a     |count(*) (Table a & b union )| 
=================
hi    | 6
hello | 9
why   | 12

我已经制定了一个适用于两个表的查询，但表上的进一步联合效果不佳:

select a, sum(total) from (select a, count(*) from table a union all select a, count(*) from table b) as T group by a;

这适用于两个表，但不超过我所缺少的。

Answer 1

select a,
   sum(total) from
    (select a, count(*) AS total from table a 
        union all 
    select a, count(*) AS total from table b
        union all
    select a, count(*) AS total from table c 
        union all 
    select a, count(*) AS total from table d 
        union all 
    )
    group by a