mysql - 根据另一个列值对列求和

Question

我在根据另一个字段值求和字段值时遇到问题。

如果是 used_points 或 added_points，我需要根据 activities.activity_type SUM(activities.points)并将其放入 AS used_points/added_points。

表事件:

id | subscription_id  | activity_type | points
--------------------------------------------------
1  |         1        |  used_points  |   10
2  |         1        |  used_points  |   50 
3  |         1        | added_points  |   20 
4  |         1        | added_points  |   30
5  |         2        |  used_points  |   20
6  |         2        |  used_points  |   45
7  |         2        | added_points  |   45
8  |         2        | added_points  |   45

表订阅:

id |     name    |  current_points
-------------------------------------
 1 |    card_1   |        700
 2 |    card_2   |        900

我需要什么:

  name   |  current_points  | used_points | added_points
-----------------------------------------------------------
 card_1  |         700      |     60      |     50
 card_2  |         900      |     65      |     90

我试过的:

SELECT
    subscriptions.name,
    subscriptions.current_points,
    IF(activities.activity_type="used_points", SUM(activities.points), null)
        AS used_points,
    IF(activities.activity_type="added_points", SUM(activities.points), null)
        AS added_points 
FROM activities
JOIN subscriptions
    ON activities.subscription.id = subscription.id
GROUP BY subscriptions.name

这是错误的。

谢谢

Answer 1

您想使用 SUM(IF( ))。您想要将 IF 返回的值相加。您希望为每一行计算 IF 表达式。然后，使用 SUM 聚合将每行返回的值相加。

从 IF 表达式中删除 SUM 聚合，而是将 IF 包装在 SUM 中。

---

跟进

问但为什么 IF 内部的 SUM() 不起作用？

A 好吧，它确实有效。它只是没有按照您希望的方式工作。

MySQL SUM 函数是一个“聚合”函数。它将行聚合在一起，并返回单个值。

对于这种形式的表达式:IF(col='foo',SUM(numcol),0)

MySQL 所做的是将所有行聚合到 SUM 中，并返回单个值。

其他数据库会适配，并在该表达式中引用非聚合 col 时抛出错误。 MySQL 更为宽松，将 col 引用视为聚合(如 MIN(col) 或 MAX(col)... 处理一组行，并返回单个值。在这种情况下，MySQL 选择单个样本行。(它不确定哪一行将被“选择”为样本行。)所以对 col 的引用有点像 GET_VALUE_FROM_SAMPLE_ROW(col)。聚合完成后，该 IF 表达式将被计算一次。

如果您从这个查询开始，这就是您要操作的行集。

SELECT s.name
     , s.current_points
     , a.activity_type
     , a.points
     , IF(a.activity_type='used_points',a.points,NULL) AS used_points
     , IF(a.activity_type='added_points',a.points,NULL) AS added_points
  FROM subscriptions s
  JOIN activities a
    ON a.subscription_id = s.id

当您添加 GROUP BY 子句时，会将其中一些行聚合在一起。对于非聚合，您将得到的是样本行中的值。

尝试将 GROUP BY s.name 添加到查询中，然后查看返回的内容。

也尝试添加一些聚合，例如 SUM(a.points)

SELECT s.name
     , s.current_points
     , a.activity_type
     , a.points
     , IF(a.activity_type='used_points',a.points,NULL) AS used_points
     , IF(a.activity_type='added_points',a.points,NULL) AS added_points
     , SUM(a.points) AS total_points
  FROM subscriptions s
  JOIN activities a
    ON a.subscription_id = s.id
 GROUP BY s.name

最后，我们可以将查询中的表达式添加到 SELECT 列表中:

     , IF(a.activty_type='used_points',SUM(a.points),NULL) AS if_used_sum
     , IF(a.activty_type='added_points',SUM(a.points),NULL) AS if_added_sum

我们发现，从这些表达式返回的值要么是 SUM(a.points)，这将匹配 total_points，要么是 NULL。我们可以看到 activity_type 列的值，它是从每个组的单个示例行中检索的，我们可以看到这个表达式是“有效的”，它只是没有做我们真正做的事情希望发生:条件测试在每一行上运行，返回点值或空值，然后为组求和。