mysql - 2 LEFT OUTER JOIN 在同一张表上卡住服务器

Question

我正在尝试对表“apst_mailings”运行查询，该表存储我们发送给订阅者的每份时事通讯的内容。每次我们尝试向个人发送电子邮件时，我们都会在 apst_mailings_accuses 中插入一行，报告发送的时间和状态，以及新信件的 ID。我想列出简报并计算每个简报的发送总数和成功发送的总数。

    SELECT m.id AS code_mailing, 
    COUNT(a.adh_code) AS num, COUNT(b.adh_code) AS succes
    FROM apst_mailings AS m
        LEFT OUTER JOIN apst_mailings_accuses AS a
            ON a.id_mailing = m.id
        LEFT OUTER JOIN apst_mailings_accuses AS b
            ON b.id_mailing = m.id
            AND b.etat = 'succes'
    GROUP BY m.id

它只会永远挂起服务器。我已经尝试在单独的查询中加入每个连接，并且没有问题:

// Counts the email sent per mailing
    SELECT m.id AS code_mailing, 
    COUNT(a.adh_code) AS num
    FROM apst_mailings AS m
        LEFT OUTER JOIN apst_mailings_accuses AS a
            ON a.id_mailing = m.id
    GROUP BY m.id

    SELECT m.id AS code_mailing, 
    COUNT(b.adh_code) AS succes
    FROM apst_mailings AS m
        LEFT OUTER JOIN apst_mailings_accuses AS b
            ON b.id_mailing = m.id
            AND b.etat = 'succes'
    GROUP BY m.id

我可以拆分我的查询，但它不起作用的原因真的让我很困扰。谁能解释一下？

谢谢!

Answer 1

通过使用单个连接并使用 SUM 执行条件计数，您可以以更简单的方式获得所需的结果。

SELECT
   m.id AS code_mailing, 
   COUNT(a.adh_code) AS num,
   SUM(a.etat = 'succes') AS succes
FROM apst_mailings AS m
LEFT OUTER JOIN apst_mailings_accuses AS a
ON a.id_mailing = m.id
GROUP BY m.id

但是您的查询不起作用的原因是因为您正在将 ALL 子查询 a 中的行与 ALL 匹配的巨大交叉连接中子查询 b 中的行。这可能会生成一个巨大的临时结果集，这大概就是查询需要永远终止的原因。即使它确实终止了，您的计数也会完全消失 - 它们将是两个计数的乘积。

要解决它，请先执行 GROUP BY。然后 JOIN 将结果添加到您的主表。

SELECT
   m.id AS code_mailing, 
   IFNULL(a.num, 0) AS num,
   IFNULL(b.succes, 0) AS succes
FROM apst_mailings AS m
LEFT OUTER JOIN (
   SELECT id_mailing, COUNT(adh_code) AS num
   FROM apst_mailings_accuses 
   GROUP BY id_mailing
) a
ON a.id_mailing = m.id
LEFT OUTER JOIN (
    SELECT id_mailing, COUNT(adh_code) AS succes
    FROM apst_mailings_accuses
    WHERE etat = 'succes'
    GROUP BY id_mailing
) b
ON b.id_mailing = m.id