mysql - 特定条件匹配后分组

Question

考虑下面我的表 transactions

+----+---------+-------+--------+
| id | user_id | value | tag    |
+----+---------+-------+--------+
|  1 |       1 |    30 | ''     |
|  2 |       1 |    20 | 'foo'  |
|  3 |       1 |   -10 | ''     |
|  4 |       2 |    60 | ''     |
|  5 |       2 |    30 | 'foo'  |
|  6 |       2 |   -25 | ''     |
+----+---------+-------+--------+

我想按 user_id 分组，以获得 sum(value)，对于标签匹配“foo”之后的行(其中 id 的行大于匹配的行)

比如上面的表格，我想得到下面的

+---------+-------+
| user_id | value |
+---------+-------+
|       1 |    10 |
|       2 |     5 |
+---------+-------+

Answer 1

您可以使用自连接:

SELECT t1.user_id, SUM(t2.`value`) AS `value`
FROM transactions t1
JOIN transactions t2
  ON t1.user_id = t2.user_id
 AND t1.id <= t2.id
WHERE t1.tag = 'foo'
GROUP BY t1.user_id;

LiveDemo

输出:

╔═════════╦═══════╗
║ user_id ║ value ║
╠═════════╬═══════╣
║       1 ║    10 ║
║       2 ║     5 ║
╚═════════╩═══════╝