mysql - 根据事件和响应代码计算邀请响应

Question

我有这张表用于响应代码:

这张表用于邀请:

到目前为止，我的查询给出了这个:

虽然我想实现这个:

我的查询:

SELECT 
      i.eventId
     ,code.responseCode
     ,COUNT(i.attendeeResponse) responseCount
FROM invitations i
LEFT JOIN response_codes code
    ON code.responseCode = i.attendeeResponse
GROUP BY i.eventId, code.responseCode, i.attendeeResponse;

SQLFiddle

Answer 1

首先需要构造所有eventId和responseCode的笛卡尔积(可以无条件join实现) :

select c.eventId
     , c.responseCode
     , count( i.attendeeResponse ) as responseCount
from ( select distinct t1.responseCode
            , t2.eventId
       from `response_codes` t1
       join `invitations` t2 ) c
left join `invitations` i on c.responseCode = i.attendeeResponse and c.eventId = i.eventId
group by c.eventId, c.responseCode;

SQLFiddle