I have a table transaction with columns: date, approved. I'm trying to subtract two rows (approved) together and put the results into an alias called approved_diff
I'm using:
SELECT date, approved FROM transaction;
| date | approved |
|---|---|
| 2022-06-24 09:43:00 | 10 |
| 2022-06-24 09:45:00 | 15 |
| 2022-06-24 09:50:00 | 50 |
| 2022-06-24 09:52:00 | 51 |
| 2022-06-24 09:57:00 | 56 |
what I would like is:
| date | approved | approved_diff |
|---|---|---|
| 2022-06-24 09:43:00 | 10 | 10 |
| 2022-06-24 09:45:00 | 15 | 5 |
| 2022-06-24 09:50:00 | 16 | 1 |
| 2022-06-24 09:52:00 | 20 | 4 |
| 2022-06-24 09:57:00 | 22 | 2 |
Can someone help me?
CodePudding user response：
On MySQL 8 , we can use the LAG() analytic function here:
SELECT date, approved,
approved - LAG(approved, 1, 0) OVER (ORDER BY date) AS approved_diff
FROM yourTable
ORDER BY date;
On earlier versions of MySQL, we can use a correlated subquery in place of LAG():
SELECT date, approved,
approved - (SELECT approved FROM yourTable t2
WHERE t2.date < t1.date
ORDER BY t2.date DESC LIMIT 1) AS approved_diff
FROM yourTable t1
ORDER BY date;