MySQL Join 三表返回多个结果

Question

我有三个表:我们称它为 CUSTOMER、LOG 和 REVIEW

CUSTOMER 表是:

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

LOG表是

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

REVIEW 表是

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

Answer 1

如果您在没有 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 所做的)。然后 COUNT 就数他们了!

这应该给你你所需要的:

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