mysql - group by count(*) mysql 多个表

Question

我正在尝试跨多个表进行计数(*)，但将它们放在一个组中。

我在 mysql 中的查询是:

SELECT
  (SELECT COUNT(*) as 'Orders', Customer FROM table WHERE `date` = CURRENT_DATE()) as Client1, 
  (SELECT COUNT(*) as 'Orders', Customer FROM table2 WHERE `date` = CURRENT_DATE()) as Client2,
  (SELECT COUNT(*) as 'Orders', Customer FROM table3 WHERE `date` = CURRENT_DATE()) as Client3
  group by Customer

这就是我想要找回的:

+-------------+---------+---------+---------+
| Customer    | Client1 | Client2 | Client3 |
+-------------+---------+---------+---------+
| John Doe    | 88      | 19      | 0       |
+-------------+---------+---------+---------+
| Mary P      | 0       | 32      | 0       |
+-------------+---------+---------+---------+
| Scott K     | 11      | 25      | 31      |
+-------------+---------+---------+---------+

我唯一担心的是客户不会存在于其他表中，例如 - John Doe 仅是 table1 中的客户 - 而不是 table2 或 table3 中的客户。

与 Mary P 相同 - 她只是表 2 中的客户，而不是表 1 或表 3 等中的客户。

Answer 1

select a.Customer, sum(client1), sum(client2), sum(client3)
from
(
    select Customer, count(*) as client1, 0 as client2, 0 as client3 from table WHERE `date` = CURRENT_DATE() group by Customer
    union all
    select Customer, 0, count(*), 0 from table2 WHERE `date` = CURRENT_DATE() group by Customer
    union all
    select Customer, 0, 0, count(*) from table3 WHERE `date` = CURRENT_DATE() group by Customer
) as a
group by a.Customer