sqlserver 递归查询

目的：获取上一条的数据减下一条并保留差值依次递减

解决方案：

1、表中数据如下：

select * from test;

 

2、要把每个人上一个FEE减当前的FEE然后再用减完的差值去减下一个FEE，并把每一行算出来的结果存放到BALANCE字段，也就是要达到这个效果，如：John的第一个BALANCE是当前的FEE，第二条就是1200-150=1050，第三条是1050-300=750

 

 

 

3、为test表添加两个字段方便做递归查询

alter table test add creq int;

alter table test add preq int;

 

4、先给creq赋值

with t as (select NAME,ORDER_NUMBER,row_number()over(partition by NAME order by ORDER_NUMBER) creq from test)
update A set A.creq=B.creq
from test A left join t B on A.NAME=B.NAME and A.ORDER_NUMBER=B.ORDER_NUMBER;

 

 

5、再给preq赋值，preq的值等于上一条的creq

with t as (select NAME,ORDER_NUMBER,lag(creq)over(partition by NAME order by ORDER_NUMBER) preq from test)
update A set A.preq=B.preq
from test A left join t B on A.NAME=B.NAME and A.ORDER_NUMBER=B.ORDER_NUMBER;

 

 

6、编写递归查询的sql

with CTE as (
select NAME,ORDER_NUMBER,FEE,
cast(FEE as numeric(17,2)) as BALANCE,
creq,preq from test where creq=1
union all
select B.NAME,B.ORDER_NUMBER,B.FEE,
cast(A.BALANCE-B.FEE as numeric(17,2)) as BALANCE
,B.creq,B.preq from CTE A inner join test B on A.creq=B.preq and A.NAME=B.NAME
)select * from CTE order by NAME,ORDER_NUMBER
OPTION(MAXRECURSION 0);

 

 

OPTION(MAXRECURSION 0)的意思是递归次数无限制，不加这个默认是100次