22,302
社区成员




--CREATE TABLE T_Party
--(
-- ID INT,
-- ParentID INT,
-- COUNTPERSON INT
--)
--INSERT INTO T_Party
--SELECT 1,0,9 UNION ALL
--SELECT 2,0,7 UNION ALL
--SELECT 3,1,4 UNION ALL
--SELECT 4,1,5 UNION ALL
--SELECT 5,3,3 UNION ALL
--SELECT 6,5,1 UNION ALL
--SELECT 7,5,2
DECLARE @ID INT=7 --更新的党组织ID
;WITH CTE AS (
/*递归获取上级所有党组织*/
SELECT ID,ParentID,COUNTPERSON,1 AS [LEVEL] FROM T_Party WHERE ID=@ID
UNION ALL
SELECT a.ID,a.ParentID,a.COUNTPERSON,b.[LEVEL]+1 AS [LEVEL]
FROM T_Party a
JOIN CTE b ON b.ParentID=a.ID
)
UPDATE T_Party SET COUNTPERSON=CTE.COUNTPERSON+1 FROM CTE WHERE CTE.ID=T_Party.ID
create procedure huizong
@ID int
as
--声明变量
declare @Father varchar(20)
;with cte as
(
select id=@Father,党员数=(select SUM(党员数) from T_Party where ParentID=@Father)
union all
select a.id,党员数=(select SUM(党员数) from T_Party where ParentID=a.id) from T_Party a,cte b where a.id=b.ParentID
)
update T_Party
set 党员数=t.党员数
from cte t
where T_Party.id=t.id
go