同比环比 sql 帆软frdemo订单表，订单明细表

with tab1 as(
select a.原年月,a.原数量,
(select b.原数量 from
(select substr(订购日期,0,8)原年月,sum(数量)原数量 from 订单 left join 订单明细 on 订单.订单ID=订单明细.订单ID group by substr(订购日期,0,8))b
where b.原年月 = substr(DATE(a.订购日期,'-1 month'),0,8)
)上月
from (select substr(订购日期,0,8)原年月,sum(数量)原数量,订购日期 from 订单 left join 订单明细 on 订单.订单ID=订单明细.订单ID group by substr(订购日期,0,8))a),

tab2 as(
select a.原年月,a.原数量,
(select c.原数量 from
(select substr(订购日期,0,8)原年月,sum(数量)原数量 from 订单 left join 订单明细 on 订单.订单ID=订单明细.订单ID group by substr(订购日期,0,8))c
where c.原年月 = substr(DATE(a.订购日期,'-1 year'),0,8)
)上年
from (select substr(订购日期,0,8)原年月,sum(数量)原数量,订购日期 from 订单 left join 订单明细 on 订单.订单ID=订单明细.订单ID group by substr(订购日期,0,8))a)

select substr(tab1.原年月,0,5)年份,tab1.原年月,tab1.原数量,round((tab1.原数量-tab1.上月)/tab1.上月*100,1)||'%' 环比,
round((tab2.原数量-tab2.上年)/tab2.上年*100,1)||'%' 同比
from tab1 left join tab2 on tab1.原年月=tab2.原年月