我有两个表:表 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/

10-11 21:56
查看更多