【转载】获取某个字段每天的差值

08-获取某个字段每天的差值

一、要求

一张表中，有date，money字段，怎么通过一个sql求出每天money的差值，比如算一个月中每天的money差值

二、相关SQL

create table customer (navdate date,money int)


insert into customer values('20210401',25),('20210402',35),('20210403',18),('20210404',32)

select * from customer

--写法1
select a.navdate as adate, b.navdate as bdate,a.money,b.money,(b.money - a.money) from customer  a
inner join  customer  b on DATEADD(DD,1,a.navdate)=b.navdate
 where a.navdate >='20210401' and a.navdate <='20210404'

 --写法2
 select a.navdate as adate, c.navdate as bdate,a.money as amoney,c.money as bmoney,c.diffmoney from customer  a
cross apply (select b.navdate,b.money,(b.money - a.money) as diffmoney from   customer b where   DATEADD(DD,1,a.navdate)=b.navdate) as c
 where a.navdate >='20210401' and a.navdate <='20210404'

注意 Mysql DATEADD不支持，要换成DATE_ADD

MySQL报错：SQL_ERROR_INFO: ‘FUNCTION DATEADD does not exist‘