sql2005 CTE 递归

create table A无忧脚本-st6@(hz%L(]
(
pU!f"A#z0   id int,无忧脚本'q(F%Y!r d6Au'Q
   filecode varchar(10),无忧脚本b K7gb||Lm
   parentid int,无忧脚本w*z9co_'Z5Q
   totalchildren int,无忧脚本#L8gR6pYLW R
   total int
j K
Jg/G(R0)

insert A select 1,'ws112',0,3,0无忧脚本4^Eb!kV)B
insert A select 2,'01',1,1,  2
!l0LwZh+M!@ l0insert A select 3,'02',1,1,  2无忧脚本 xk,?,^5B1S&Z hG
insert A select 4,'03',1,1,  2
!p0rW,c-]$e0insert A select 5,'0101',2,0,  3
}t
Qtrk0insert A select 6,'0201',3,0,  3
&_5{v)w"rQ4x0j Op0insert A select 7,'0301',4,0,  3无忧脚本/dz v U,fUgs6Z3RP
insert A select 8,'04',1,0,5

H|Ov'^(WZu0DECLARE @FILECODE AS VARCHAR(20)
\(t%Zc2il'@{2\:f0SET @FILECODE='ws112';
!m5zL4V[0WITH ACTE(ID,FILECODE,PARENTID,TOTALCHILDREN,TOTAL,LVL)
'@4KD3W,A8aC0AS无忧脚本 G7Ny1K;?&_b q0g
(
Z6UP/b-T+\-g0 SELECT ID,FILECODE,PARENTID,TOTALCHILDREN,TOTAL,0
nS1mn0w-Yu0 FROM A WHERE FILECODE=@FILECODE
{0Bvt b
h"@p0 UNION ALL
Zu9n-x2\_
m0 SELECT TA.ID,TA.FILECODE,TA.PARENTID,TA.TOTALCHILDREN,TA.TOTAL,TB.LVL+1
liH+SEDh!q0 FROM A TA INNER JOIN ACTE TB
AC3d shr0    ON TA.PARENTID=TB.ID
q5viK;i }Wg&?M0)无忧脚本O|;L7b1q!@~
SELECT FILECODE,值=TOTAL*LVL FROM ACTE WHERE TOTALCHILDREN=0

h7x ix fKN"H0DECLARE @FILECODE AS VARCHAR(20)无忧脚本
fJMio.?
p
SET @FILECODE='0301';无忧脚本~lt[;m]2{B
WITH ACTE(ID,FILECODE,PARENTID,TOTALCHILDREN,TOTAL,LVL)无忧脚本[*?e`%M{_
AS无忧脚本[]!m nQ"B ^|
( 无忧脚本}(|1TLq
 SELECT ID,FILECODE,PARENTID,TOTALCHILDREN,TOTAL,0
RDW2nB-H-Y0 FROM A WHERE FILECODE=@FILECODE无忧脚本0wsp x0W A
 UNION ALL
+I$^-Dh E3j*Q_0 SELECT TA.ID,TA.FILECODE,TA.PARENTID,TA.TOTALCHILDREN,TA.TOTAL,TB.LVL+1无忧脚本5G*W5T$a:V!K2N"~/sd
 FROM A TA INNER JOIN ACTE TB
:v&Q;_.l c"sx0    ON TA.id=TB.PARENTID
A9[R\s4j5v2[0)无忧脚本$H^b.^6AZ@
select *from acte无忧脚本+y"r3e*KnIE
select * from A
]Y:@yF'~0SELECT FILECODE,值=TOTAL*LVL FROM ACTE WHERE TOTALCHILDREN=0