用mysql计算两个值的差值并根据结果排序

debugcn 发表于 Dev

更聪明

  game table:
 | id | group_id | user_id | last_update         | bonus |
 | 1  | 4        | 1       | 2017-01-22 00:06:10 |    0  |
 | 2  | 4        | 1       | 2017-01-12 00:11:34 |  300  |
 | 3  | 4        | 1       | 2017-01-02 00:30:44 | -111  |
 | 3  | 4        | 1       | 2017-02-02 00:21:44 | 4330  |
 | 3  | 4        | 6       | 2017-01-02 01:02:27 | 30    |
 | 3  | 4        | 6       | 2017-01-07 11:22:37 | 40    |
 | 3  | 4        | 6       | 2017-03-04 11:22:37 |  0    |

我想为给定组的每个用户计算当前日期的奖金减去当月第一天的奖金。

想要的输出：

| user_id | january (last day bonus - first day bonus) |
| 5       | 1400                                     |
| 19      | 1377                                     |
| 1       | 806                                      |
| 14      | 140                                      |
| 50      | 14                                       |

目前，我正在获得给定月份的奖金（1 个查询），计算最后一个和第一个之间的差异。我有 4000 个用户，所以我正在执行 4000 次查询来做我想做的事情，但速度太慢了。

是否可以仅使用 mysql 来做到这一点？

戈登·利诺夫

也许“最正确”的方法是使用变量或复杂的子查询。但是，另一种应该起作用的方法是使用group_concat()/ substring_index()：

select user_id, date_format(last_update, '%Y-%m') as yyyymm,
       substring_index(group_concat(bonus order by last_update desc), ',', 1) as last_bonus,
       substring_index(group_concat(bonus order by last_update asc), ',', 1) as first_bonus,
       (substring_index(group_concat(bonus order by last_update desc), ',', 1) -
        substring_index(group_concat(bonus order by last_update asc), ',', 1)
       ) as bonus_diff
from t
group by user_id, yyyymm;

请注意，这会将转换bonus为字符串——然后再次转换为数字以进行计算。这就是为什么我可能称之为“快速而肮脏”或“黑客”的原因。但是，它应该可以工作并且转换是安全的，因为值以数字开头。

其次，group_concat()默认限制为 1024 字节。对于这些聚合来说，这应该不是问题——除非您在一个月内为一个用户准备了数百行。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。