WITH sub_nodes(pk_id) AS (
SELECT pk_id FROM IBP_T_Regions WHERE iparentid = 56
UNION ALL
SELECT tn.pk_id FROM IBP_T_Regions tn
JOIN IBP_T_Regions s ON tn.iparentid = s.pk_id
)
SELECT * FROM sub_nodes;
如果修改指定父节点所有子节点的部门id值如下:
WITH sub_nodes(pk_id) AS (
SELECT pk_id FROM IBP_T_Regions WHERE iparentid = 1
UNION ALL
SELECT tn.pk_id FROM IBP_T_Regions tn
JOIN sub_nodes s ON tn.iparentid = s.pk_id)
select * from sub_nodes
update IBP_T_Regions set iDeptid=159 from sub_nodes pt where IBP_T_Regions.PK_ID=pt.PK_ID