2 同一表上的左外部联接会冻结服务器

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

但是您的查询不起作用的原因是，您将子查询 a 中的所有行与子查询 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