每天同一列的值之间的差的列 [英] Column that is the difference between values of same column perday

问题描述

我的表格如下:

Balance          Value 1        Value2          Date        Daily Change (Expected)  
8                8,648,668.60   12,620,390.51   3/27/2018     -1
7                3,087,707.30   3,494,174.70    3/28/2018     -1
6                3,039,443.51   2,345,356.15    3/29/2018     -1

如何设置查询格式，以使每日更改"列与前一天的余额和前一天的余额之差?

How can I format a query so that the "Daily Change" column is the difference of the latter day's balance and the former's?

让我们将我的表称为表"

Lets refer to my table as "table"

按顺序

3/27/2018 - 3/26/2018

3/28/2018 - 3/27/2018

3/29/2018 - 3/28/2018

推荐答案

我使用以下内容回答了这个问题:

I used the following to answer this question:

选择t1.*，t1.balance-t2.balance Daily_Change 从TestData t1 左联接TestData t2 在t2.report_date = DATE_SUB(t1.report_date，间隔1天) AND t1.account = t2.account 按t1.report_date，t1.account排序

SELECT t1.*,t1.balance-t2.balance Daily_Change FROM TestData t1 LEFT JOIN TestData t2 ON t2.report_date=DATE_SUB(t1.report_date, INTERVAL 1 DAY) AND t1.account=t2.account ORDER BY t1.report_date,t1.account

这篇关于每天同一列的值之间的差的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！