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

Answer 2

您的查询所做的是加入客户的评论和日志，两者之间没有加入条件。这意味着你正在为每个日志创建一个笛卡尔乘积，其中包含对给定客户的每次评论（例如，你对John期望的4条日志乘以他的5条评论，解释了你得到的20条）。

解决此问题的一种方法是在子查询中分别对日志和评论执行group by：

SELECT    c.id, c.name, review_qty, log_qty
FROM      customer c
LEFT JOIN (SELECT   customer_id, COUNT(*) AS review_qty
           FROM     review 
           GROUP BY customer_id) r ON r.customer_id = c.id
LEFT JOIN (SELECT   customer_id, COUNT(*) AS log_qty
           FROM     log
           GROUP BY customer_id) l ON l.customer_id = c.id
ORDER BY  c.id

Answer 3

每当您有这样一个复杂的查询时，我总是建议您首先将其分解并重新放在一起。

例如，要获得单个表的每个客户的计数，可以使用以下聚合：

SELECT customer_id, COUNT(*) AS logCount
FROM log
GROUP BY customer_id;

您可以对审核执行同样的操作，并在最后将这些结果连接到customer表中以获得它们的名称。您应该使用外部联接的原因是，用户可能在其他表中没有条目。因此，您应该使用COALESCE()函数将空计数替换为0:

SELECT c.id, c.name, COALESCE(l.logCount, 0) AS logCount, COALESCE(r.reviewCount, 0) AS reviewCount
FROM customer c
LEFT JOIN(
  SELECT customer_id, COUNT(*) AS logCount
  FROM log
GROUP BY customer_id) l ON l.customer_id = c.id
LEFT JOIN(
  SELECT customer_id, COUNT(*) AS reviewCount
  FROM review
  GROUP BY customer_id) r ON r.customer_id = c.id;

下面是一个使用示例数据的SQLFiddle示例。