group by count(*) mysql多个表。

Im trying to do a count(*) across multiple tables, but put them in a group by.

我尝试在多个表之间做一个计数(*)，但是将它们放在一个组中。

My query in mysql is:

我在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

This is what I am trying to get back:

这就是我想要找回的东西:

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

My only other concern is that the customers will not exist in other tables, for example - John Doe is only a customer in table1 - not table2 or table3.

我唯一关心的另一个问题是，客户将不会存在于其他表中，例如，John Doe只是table1的客户，而不是table2或table3。

Same with Mary P - she is only a customer in table2 not table1 or table 3 etc.

和Mary P一样——她只是table2的客户，而不是table1或表3等等。

2 个解决方案

#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

#2

Please use () before the union all like

请在联盟前使用()。

  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