MySQL Join三个表返回多个结果 [英] MySQL Join three tables return multiple results

问题描述

我有三个表:我们称其为CUSTOMER，LOG和REVIEW

I have three tables: Let's call it CUSTOMER, LOG and REVIEW

客户表为:

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

推荐答案

如果在没有COUNT()和GROUP BY的情况下运行查询，您将看到正在发生的事情:

If you run your query without the COUNT() and GROUP BY, you'll see what's happening:

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只是数出它们！

This returns a row for each possible combination of rows with the same CUSTOMER_ID from the three tables (that's just what INNER JOIN does). Then COUNT just counts 'em!

这应该给您您所需要的:

This should give you what you need:

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 Join三个表返回多个结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！