Find problems with all child nodes
Create Table A (userid int, username nvarchar (10), Ratio Int, Amount Int, FatherId int , 1 Union AllSelect 3, N 'Li Si', 12, 200, 1 Union Allselect 4, N' King 5th, 6, 130, 2 Union Allselect 5, N 'Yang Sull', 9, 200, 3 Union AllSelect 6 , N 'Chen Qi', 4, 190, 2GO
Create function f_cid (@ID int) returns @RE table (userid int, [level] int) asbegindeclare @L INTSET @ L = 0insert @re select @ id, @ @@ RowCount> 0beginset @ l = @ L 1insert @ Re select a.userid, @ lfrom a, @ re bwhere a.fatherid = B.Userid and b. [level] = @ l-1ndreturnendgo
Select a. *, level = b. [Level], Amount * Ratio / 100 as Fee from A, F_CID (2) bwhere a.userid = B.Userid --------------- --Select SUM (SELECT A. *, Level = B. [Level], Amount * Ratio / 100 as Fee from A, F_CID (2) bwhere a.userid = B.Userid) TGO
Drop function f_ciddrop Table A
