mysql - 在 MySQL 中自连接表时在不同列中显示特定结果

Question

我有一个表 email_activities_2，其中有一列标题为“action_type”，其中结果可以是“发送”、“打开”、“点击”等，并且每个操作、每封电子邮件、每个收件人 ID 都有一条记录。例如，recipient_id 54 的 email_id 94607 有两行:第一行的 action_type 为“已发送”，第二行为“打开”。

我想要一个查询，该查询将在一行中返回该内容，其中 action_type 列列出两次，但在左侧，仅显示 action_type =“sent”，右侧列为 action_type =“open”或“null”

这是我最接近我想要的:

select email2.email_id, email2.recipient_id, email2.action_type as sent, 
opens.action_type as opened
  from email_activities_2 email2 
  left join email_activities_2 opens on email2.id = opens.id
  where email2.action_type = "sent"
union all
select email2.email_id, email2.recipient_id, email2.action_type as sent, 
opens.action_type as opened
  from email_activities_2 email2
  right join email_activities_2 opens on email2.id = opens.id
  where opens.action_type = "open"
  order by recipient_id, email_id asc;

这将返回:

email_id    |   recipient_id   |  sent  |   opened

94607              54             sent      sent
94607              54             open      open
94981              54             sent      sent
98479              54             sent      sent
98479              54             open      open

当我想要的是:

email_id    |   recipient_id   |  sent  |   opened

94607              54             sent      open
94981              54             sent      NULL
98479              54             sent      open

这可能吗？我对 SQL 是全新的，并试图在工作中解决这个问题。

Answer 1

一种方法是条件聚合，不需要连接。

SELECT e.email_id
     , e.recipient_id
     , MAX(IF(e.action_type='sent',e.action_type,NULL)) AS sent
     , MAX(IF(e.action_type='open',e.action_type,NULL)) AS opened
  FROM email_activities_2 e
 WHERE e.action_type IN ('sent','open')
 GROUP
    BY e.email_id
     , e.recipient_id

GROUP BY 子句会将所有具有相同 email_id 和收件人_id 值的行“折叠”为一行。 SELECT 子句中的表达式中的条件测试将为每个组“挑选”'open' 和 'sent' 的值。 MAX 聚合将选择要返回的最高非 NULL 值，并且仅当组中没有与条件匹配的行时才返回 NULL。

此查询有可能返回如下行:

email_id    |   recipient_id   |  sent  |   opened
94999           54                NULL      open

如果有一行open，但没有行sent。

如果需要消除这些行，可以添加 HAVING 子句:

HAVING MAX(IF(e.action_type='sent',e.action_type,NULL)) = 'sent'

如果您想要从表中返回 email_id 和收件人的所有值，即使不存在 action_type 为 'sent' 或 'open' 的行，您也可以可以完全省略 WHERE 子句。