mysql - MySQL Join三个表返回多个结果

我有三张桌子：我们称之为客户，记录和回顾
客户表是：

id name
== ====
1  John
2  Jane
3  Mike

日志表是

id customer_id  created_at
== ===========  ==========
1  1            2015-06-10
2  1            2015-06-10
3  2            2015-06-11
4  1            2015-06-13
5  2            2015-06-15
6  1            2015-06-15

评审表是

id customer_id  created_at
== ===========  ==========
1  1            2015-06-10
2  2            2015-06-10
3  2            2015-06-11
4  1            2015-06-13
5  1            2015-06-15
6  1            2015-06-15
7  1            2015-06-18

我想要的

CUSTOMER_ID NAME LOG_QTY REVIEW_QTY
=========== ==== ======= ==========
1           John 4       5
2           Jane 2       2
3           Mike 0       0

我得到的：

CUSTOMER_ID NAME LOG_QTY REVIEW_QTY
=========== ==== ======= ==========
1           John 20      20
2           Jane 4       4
3           Mike 0       0

我的问题：

                       select CUSTOMER.ID, CUSTOMER.NAME,
 count(REVIEW.CUSTOMER_ID) as REVIEW_QTY,
    count(LOG.CUSTOMER_ID) as LOG_QTY
                         from CUSTOMER
                    left join REVIEW
                           on REVIEW.CUSTOMER_ID = CUSTOMER.ID
                    left join LOG
                           on LOG.CUSTOMER_ID = CUSTOMER.ID
                     group by CUSTOMER.ID
                     order by CUSTOMER.ID

最佳答案

如果在不使用COUNT()和GROUP BY的情况下运行查询，您将看到发生了什么：

select CUSTOMER.ID, CUSTOMER.NAME,
 REVIEW.CUSTOMER_ID as REVIEW_QTY,
 LOG.CUSTOMER_ID as LOG_QTY
from CUSTOMER
 left join REVIEW on REVIEW.CUSTOMER_ID = CUSTOMER.ID
 left join LOG on LOG.CUSTOMER_ID = CUSTOMER.ID
order by CUSTOMER.ID

这将为三个表中每个可能的CUSTOMER_ID行组合返回一行（这正是INNER JOIN的作用）。那就数数吧！
这应该能满足你的需要：

select CUSTOMER.ID, CUSTOMER.NAME,
 (select count(*) from REVIEW where CUSTOMER_ID = CUSTOMER.ID) as REVIEW_QTY,
 (select count(*) from LOG where CUSTOMER_ID = CUSTOMER.ID)  as LOG_QTY
from CUSTOMER
order by CUSTOMER.ID

关于mysql - MySQL Join三个表返回多个结果，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/31171361/