SQL语句计算同环比增长率

有没有大佬赐教一下SQL语句计算怎么写啊

使用临时表处理,先按月汇总数据，添加上期数据和同期数据列，然后分别汇总上期数据和同期数据到上期临时表和同期临时表，再关联更新到汇总数据临时表。注意汇总上期数据和同期数据时，要分别在日期列上扣减一个月和一年

low一点的方法就是把月当列，自定义列用于计算

--店铺每日销售表，主键店铺ID，销售日期
if object_id('tempdb..#ShopSaleByeDay') is not null drop table #ShopSaleByeDay
create table #ShopSaleByeDay(ShopID varchar(20),SaleDate date,SaleAmount decimal(24,6)  primary key (SaleDate,ShopID))
go
--店铺表，主键店铺ID
if object_id('tempdb..#Shop') is not null drop table #Shop
create table #Shop(ShopID varchar(20) primary key (ShopID))
go
--日历表，主键日期
if object_id('tempdb..#Date') is not null drop table #Date
create table #Date(SaleDate date primary key(SaleDate))
go
--产生1500个店铺
insert into #Shop(ShopID)
select right(100000000+ number,6) from master..spt_values where type='P' and number between 1 and 1500
--产生2年的日历
insert into #Date(SaleDate)
select dateadd(day,number,'2020-01-01') from master..spt_values where type='P' and number between 0 and 730
--产生2年1500个店铺的销售数据
insert into #ShopSaleByeDay(ShopID,SaleDate,SaleAmount)
select a.ShopID,b.SaleDate,ABS(CHECKSUM(NEWID()))%10000
  from #Shop a join #Date b on 1=1

--取所有店铺2021年7月的环比销售和同比销售
select a.ShopID,a.SaleDate,a.SaleAmount,b.SaleAmount as YOYSaleAmount,d.SaleAmount as MOMSaleAmount
  from #ShopSaleByeDay a
  left join #ShopSaleByeDay b on a.ShopID=b.ShopID and a.SaleDate=dateadd(year,1,b.SaleDate) and b.SaleDate between '2020-07-01' and '2020-07-31'
  left join #ShopSaleByeDay d on a.ShopID=d.ShopID and a.SaleDate=dateadd(month,1,d.SaleDate) and d.SaleDate between '2021-06-01' and '2021-06-30'
  where a.SaleDate between '2021-07-01' and '2021-07-31'
日期部分，使用变量传参数进去控制