时间戳

首页 > 解决方案 > 在一张表中计算三个可能性

mysql - 在一张表中计算三个可能性

问题描述

我有一个包含两个字段的订阅者表，活跃和订阅。我需要在同一咨询中获取订阅者总数、激活数和订阅数。我尝试使用双左连接来做到这一点，但我需要按一个不是主键的字段进行分组，我得到了错误。我现在有这样的咨询：

FROM (SELECT subscribers.mailing_list, subscribers.mailing_list AS suscriptores, s2.inactivos AS inactivos, s3.excluidos AS excluidos
FROM subscribers
LEFT JOIN (SELECT id, mailing_list, COUNT(*) AS inactivos FROM subscribers WHERE subscribed = false GROUP BY id) s2 ON subscribers.id = s2.id
LEFT JOIN (SELECT id, mailing_list, COUNT(*) AS excluidos FROM subscribers WHERE excluded = true GROUP BY id) s3 ON subscribers.id = s3.id
) AS subs
GROUP BY subs.mailing_list```

标签： mysqlsql

解决方案

您需要在聚合函数内部而不是需要 3 个查询，case expressions这被称为“条件聚合”，例如

SELECT
        mailing_list
      , COUNT( CASE WHEN subscribed = FALSE THEN 1 END ) AS inactivos
      , COUNT( CASE WHEN subscribed = TRUE  THEN 1 END ) AS excluidos
      , COUNT( * ) AS Total
FROM subscribers
GROUP BY
        mailing_list

推荐阅读