问题描述
我有一个SQL表，一行是特定日期的收入，我想在表中添加一个新列，该值是特定日期与特定日期之间的增量收入(可以是正数或负数)前一天，想知道如何通过SQL实现?
I have a SQL table, one row is the revenue in the specific day, and I want to add a new column in the table, the value is the incremental (could be positive or negative) revenue between a specific day and the previous day, and wondering how to implement by SQL?
这里是一个例子，
原始表
...
Day1 100
Day2 200
Day3 150
...
新表(在末尾添加增量列，对于第一列，可以分配零)，
new table (add incremental column at the end, and for first column, could assign zero),
Day1 100 0
Day2 200 100
Day3 150 -50
我正在使用MySQL/MySQL Workbench.
I am using MySQL/MySQL Workbench.
先谢谢了，林
推荐答案
SELECT a.day, a.revenue , a.revenue-COALESCE(b.revenue,0) as previous_day_rev
FROM DailyRevenue a
LEFT JOIN DailyRevenue b on a.day=b.day-1
该查询假定每天表中都有一条记录.如果每天的行数可能超过1，则需要创建一个视图，以汇总按天分组的所有天数.
the query assume that each day has one record in the table. If there could be more than 1 row for each day you need to create a view that sums up all days grouping by day.
这篇关于计算SQL中两行之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！