mysql - 分组表达式可以与变量赋值一起使用吗？

Question

我正在尝试计算分组结果集的行差异(如 MySQL difference between two rows of a SELECT Statement):

create table test (i int not null auto_increment, a int, b int, primary key (i));
insert into test (a,b) value (1,1),(1,2),(2,4),(2,8);

给予

| a | b
---------
| 1 | 1
| 1 | 2
| 2 | 4
| 2 | 8

这是带有 group 和 max(group) 结果列的简单 SQL:

select 
    data.a,
    max(data.b)
from
    (
        select a, b
        from test
        order by i
    ) as data
group by a
order by a

显而易见的结果是

| a | max(data.b)
-----------------
| 1 | 2
| 2 | 8

我失败的地方是当我想计算分组列上的逐行差异时:

set @c:=0;
select 
    data.a,
    max(data.b),
    @c:=max(data.b)-@c
from
    (
        select a, b
        from test
        order by i
    ) as data
group by a
order by a

仍然给出:

| a | max(data.b) | @c:=max(data.b)-@c
--------------------------------------
| 1 | 2           | 2 (expected 2-0=2)
| 2 | 8           | 8 (expected 8-2=6)

谁能强调为什么 @c 变量没有按预期从分组行更新到分组行？

Answer 1

SELECT data.a
     , data.b
     , @c := data.b - @c
FROM (
        SELECT a
             , max(b) AS b
        FROM test
        GROUP BY a
     ) AS data
ORDER BY a

Example