variable value mysql_MySQL - How to set variable value inside select statement?

I want to get the value of the previous row, current row, and add them. If there's no previous row then I'll just set previous to 0. Here's what I tried.

SET @previous = CASE WHEN (SELECT MAX(value) FROM table WHERE dateTime < '2018-07-01') IS NULL

THEN 0

ELSE (SELECT MIN(value) FROM table WHERE dateTime BETWEEN '2018-07-01' AND '2018-07-30')

END;

SELECT @previous AS Previous,

@previous:=MAX(value) AS Current,

(@previous + MAX(value)) AS Joined

FROM table

WHERE dateTime BETWEEN '2018-07-01' AND '2018-07-30'

GROUP BY dateTime;

Here's the table content:

dateTime | value

2018-07-01 | 1

2018-07-01 | 1.2

2018-07-02 | 1.7

2018-07-03 | 2.1

2018-07-05 | 2.6

2018-07-05 | 3

2018-07-06 | 3.5

Unfortunately setting @previous:=MAX(value) does not change @previous value for the next row, if it's 0 in initialization, it will remain 0. Sample Result:

dateTime | Previous | Current | Joined

2018-07-01 | 0 | 1.2 | 1.2

2018-07-02 | 0 | 1.7 | 1.7

2018-07-03 | 0 | 2.1 | 2.1

2018-07-05 | 0 | 4 | 4

2018-07-06 | 0 | 4 | 4

Expected result

dateTime | Previous | Current | Joined

2018-07-01 | 0 | 1.2 | 1.2

2018-07-02 | 1.2 | 1.7 | 2.9

2018-07-03 | 1.7 | 2.1 | 3.8

2018-07-05 | 2.1 | 3 | 5.1

2018-07-06 | 3 | 3.5 | 6.5

Hope you can help me!

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值