MYSQL:从多个表返回计数

Question

下面的脚本有问题。我正在尝试返回一个表，其中两个邮件的收件人计数同时返回。我知道我所拥有的是错误的，但它可能让你知道我在寻找什么。

SELECT count( mailing_recipient_id ) AS CountA
                    FROM mailing_recipient
                    WHERE `mailing_id` =(
                                        SELECT mailing_id
                                        FROM mailing
                                        WHERE mailing_name = 'Mailing A' ) 
UNION

SELECT COUNT( mailing_recipient_id ) AS CountB
                    FROM mailing_recipient
                    WHERE `mailing_id` =(
                                        SELECT mailing_id
                                        FROM mailing
                                        WHERE mailing_name = 'Mailing B' ); 

非常感谢你。

Answer 1

您可以使用JOIN和GROUP BY来实现所需的结果，如下所示：

SELECT
     m.mailing_name ,
    count(mr.mailing_recipient_id)
FROM mailing_recipient mr
INNER JOIN mailing m
ON mr.mailing_id = m.mailing_id
GROUP BY m.mailing_name
ORDER BY m.mailing_name;

Answer 2

只需将它们放入子查询：

    SELECT SUM(tot.CountA) FROM (

SELECT count( mailing_recipient_id ) AS CountA
                    FROM mailing_recipient
                    WHERE `mailing_id` =(
                                        SELECT mailing_id
                                        FROM mailing
                                        WHERE mailing_name = 'Mailing A' ) 
UNION

SELECT COUNT( mailing_recipient_id ) AS CountA
                    FROM mailing_recipient
                    WHERE `mailing_id` =(
                                        SELECT mailing_id
                                        FROM mailing
                                        WHERE mailing_name = 'Mailing B' ); 

) tot

匹配两个联合查询中的别名，并对别名进行求和。

Answer 3

SELECT IFNULL(SUM(mailing_name = 'Mailing A'), 0) AS CountA,
       IFNULL(SUM(mailing_name = 'Mailing B'), 0) AS CountB
FROM mailing_recipient mr
JOIN mailing m ON mr.mailing_id = m.mailing_id