mysql - SELECT 查询的分组问题

Question

我正在构建一个非常基础的事件管理和邀请系统。为此，我构建了 2 个表并使用查询来列出所有事件及其邀请计数1. 发送的邀请总数2. 接受总数3. 拒绝总数4.总等待

下面是查询

SELECT 
    *, 
    count(im.event_id_fk) as total_invitations, 
    count(im2.event_id_fk) as total_accepted, 
    count(im3.event_id_fk) as total_rejected,
    count(im4.event_id_fk) as total_waiting 
FROM event_mst em
LEFT JOIN invitation_mst im
    ON (em.event_id_pk = im.event_id_fk)
LEFT JOIN invitation_mst im2
    ON (em.event_id_pk = im2.event_id_fk AND im2.status = 'Accept')
LEFT JOIN invitation_mst im3
    ON (em.event_id_pk = im3.event_id_fk AND im3.status = 'Reject')
LEFT JOIN invitation_mst im4
    ON (em.event_id_pk = im4.event_id_fk AND im4.status = 'Waiting')
GROUP BY 
    im.event_id_fk, 
    im2.event_id_fk, 
    im3.event_id_fk, 
    im4.event_id_fk 
ORDER BY 
    em.date_added DESC

现在的问题是这个查询给出了错误的计数，例如如果总共发送了 3 个邀请，则给予 9。如果发送了 5 个邀请，则给予 25。

所以看起来它是在与自身相乘并返回结果。我知道这个选择查询一定有问题。有人纠正我这个问题吗？

提前致谢。

Answer 1

这是mysql最简单的解决方案，因为它支持 bool 计算。

SELECT  event_id_pk,
        COUNT(*) as total_invitations, 
        SUM(status = 'Accept') as total_accepted, 
        SUM(status = 'Reject') as total_rejected,
        SUM(status = 'Waiting') as total_waiting 
FROM    event_mst
GROUP   BY event_id_pk

但查询不会为您提供有关邀请的完整详细信息，为此，您可以将查询包装在子查询中并将其与原始表本身连接起来，

SELECT  a.*,
        b.total_invitations,
        b.total_rejected,
        b.total_waiting
FROM    event_mst a
        INNER JOIN
        (
            SELECT  event_id_pk,
                    COUNT(*) as total_invitations, 
                    SUM(status = 'Accept') as total_accepted, 
                    SUM(status = 'Reject') as total_rejected,
                    SUM(status = 'Waiting') as total_waiting 
            FROM    event_mst
            GROUP   BY event_id_pk
        ) b ON a.event_id_pk = b.event_id_pk