SQL Server Recursive recursive statistics

The data dictionary is as follows

Only, where ' xx ' means ' from ', because Oschina does not give input keywords

Kmcode,kmname,pidkm101    , tax revenue, 010103   , sales tax, 1011010304, general business Tax, 1010310106, personal income tax, 1011010601, personal income tax, 10106101060109, other personal income tax, 1010601

There is a table to count

SELECT * XX PS where swdjh= ' some value '

The results are as follows

Company name, account code, amount

AAAA, 101060109,20

How does it roll up the amount of his parent account code,

Solution Ideas:

1. Create a temporary table to save the results
2. Use SQL Server CTE to query all of your own and parent's account codes, as follows
   

With CTE as     select a.kmcode,a.kmname,a.pidkm xx sys_km a   where Kmcode in  (select t1.yskm from  V IEW_DJ  t1 where [email protected]) UNION ALL    Select k.kmcode,k.kmname,k.pidkm    )

3. Insert the data into the temporary table by associating the CTE with the amount table code below

INSERT INTO #tmphjcx SELECT * FROM (select  distinct  A.kmcode,a.kmname,a.pidkm,b.hjje,b.guoshui,b.dishui from CTE a left   join (select T2.kmcode, (t2.kmcode+ ' _ ' +t2.kmname) as Kmname,hjje=sum (JE), guoshui=sum (case when t1.ic= ' 1 "Then JE End", dishui=sum (case is t1.ic= ' 2 ' then JE end)  xx view_dj  t1left join sys_km  T2 on T2.KMCODE=T1 . yskm where [email protected]group by T2.kmcode,t2.kmname) Bon B.kmcode=a.kmcode) c

4. The query temporary table results as follows, you can see that the account code order is now the right, but their amount is empty, how to get the account amount of the next level of the account amount

5, at this time we should be in accordance with the length of the account code in reverse order, update the amount, we should only calculate the amount of the previous level, and then on the basis of the first level of the amount of the calculation of the next level. ( Note: Logical thinking is important )

Select Kmcode,hjje xx #tmphjcx order by Len (Kmcode) desc

The results are as follows

6. We then update the records in the temporary table above (create a cursor traversal amount table, and a temporary table association to update the value of the amount table)

Update a  set a.hjje= B.hjje,a.guoshui=b.guoshui,a.dishui=b.dishui from    #tmphjcx aleft join (select SUM (hjje) as Hjje,sum (Dishui) as Dishui,sum (Guoshui) as Guoshui, @kmcode as Kmcode xx  #tmphjcx b where [email protected]  ) Bon b . kmcode=a.kmcode where [email protected]

7. Find out the results we want

Select Kmcode, (kmcode+ ' _ ' +kmname) as Kmname,pidkm,isnull (hjje,0) as Hjje,isnull (guoshui,0) as Guoshui,isnull (dishui,0 ) as Dishui xx #tmphjcx

The results are as follows

finally: Good ideas + technology can solve all the problems. Please keep the original address if you want to reprint.

SQL Server Recursive recursive statistics