mysql - 按列分组并显示mysql中所有表的计数

Question

我有两个表:表 1 如下所示:

id   type    
1    bike     
2    car      
3    cycle
4    bike

Table2 是这样的:

id   type    
1    bike     
2    car 

我希望我的最终输出如下所示:

type   count_table1   count_table2
bike        2            1
car         1            1 
cycle       1            0

在 SQL 中执行此操作的最有效方法是什么？

Answer 1

简单的解决方案，不需要复杂的表连接和函数:

SELECT type, MAX(count_table1) as count_table1, MAX(count_table2) as count_table2 FROM (
(
    SELECT type, COUNT(*) AS count_table1, 0 AS count_table2
    FROM Table1
    GROUP BY type
) UNION (
    SELECT type, 0 AS count_table1, COUNT(*) AS count_table2
    FROM Table2
    GROUP BY type)
) AS tmp
GROUP BY type

SQL Fiddle