我有两个表:表 1 看起来像这样:
id type
1 bike
2 car
3 cycle
4 bike
表 2 如下所示:
id type
1 bike
2 car
我希望我的最终输出如下所示:
type count_table1 count_table2
bike 2 1
car 1 1
cycle 1 0
在 SQL 中执行此操作的最有效方法是什么?
最佳答案
简单的解决方案,不需要复杂的表连接和函数:
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
关于mysql - 按列分组并显示mysql中所有表的计数,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/18481416/