关于计算同一个用户购买日期间隔查询语句

需求：

         购买明细表中有每一次购买日期，如何计算同一个人相邻两次购买的日期间隔天数 。

一：将目标表关键字段排序做成一个视图

select `transaction_detail_sales_2021`.`id` AS `id`,`transaction_detail_sales_2021`.`会员编码` AS `会员编码`,`transaction_detail_sales_2021`.`首次交易日期` AS `首次交易日期`,`transaction_detail_sales_2021`.`促销模式名称` AS `促销模式名称`,`transaction_detail_sales_2021`.`交易日期` AS `交易日期`,`transaction_detail_sales_2021`.`交易数量（标听）` AS `交易数量（标听）` 

from `transaction_detail_sales_2021` 

where ((`transaction_detail_sales_2021`.`交易类型` = '销售积分') and (year(`transaction_detail_sales_2021`.`首次交易日期`) = 2021)) 

order by `transaction_detail_sales_2021`.`会员编码`,`transaction_detail_sales_2021`.`交易日期`

二：查询语句

select r1.* ,r2.`会员编码` as `第二次会员编码` , r2.`交易日期` as  `第二次购买日期`, TIMESTAMPDIFF(DAY, r1.`交易日期`,r2.`交易日期`) as tdiff
from

(select (@i := @i + 1) as rownum,
                                        tablea.*
from tablea, (select @i := 1) r
) r1

left join 

(select (@j := @j + 1) as rownum,
                                        tablea.*
                    
from tablea, (select @j := 0) r
) r2

on r1.rownum = r2.rownum and r1.`会员编码` = r2.`会员编码`
limit 900000 offset 800000

注意@j ：= 0 的用法。