mysql - 获取组的总和以及最后一个值

Question

在MySQL中，我尝试根据字段对表进行分组，但在每个组中都输出某些字段的总和和最后一个值。对于每个问题我都有一个解决方案，但不能同时解决两个问题。

举个例子会更容易解释。假设这是我们拥有的数据:

CREATE TABLE IF NOT EXISTS `trades` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `trader` int(11) NOT NULL,
  `volume` int(11) NOT NULL,
  `cash` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

INSERT INTO `trades` (`id`, `trader`, `volume`, `cash`) VALUES
(1, 1, 5, 0),
(2, 1, 7, 5),
(3, 1, 4, 2),
(4, 2, 10, 10);

所以我们的表格如下所示:

+----+--------+--------+------+
| id | trader | volume | cash |
+----+--------+--------+------+
|  1 |      1 |      5 |    0 |
|  2 |      1 |      7 |    5 |
|  3 |      1 |      4 |    2 |
|  4 |      2 |     10 |   10 |
+----+--------+--------+------+

现在，为了按交易者分组并获取交易量总和，我们执行:

mysql> select trader, sum(volume) from trades group by trader;                                                                                                 
+--------+-------------+
| trader | sum(volume) |
+--------+-------------+
|      1 |          16 |
|      2 |          10 |
+--------+-------------+

这很容易。现在，为了获得每个组中的“最后”现金值，这里的“最后”是指“最大的id”(但一般来说，这可以使用任何其他字段进行排序) )，我可以使用following clever idea (另请参阅 here ):

mysql> select t1.trader, t1.cash
    -> from trades t1 left join trades t2
    -> on t1.trader = t2.trader and t1.id < t2.id
    -> where t2.id is null;                            
+--------+------+
| trader | cash |
+--------+------+
|      1 |    2 |
|      2 |   10 |
+--------+------+

但是我如何组合这些解决方案，或者找到不同的解决方案，以获得或多或少如下所示的结果:

+--------+--------+------+
| trader | volume | cash |
+--------+--------+------+
|      1 |     16 |    2 |
|      2 |     10 |   10 |
+--------+--------+------+

重要说明:我不想使用非稳健的解决方案，它会在 ONLY_FULL_GROUP_BY is enabled 时失败。 .

另一注意事项:我希望能够获取多个字段的“最后”值，但针对单个排序执行此操作应该没问题。

Answer 1

在 MySQL 中基本上有三种方法可以做到这一点:

• 使用变量和聚合。
• 使用联接和聚合。
• 使用 substring_index()/group_concat() 技巧。

最后一个有一定的局限性。它将所有值转换为字符串。并且作为字符串的中间值的数量受到参数group_concat_maxlen(可以更改)的限制。但是，当您已经进行聚合时，这是最简单的:

select trader, sum(volume),
       substring_index(group_concat(cash order by id desc), ',', 1) as last_cash
from trades
group by trader;

使用 join 的方法如下所示:

select t.trader, sum(t.volume),
       max(case when t.id = tt.maxid then cash end) as last_cash
from trades t left join
     (select trader, max(id) as maxid
      from trades t
      group by trader
     ) tt
     on t.trader = tt.trader
group by t.trader;